-- Tristan Sabol 
-- Due Date 11/15/24
-- CarDB Assignment Data
-- This program creates a database to store information about cars.

-- Create the database and set its name.
CREATE DATABASE Cars;

-- Switch to the Cars database.
USE Cars;

-- Create a table with fields for car information.
CREATE TABLE CarsData (
    id INT PRIMARY KEY AUTO_INCREMENT,
    year_ VARCHAR(4),
    make VARCHAR(20),
    model VARCHAR(20),
    Cycl VARCHAR(5),
    engine VARCHAR(5),
    MPG VARCHAR(5),
    price VARCHAR(10),
    Doors VARCHAR(1),
    Origin VARCHAR(15)
);


-- Create an index for the model column to optimize queries.
CREATE INDEX idx_model ON CarsData(model);

-- Describe the CarsData table to verify its structure.
DESCRIBE CarsData;

-- Insert values into the CarsData table.
INSERT INTO CarsData
(year_, make, model, Cycl, engine, MPG, price, Doors, Origin)
VALUES
("2012", "Nissan", "Versa", "4", "1.6", "31", "10990", "4", "American"),
("2013", "Nissan", "Versa", "4", "1.6", "31", "11990", "4", "American"),
("2013", "Chevrolet", "Spark", "4", "1.2", "35", "12185", "2", "American"),
("2013", "Smart", "Fortwo", "3", "1", "36", "12490", "2", "German"),
("2012", "Hyundai", "Accent", "4", "1.6", "32", "12545", "4", "American"),
("2013", "Ford", "Fiesta", "4", "1.6", "33", "13400", "4", "American"),
("2012", "Kia", "Rio", "4", "1.6", "33", "13600", "4", "American"),
("2012", "Kia", "Rio5", "4", "1.6", "33", "13600", "4", "American"),
("2012", "Fiat", "500", "4", "1.4", "34", "15500", "2", "Italy"),
("2012", "VW", "Jetta", "4", "2", "29", "15515", "4", "German"),
("2013", "VW", "Jetta", "4", "2", "29", "15454", "4", "German"),
("2012", "Mitsubishi", "Lancer", "4", "2", "29", "15695", "4", "Japanese"),
("2013", "Scion", "xD", "4", "1.8", "30", "15745", "4", "Japanese"),
("2013", "Dodge", "Grand Caravan", "6", "3.6", "21", "22030", "4", "American"),
("2013", "Nissan", "Frontier", "6", "4", "19", "22030", "4", "American"),
("2013", "Ford", "Mustang", "6", "3.7", "24", "22200", "2", "American"),
("2012", "Ferrari", "California", "8", "4.3", "16", "195840", "2", "Italy"),
("2013", "Ferrari", "California", "8", "4.3", "16", "198190", "2", "Italy"),
("2013", "Mercedes", "SLS-AMG", "8", "6.2", "16", "199500", "2", "German"),
("2012", "Ferrari", "458 Italia", "8", "4.5", "15", "229825", "2", "Italy"),
("2012", "Bentley", "Mulsanne", "8", "6.8", "14", "290000", "4", "British"),
("2013", "Nissan", "GT-R", "6", "3.8", "19", "96820", "2", "American"),
("2012", "BMW", "ActiveHybrid 750", "6", "3.8", "19", "96820", "2", "German"),
("2013", "BMW", "M6", "8", "4.4", "17", "108350", "2", "German"),
("2013", "Audi", "S8", "8", "4", "20", "110000", "4", "German"),
("2013", "Lexus", "LX 570", "8", "5.7", "14", "81530", "4", "Japan"),
("2013", "BMW", "750", "8", "4.4", "21", "86800", "4", "German"),
("2012", "BMW", "ActiveHybrid 5", "6", "3", "26", "60950", "4", "German"),
("2012", "VW", "Touareg Hybrid", "6", "3", "22", "61995", "4", "German"),
("2012", "Lotus", "Evora", "6", "3.5", "22", "66100", "2", "American"),
("2012", "Porsche", "Cayenne Hybrid", "6", "3", "22", "69000", "4", "German");

-- Display all American cars.
SELECT year_ AS "Year", make AS "Make", model AS "Model", Cycl AS "Cylinders", engine AS "Engine", MPG AS "Miles Per Gallon", price AS "Price $$", Doors AS "Car Doors", Origin AS "Manufactured IN"
FROM CarsData
WHERE Origin = "American";

-- Display all German cars.
SELECT year_ AS "Year", make AS "Make", model AS "Model", Cycl AS "Cylinders", engine AS "Engine", MPG AS "Miles Per Gallon", price AS "Price $$", Doors AS "Car Doors", Origin AS "Manufactured IN"
FROM CarsData
WHERE Origin = "German";

-- All cars with at least 20 MPG sorted by price in ascending order.
SELECT year_ AS "Year", make AS "Make", model AS "Model", Cycl AS "Cylinders", engine AS "Engine", MPG AS "Miles Per Gallon", price AS "Price $$", Doors AS "Car Doors", Origin AS "Manufactured IN"
FROM CarsData
WHERE MPG >= 20
ORDER BY price ASC;

-- All cars priced > 10K and < 20K sorted by model name in descending order.
SELECT year_ AS "Year", make AS "Make", model AS "Model", Cycl AS "Cylinders", engine AS "Engine", MPG AS "Miles Per Gallon", price AS "Price $$", Doors AS "Car Doors", Origin AS "Manufactured IN"
FROM CarsData
WHERE price > 10000 AND price < 20000
ORDER BY model DESC;

-- All cars priced > 20K and < 30K sorted by price in ascending order.
SELECT year_ AS "Year", make AS "Make", model AS "Model", Cycl AS "Cylinders", engine AS "Engine", MPG AS "Miles Per Gallon", price AS "Price $$", Doors AS "Car Doors", Origin AS "Manufactured IN"
FROM CarsData
WHERE price > 20000 AND price < 30000
ORDER BY price ASC;

-- All cars priced > 30K sorted by MPG in ascending order.
SELECT year_ AS "Year", make AS "Make", model AS "Model", Cycl AS "Cylinders", engine AS "Engine", MPG AS "Miles Per Gallon", price AS "Price $$", Doors AS "Car Doors", Origin AS "Manufactured IN"
FROM CarsData
WHERE price > 30000
ORDER BY MPG ASC;

-- All German cars sorted by make ascending that are 6 cylinders.
SELECT year_ AS "Year", make AS "Make", model AS "Model", Cycl AS "Cylinders", engine AS "Engine", MPG AS "Miles Per Gallon", price AS "Price $$", Doors AS "Car Doors", Origin AS "Manufactured IN"
FROM CarsData
WHERE Origin = "German" AND Cycl = 6
ORDER BY make ASC;

-- All cars that get over 30 MPG sorted by model descending order.
SELECT year_ AS "Year", make AS "Make", model AS "Model", Cycl AS "Cylinders", engine AS "Engine", MPG AS "Miles Per Gallon", price AS "Price $$", Doors AS "Car Doors", Origin AS "Manufactured IN"
FROM CarsData
WHERE MPG > 30
ORDER BY model DESC;

-- All cars that get < 20 MPG sorted by price descending order.
SELECT year_ AS "Year", make AS "Make", model AS "Model", Cycl AS "Cylinders", engine AS "Engine", MPG AS "Miles Per Gallon", price AS "Price $$", Doors AS "Car Doors", Origin AS "Manufactured IN"
FROM CarsData
WHERE MPG < 20
ORDER BY price DESC;

-- All 8-cylinder cars sorted by make descending order.
SELECT year_ AS "Year", make AS "Make", model AS "Model", Cycl AS "Cylinders", engine AS "Engine", MPG AS "Miles Per Gallon", price AS "Price $$", Doors AS "Car Doors", Origin AS "Manufactured IN"
FROM CarsData
WHERE Cycl = 8
ORDER BY make DESC;

-- All cars sorted by make descending and model ascending order.
SELECT year_ AS "Year", make AS "Make", model AS "Model", Cycl AS "Cylinders", engine AS "Engine", MPG AS "Miles Per Gallon", price AS "Price $$", Doors AS "Car Doors", Origin AS "Manufactured IN"
FROM CarsData
ORDER BY make DESC, model ASC;

-- Drop the Cars database.
DROP DATABASE Cars;
