Cars Database Program Documentation

Tristan Sabol

Due Date: 11/15/24

CarDB Assignment Data

This program creates a database to store information about cars.

1. Create Database


            CREATE DATABASE Cars;

This creates the database with the name Cars.

2. Switch to Database


            USE Cars;

This command switches the context to the Cars database for data manipulation.

3. Create Table

        
            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)
            );
        
    

This creates a table named CarsData with specified fields.

4. Create Indexes


            CREATE INDEX idx_model ON CarsData(model);

These commands create indexes to improve search efficiency on Model.

5. Describe Table Structure


            DESCRIBE CarsData;

This command displays the structure of the CarsData table.

6. Insert Values into 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");
        
    

This command inserts multiple values into the CarsData table.

7. Display Queries from Table

        
-- 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 descending.
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 ascending.
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 with MPG    by price descending.
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 
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;
        
    

This retrieves the data from the CarsData table and displays it.

8. Drop Database


            DROP DATABASE Cars;

This command deletes the Cars database from the server.