SQLize Online / PHPize Online  /  SQLtest Online

CREATE TABLE customers ( id INT NOT NULL AUTO_INCREMENT, name VARCHAR(30), license VARCHAR(12), PRIMARY KEY(id) ); INSERT INTO customers (name, license) VALUES ("Sally Phillips","NY774055434"), ("Steven Smith","NY160423658"), ("Gary Moore","MA942299072"), ("Bill Barnes","CT896900826"), ("Mary Andersen","NY132497102"); CREATE TABLE vehicles ( id INTEGER PRIMARY KEY AUTO_INCREMENT, year INTEGER, make VARCHAR(20), model VARCHAR(20), trim VARCHAR(20), mileage INTEGER, color VARCHAR(8), price INTEGER, VIN VARCHAR(20), buyer INTEGER, FOREIGN KEY(buyer) REFERENCES customers(id) ); 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); SELECT COUNT(*) FROM vehicles WHERE buyer IS NULL; -- some students thought that purchased cars were awaiting delivery (as in our prior assignment) -- so I didn't penalize answers for Q1 and Q2 that used computations including purchased 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; SELECT * FROM vehicles WHERE trim LIKE '%4X4%'; UPDATE vehicles SET color = "Taupe" WHERE VIN = "1FAHP2EW1AG163911"; -- SELECT * FROM vehicles LIMIT 1; -- show the answer for Q6 SELECT count(*) FROM vehicles GROUP BY color ORDER BY COUNT(*) DESC LIMIT 3; SELECT name,license,sum(price) FROM vehicles JOIN customers on vehicles.buyer = customers.id GROUP BY name,license; CREATE TABLE luxury ( brand VARCHAR(20) ); INSERT INTO luxury VALUES ("LINCOLN"), ("CADILLAC"), ("LAMBORGHINI"); SELECT * FROM vehicles WHERE make IN (SELECT * FROM luxury);