SQLize Online / PHPize Online  /  SQLtest Online

CREATE TABLE customers (Customer_ID INT PRIMARY KEY, Name VARCHAR(20), Drivers_License VARCHAR(11)); INSERT INTO customers (Customer_ID, Name, Drivers_License) Values (1, 'Sally Phillips', 'NY774055434'), (2, 'Steven Smith', 'NY160423658'), (3, 'Gary Moore', 'MA942299072'), (4, 'Bill Barnes', 'CT896900826'), (5, 'Mary Andersen', 'NY132497102'); CREATE TABLE vehicles (Year INT, Make VARCHAR(20), Model VARCHAR(20), Trim VARCHAR (20), Mileage INT, Color VARCHAR(20), Price DEC(8,2), VIN VARCHAR(17) PRIMARY KEY, Buyer INT, FOREIGN KEY (Buyer) REFERENCES customers(Customer_ID)); INSERT INTO vehicles (Year, Make, Model, Trim, Mileage, Color, Price, Vin, Buyer) values (2010, 'FORD', 'TAURUS', 'SEL', 2572, 'White', 25600, '1FAHP2EW1AG163911', NULL); INSERT INTO vehicles (Year, Make, Model, Trim, Mileage, Color, Price, Vin, Buyer) values (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); INSERT INTO vehicles (Year, Make, Model, Trim, Mileage, Color, Price, Vin, Buyer) values (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); INSERT INTO vehicles (Year, Make, Model, Trim, Mileage, Color, Price, Vin, Buyer) values (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); SELECT COUNT(*) - COUNT(Buyer) AS 'Available Stock' FROM vehicles; SELECT AVG(Mileage) FROM vehicles WHERE Buyer IS NULL; SELECT SUM(Price) FROM vehicles WHERE Buyer IS NOT NULL; SELECT DISTINCT Make FROM vehicles ORDER BY Make ASC; SELECT * FROM vehicles WHERE Trim LIKE '%4X4%'; UPDATE vehicles SET Color = 'Taupe' WHERE Vin = '1FAHP2EW1AG163911'; SELECT * FROM vehicles WHERE Vin = '1FAHP2EW1AG163911'; SELECT Color AS 'Top Colors' , COUNT(Color) AS 'Total Stock (Past and Present)' FROM vehicles GROUP BY Color ORDER BY COUNT(Color) DESC LIMIT 3; SELECT customers.Name, customers.Drivers_License, vehicles.Buyer AS 'Vehicles Purchased' FROM customers INNER JOIN vehicles ON vehicles.Buyer=customers.Customer_ID;