Question

HELP WITH SQL QUERY NEEDED

New Vehicle Inventory	Used Vehicle Inventory
VIN Number	VIN Number
Price	Price
Make	Make
Model	Model
Year	Year
Exterior Color	Exterior Color
Interior Color	Interior Color
Transmission (Manual/Automatic)	Transmission (Manual/Automatic)
Engine (# of cylinders)	Engine (# of cylinders)
Mileage	Mileage
Fully loaded (Yes/No)	Fully loaded (Yes/No)
	Carfax Report (Yes/No)

You will also want to track the following types of information for new and used car sales, including specific customer data.

New Car Sales	Used Car Sales
Sales ID Number	Sales ID Number
VIN Number	VIN Number
Customer ID	Customer ID
Advertised Price	Advertised Price
Purchase Price	Purchase Price
Financed (Yes/No)	Financed (Yes/No)
Date sold	Date sold

Customer

Customer ID

First Name

Last Name

Address

Phone Number

Sales ID Number

4. Provide a query for each task listed below:

• identify which color car was the most popular customer choice
• give a list of all the used cars that were sold for less than $20,000
• lists all new cars for sale that are priced higher than $40,000
• lists all fully loaded used cars for sale that have less than $30,000 miles
• lists all cars sold this year
• list customers who bought more than one car in the past three years
• lists all manual cars with a 6-cylinder engine and the list displays the VIN number, whether it is a new or used car and the interior and exterior color

Answer 1

4.

SOLUTION ;-

identify which color car was the most popular customer choice

select exterior_color
from (
   select exterior_color,count(*) as total
   from New_Vehicle_Inventory
   group by exterior_color
   union all
   select exterior_color,count(*) as total
   from Used_Vehicle_Inventory
   group by exterior_color
   ) as combi
group by exterior_color order by sum(total) desc limit 1

give a list of all the used cars that were sold for less than $20,000

select us.VIN_Number,make,model
from used_car_sales us inner join used_vehicle_inventory ui
on us.VIN_Number=ui.VIN_Number
where purchase_price<20000

lists all new cars for sale that are priced higher than $40,000

select ns.VIN_Number,make,model
from new_car_sales ns inner join new_vehicle_inventory ni
on ns.VIN_Number=ni.VIN_Number
where advertised_price>40000

lists all fully loaded used cars for sale that have less than $30,000 miles

select VIN_Number,make,model
from used_vehicle_inventory
where Mileage<30000 and Fully_loaded='Yes'

lists all cars sold this year

select count(vin_number) from (
select vin_number from new_car_sales ns inner join new_vehicle_inventory ni
on ns.VIN_Number=ni.VIN_Number where year=2019
union
select vin_number from used_car_sales us inner join used_vehicle_inventory ui
on us.VIN_Number=ui.VIN_Number where year=2019
) as combi

list customers who bought more than one car in the past three years

select c.Customer_id
from customer c left join new_Car_sales n
on c.sales_id_number=n.sales_id_number and c.customer_id=n.customer_id
left join used_Car_sales u on c.sales_id_number=u.sales_id_number and c.customer_id=u.customer_id
where (n.year between 2017 and 2019 ) and (u.year between 2017 and 2019 )
group by c.Customer_id having count(*)>1