Spring 2026 — Assignment 3

Due 11-March-2026, 1pm EST (before class)
Purpose: If you use Google Cloud Platform (GCP): If you do the bonus points

For this assignment you may work individually or with at most 3 partners (max group size = 4). We strongly encourage you to work with partners as different query designers typically have different perspectives. Besides, you can learn from each other.

Answer the following questions / complete the following tasks. All homeworks are due before class on the due date. Please remember that the UVA Honor Code is in effect.

Part 1: [Total: 100 points] Write SQL queries to solve the following problems

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)

You may use phpMyAdmin (locally or on the CS server) or use GCP SQL instance.

Write SQL queries to solve the following problems.

  1. (10 points) Find all car brands that have a minimum list price at least 5000 for their least expensive vehicle. Display the brand and the lowest price of the vehicle the brand has.
  2. (10 points) Find the average price of a car by its color. List the colors and the average prices from the lowest to the highest average price.
  3. (10 points) Find the highest price of a car for each state. Display the state and the highest price, sort them from highest to lowest.
  4. (10 points) Find the number of vehicles with prices below the average price of their state. Then, display the state and the number of vehicles.
  5. (10 points) For each state, find the number of vehicles with a price below the average price of their state and the average mileage of these (below average price) vehicles. Display the state, the number of these vehicles, and the average mileage.
  6. (10 points) For each year, find the ratio of average price vs. average mileage of the cars. Display the years and the ratios, and sort the information by year. Display only the year with a ratio of no more than 0.1
  7. (10 points) Find the differences in prices between the least and most expensive vehicles in each state. For each state, display the price difference. Sort the information by the price differences from highest to lowest.
  8. (10 points) Find all vehicle brands that are listed in Virginia but not listed in New York.
  9. (10 points) Find all the brands and models of cars with prices between 20,000 and 50,000 (inclusive). Only consider the cars that have less than 10,000 mileage. Display vin, brand, model, price, and mileage.
  10. (10 points) Find the brands and the average prices of the vehicles with the "salvage insurance" title status. Display only the brands with the average prices between 2000 and 3000, inclusive.

Part 2: [10 bonus points] Data exploration and analysis

Let's practice data exploration and analysis using SQL! For this section, the questions will be a bit more open-ended. (Note: This is a bonus section of the assignment. If you do not wish to work on this part and have completed the SQL queries part, then you have finished this assignment!)

  1. (Up to 5 points) Using the following listed criteria, create at least 3 SQL queries on the US vehicle toy database (see Part 1 of the SQL queries part, above) that can be used for analysis. You can create as many queries as you want (they can be simple or complicated queries), as long as all three of the following requirements are satisfied:
    • One of your queries must use an aggregate function (SUM, AVG, MIN, MAX, COUNT)
    • One of your queries must make use of a join (NATURAL JOIN, INNER JOIN, LEFT OUTER JOIN, RIGHT OUTER JOIN)
    • One of your queries must use a subquery (or subqueries)

    The bonus points will be awarded based on the quality (meaningful, reasonable, correctness, ...) of your SQL queries.

    Your SQL queries must be *different* from the SQL queries in Part 1

  2. (Up to 3 points) Now that you have all of the queried results, what are some of the patterns or trends in the data? Are there outliers that exist, and if so, what external factors do you think could be affecting these results? Identify other areas of analysis, and write a brief paragraph (5-7 sentences) with your findings.

    The bonus points will be awarded based on the quality of your write-up (meaningful, reasonable, precise, concise, ...)

  3. (Up to 2 points) Provide a visual representation of one of your findings. This can be any type of graph/chart that best represents the data, and provides insight into your analysis (above). Make sure to label the visual with a title, axis labels and titles, key (if needed), and measurement (if applicable).

    The bonus points will be awarded based on the quality of your visual representation and the information being conveyed (meaningful, reasonable, consistent with the findings you discussed previously, ...)

Late submission

To grade and record your assignment efficiently, the following details apply to all homework assignments, unless otherwise specified.

What to submit

Part 1: Your SQL queries Part 2: If you do the bonus points, submit a report containing

Submission

Include all team member names and computingIDs in the report.

Part 1 — Save your answers as .sql file.

Part 2 (if you do the bonus points) — Save your report as a PDF —  No Word document. No handwriting. Only typed PDF is acceptable.

Each team submits only one copy.

Upload your file(s) to Assignment 3 on Gradescope. Make sure you connect your partner to your group on Gradescope so that everyone receives credit.

Verify that you submit the correct version of your file.

Making your submission available to the course staff is your responsibility; if we cannot access or open your file, we have to assign a zero grade. Be sure to test access to your file before the due date.


Copyright © 2026 Upsorn Praphamontripong
Released under the Creative Commons License CC-BY-NC-SA 4.0 license.
Last updated 2026-03-04 7:42