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)
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.
- Find the numbers of vehicles by year. Display only the year with at least 100 vehicles.
- Find the counts of vehicle brands listed in each state. Sort the result by states.
- Find the counts
of colors for all Jeep MPVs.
Display the color and the count of each color.
- You should consider the number of vehicles in each color; for example, if three Jeep MPVs are black, the count of black color is 3.
- 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 prices. Sort the information by 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 miles. Display VIN, brand, model, price, and mileage.
Copyright © 2024 Upsorn Praphamontripong
Released under the
CC-BY-NC-SA 4.0 license.