SQLize Online / PHPize Online  /  SQLtest Online

A A A
Share      Blog   Popular
Copy Format Clear
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, COUNT(vehicles.Buyer) AS 'Vehicles Purchased' FROM vehicles INNER JOIN customers ON vehicles.Buyer=customers.Customer_ID GROUP BY customers.Customer_ID; SELECT customers.Name, customers.Drivers_License, COUNT(vehicles.Buyer) AS 'Vehicles Purchased' FROM vehicles INNER JOIN customers ON vehicles.Buyer=customers.Customer_ID GROUP BY customers.Customer_ID; CREATE TABLE luxury(Luxury_Make VARCHAR(20), FOREIGN KEY(Luxury_Make) REFERENCES vehicles(Make)); SELECT * FROM vehicles; SET @table_name:='vehicles'; SET @sql:=CONCAT('SELECT * FROM ',@table_name); PREPARE dynamic_statement FROM @sql; EXECUTE dynamic_statement; DEALLOCATE PREPARE dynamic_statement;
Stuck with a problem? Got Error? Ask ChatGPT!
Copy Clear