SQLize Online / PHPize Online  /  SQLtest Online

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); SELECT * FROM Cars; /*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 Color, COUNT(*) AS `Number Of Each Color` FROM Cars GROUP BY Color ORDER BY COUNT('Number Of Each Color') DESC LIMIT 3; 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; CREATE TABLE Luxury ( Make char(15), CONSTRAINT CarsMake FOREIGN KEY (Make) REFERENCES Cars(Make) ); INSERT INTO Luxury VALUES ('Lincoln'), ('Cadillac'); SELECT Cars.* FROM Cars INNER JOIN Luxury ON Cars.Make = Luxury.Make;