Fall 2026 — Assignment 3

Due 16-October-2026, 10am (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 2 partners (max group size = 3). 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 vehicle brands with the registration years after 2015 that are listed for the state of Virginia. Do not repeat the names of the brands.
  2. (10 points) List all car brands whose lowest-priced vehicle is at least $5,000.
  3. (10 points) 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.
  4. (10 points) Find the number of vehicles with prices above the average price of their state
  5. (10 points) Find all vehicle brands that are not registered in Virginia.
  6. (10 points) Find the number of non-expired, non-salvage insurance vehicle listings in each state, which would be helpful for customers looking for states with many listings available. Display the state and the number of non-expired, non-salvage insurance vehicle listings and sort them by the number in descending order.
    • Hint: consider only the vehicles with non-expired listing "and" non-salvage insurance — this means, if a vehicle satisfies *both* requirements, count as 1. If a vehicle satisfies only one of these requirements, do not count it.
  7. (10 points) Find the number of vehicles with prices above the average price of their state. Then, display the state and the number of vehicles.
  8. (10 points) Find the brands of the vehicles with "clean vehicle" title status listed in Virginia and Michigan.
    • Hint: pay attention to "and" — this means, we are considering only those that are listed in *both* Virginia and Michigan — must be listed in both states, not just one state.

    Do not include vehicles with expired license conditions. Do not repeat the names of the brands.

  9. (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 higher than 2000.
  10. (10 points) Find all vehicle brands that are listed in New York but not listed in Alabama.

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

Per class discussion on 10/09, we decided to waive the late penalty for Assignment 3.

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.

Please ensure you complete the following steps during submission:

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-10-08 15:19