CS4750 - Database Systems

Activity: SQL practice

(no submission)

Purpose:

Be familiar with database queries
Query data from a database
Write SQL queries using a combination of basic, aggregate, joins, and subqueries and interpret SQL to solve problems
Get ready to work on homework assignment and course project

You may make a copy of a activity-sql-practice.sql and complete this activity.

You may work alone or with at most 2 other students (max size=3) in this course.

Use an SQL script cardata.sql to create and populate three tables of the US vehicle toy database. (data from https://www.kaggle.com/datasets/doaaalsenani/usa-cers-dataset?resource=download)

Expand / Collapse all

Write SQL queries to solve the following problems.

Find all vehicle brands with the registration years after 2015 that are listed for the state of Virginia. Do not repeat the names of the brands.

SELECT   DISTINCT brand 
FROM     Car_specs T1, Car_priceloc T2 
WHERE    T1.vin = T2.vin AND T2.state LIKE "Virginia"
AND      T1.year > 2015;

-- or
SELECT   DISTINCT brand 
FROM     Car_specs T1, Car_priceloc T2 
WHERE    T1.vin = T2.vin AND T2.state = "Virginia"
AND      T1.year > 2015;

Find the number of vehicles by year. Display only the year with at least 100 vehicles.

SELECT   T1.year, count(*) AS number_cars
FROM     Car_specs T1 NATURAL JOIN Car_priceloc T2 
GROUP BY T1.year
HAVING   number_cars >= 100;

Find the counts of vehicle brands listed in each state. Sort the result by states.

SELECT   T1.state, COUNT(DISTINCT T2.brand) AS number_brands
FROM     Car_priceloc T1 NATURAL JOIN Car_specs T2
GROUP BY T1.state  
ORDER BY T1.state;

Find the counts of colors for all Jeep MPVs. Display the color and the count of each color.

SELECT   color, COUNT(color) 
FROM     Car_specs 
WHERE    brand LIKE "jeep" AND model like "mpv" 
GROUP BY color;

-- or 
SELECT   color, COUNT(color) 
FROM     Car_specs 
WHERE    brand = "jeep" AND model = "mpv" 
GROUP BY color;

List VINs of the vehicles with the most expensive price in each state. If there are multiple vehicles with the most expensive price in the state, list them all. Display VINs, states, and price. Sort the information by state.
```
WITH T1 AS (SELECT   state, MAX(price) AS max_price
            FROM     Car_priceloc
            GROUP BY state)
SELECT   T2.vin, T2.price, T2.state 
FROM     Car_priceloc T2, T1
WHERE    T2.state = T1.state AND T2.price = T1.max_price
ORDER BY T1.state; 
```

Find all the brands and models of vehicles with prices between 20,000 and 50,000 (inclusive). Only consider the vehicles that have less than 10,000 mileage. Display vin, brand, model, price, and mileage.

SELECT  T1.vin, brand, model, price, mileage 
FROM    Car_priceloc T1, (SELECT * 
                          FROM   Car_specs s NATURAL JOIN Car_condition c 
                          WHERE  c.mileage < 10000) T2 
WHERE   T2.vin like T1.vin AND T1.price >= 30000 AND T1.price <= 50000;

-- or
WITH T2 AS
       (SELECT * 
        FROM   Car_specs s NATURAL JOIN Car_condition c 
        WHERE  c.mileage < 10000) 
SELECT  T1.vin, brand, model, price, mileage 
FROM    Car_priceloc T1, T2
WHERE   T2.vin = T1.vin AND T1.price >= 30000 AND T1.price <= 50000;

-- or
SELECT  vin, brand, model, price, mileage 
FROM    Car_specs NATURAL JOIN Car_condition NATURAL JOIN Car_priceloc
WHERE   mileage < 10000 AND
        price BETWEEN 30000 AND 50000;
       
-- or
SELECT  vin, brand, model, price, mileage 
FROM    Car_specs NATURAL JOIN Car_condition NATURAL JOIN Car_priceloc
WHERE   mileage < 10000 AND
        price >= 30000 AND price <= 50000;

Expand / Collapse all

Released under the Creative Commons License CC-BY-NC-SA 4.0 license.

Last updated 2024-02-22 19:41