SQLize Online / PHPize Online  /  SQLtest Online

A A A
Share      Blog   Popular
Copy Format Clear
CREATE TABLE Customers ( ID int PRIMARY KEY AUTO_INCREMENT, Name char (100) NOT NULL, DLNumb char (11) ); CREATE TABLE Cars ( CarYear year NOT NULL, Make char (15) NOT NULL, Model char (15) NOT NULL, CarTrim char (20), Mileage int (5) NOT NULL, Color char (10) NOT NULL, Price int (5) NOT NULL, VIN char (17) PRIMARY KEY, Buyer int, CONSTRAINT CustomersID FOREIGN KEY (Buyer) REFERENCES Customers(ID) ); INSERT INTO Customers (Name, DLNumb) VALUES ('Sally Phillips', 'NY774055434'), ('Steven Smith', 'NY160423658'), ('Gary Moore', 'MA942299072'), ('Bill Barnes', 'CT896900826'), ('Mary Andersen', 'NY132497102'); INSERT INTO Cars 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', 'S', 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); /*How many cars do we have in stock? */ SELECT COUNT(VIN) FROM Cars; /*What is the average mileage for the cars on our lot? */ SELECT AVG(Mileage) FROM Cars; /*What is the total value of cars sold? */ SELECT SUM(Price) FROM Cars WHERE Buyer IS NOT NULL; /*What car makes have we carried? Show each only once, sorted in ascending alphabetic order. */ SELECT DISTINCT Make FROM Cars ORDER BY Make ASC; /*Show the full record for any 4X4 vehicle. */ SELECT * FROM Cars WHERE CarTrim LIKE '4X4%'; /*The record for vehicle 1FAHP2EW1AG163911 is wrong. The car is really Taupe. Please correct that. */ SELECT Color /*Check current color*/ FROM Cars WHERE VIN = '1FAHP2EW1AG163911'; UPDATE Cars /*Update color*/ SET Color = 'Taupe' WHERE VIN = '1FAHP2EW1AG163911'; SELECT Color /*Check updated color*/ FROM Cars WHERE VIN = '1FAHP2EW1AG163911'; /* How many cars have we had in each of the top three colors? */ SELECT COUNT(Color) AS `Numbers of Top Three Colors` FROM Cars GROUP BY Color ORDER BY COUNT('Number Of Each Color') DESC LIMIT 3; /* Display the name, drivers license number and the total amount purchased for each customer. */ SELECT Customers.Name, Customers.DLNumb, IFNULL(Sum(Cars.Price), 0) FROM Customers LEFT JOIN Cars ON Customers.ID = Cars.Buyer GROUP BY Customers.Name, Customers.DLNumb; /* 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. 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. */ CREATE TABLE Luxury ( /*Create table Luxury*/ Make char (15) ); INSERT INTO Luxury /*Add the requested values into the table*/ VALUES ('Lincoln'), ('Cadillac'); SELECT Cars.* /*Select all rows in Cars with makes matching those in the Luxury table*/ FROM Cars INNER JOIN Luxury ON Cars.Make = Luxury.Make; INSERT INTO Luxury /*Insert new value to test is the code works dynamically*/ VALUES ('Lamborghini'); SELECT Cars.* /*Check if new value shows up in query*/ FROM Cars INNER JOIN Luxury ON Cars.Make = Luxury.Make;
Stuck with a problem? Got Error? Ask ChatGPT!
Copy Clear