SQLize | PHPize | SQLtest

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 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 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 Color = 'Taupe' WHERE VIN = '1FAHP2EW1AG163911'; 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 GROUP BY Color ORDER BY COUNT(Color) DESC LIMIT 3; -- 8) Display the name, drivers license number and the total amount -- purchased for each customer. SELECT Name, Drivers_License, SUM(Price) FROM customers LEFT 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;