SQLize Online / PHPize Online  /  SQLtest Online

CREATE TABLE customers ( CustomerID INT PRIMARY KEY AUTO_INCREMENT, Name VARCHAR(50) NOT NULL, Drivers_License VARCHAR(50), UNIQUE(Drivers_License) ); INSERT INTO customers (Name, Drivers_License) VALUES ('Sally Phillips', 'NY774055434'), ('Steven Smith', 'NY160423658'), ('Gary Moore', 'MA942299072'), ('Bill Barnes', 'CT896900826'), ('Mary Andersen', 'NY132497102'); CREATE TABLE vehicles ( Year VARCHAR(10), Make VARCHAR(25), Model VARCHAR(25), Trim VARCHAR(25), Mileage INT, Color VARCHAR(15), Price INT, VIN VARCHAR(30) PRIMARY KEY, Buyer INT, FOREIGN KEY(Buyer) REFERENCES customers (CustomerID) ); INSERT INTO vehicles (Year, Make, Model, Trim, Mileage, Color, Price, VIN, Buyer) VALUES ('2010','FORD','TAURUS','SEL',2572,'White',25600,'1FAHP2EW1AG163911',NULL), ('2008','JEEP','GR CHEROKEE','4X4 LIMITED',33860,'White',23700,'1J8HR582X8C117809',1), ('2007','JEEP','GR CHEROKEE','4X4 LIMITED',20155,'White',21900,'1J8HR58257C662217',NULL), ('2009','FORD','ESCAPE','4X2 LIMITED',11354,'Green',21100,'1FMCU04G69KB01856',NULL), ('2008','DODGE','CHARGER','R/T',34602,'Yellow',20250,'2B3LA53H38H185713',NULL), ('2008','LINCOLN','MKZ','',15772,'Red',20250,'3LNHM26T08R656808',NULL), ('2009','CHEVROLET','TRAILBLAZER','4X4 LT',35635,'White',19000,'1GNDT33SX92102535',NULL), ('2008','DODGE','RAM 1550','4X4 QD',68314,'Green',18600,'1D7HU18268J115846',NULL), ('2009','FORD','FUSION','SEL',34595,'Grey',13950,'3FAHP08119R159916',1), ('2007','MERCURY','GRAND MARQUIS','GS',19128,'Gold',11750,'2MEFM74VX7X617445',NULL), ('2006','CHEVROLET','COBALT','LT',48021,'White',7375,'1G1AL55F167732362',NULL), ('2007','BUICK','LUCERNE','CX',36225,'Tan',13050,'1G4HP57247U112723',4), ('2009','HYUNDAI','SONATA','4C GLS',30162,'Tan',11600,'5NPET46C89H532795',NULL), ('2007','JEEP','COMPASS','4X4',33296,'Silver',14050,'1J8FF47WX7D357711',NULL), ('2008','FORD','FOCUS','SES',35668,'Silver',11350,'1FAHP33N68W238747',NULL), ('2001','FORD','EXPLORER','4X4 SPORT',61777,'Blue',5325,'1FMYU70E81UB70673',NULL), ('2008','MERCEDES-BENZ','ML320','',50543,'Black',35400,'4JGBB22E18A355249',NULL), ('2011','CADILLAC','STS','AWD',14028,'Black',42800,'1G6DD67V280199562',5), ('1999','CADILLAC','ELDORADO','',58956,'Black',4350,'1G6EL12Y4XU607787',NULL), ('2008','LAMBORGHINI','GALLARDO','SPYDER',4895,'Black',139900,'ZHWGU22T28LA06389',NULL); -- 1) How many cars do we have in stock? Select * from vehicles where Buyer is null; -- or Select count(VIN) from vehicles where Buyer is null; -- 2) What is the average mileage for the cars on our lot? Select avg(Mileage) from vehicles; -- 3) What is the total value of cars sold? Select count(Buyer) from vehicles; -- or Select sum(Price) from vehicles where Buyer is not null; -- 4) What car makes have we carried? Show each only once, sorted in ascending alphabetic order. Select distinct Make from vehicles order by Make asc; -- 5) Show the full record for any 4X4 vehicle. Select * from vehicles where Trim like '4X4%'; -- 6) The record for vehicle 1FAHP2EW1AG163911 is wrong. The car is really Taupe. Please correct that. Select * from vehicles where VIN = '1FAHP2EW1AG163911'; UPDATE vehicles SET Model = 'TAUPE' WHERE Model = 'TAURUS'; Select * from vehicles where VIN = '1FAHP2EW1AG163911'; -- 7) How many cars have we had in each of the top three colors? Select Color, count(Color) from vehicles where Color = 'White' or Color = 'Black' or Color = 'Silver' group by Color order by count(Color) desc; -- 8) Display the name, drivers license number and the total amount -- purchased for each customer. Select Name, Drivers_License, sum(Price) from customers join vehicles on Buyer = CustomerID group by CustomerID; -- Suppose we were to create another table which holds just a single attribute, -- the makes for cars we consider -- Luxury vehicles: originally just Lincoln and Cadillac. CREATE TABLE luxury ( Car_Make VARCHAR(25) PRIMARY KEY ); INSERT INTO luxury (Car_Make) VALUES ('LINCOLN'), ('CADILLAC'), ('MERCEDES-BENZ'), ('LAMBORGHINI'); -- 9) Show the full record for vehicles with makes in the Luxury table. -- I might change the contents of the Luxury table so do this dynamically. Select * from vehicles inner join luxury on Make = Car_Make;