SQL DELETE Check Answers
Contents
Check your progress on DELETE - Answers
Check 01
Create a small database cars.db from the following SQL:
PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
CREATE TABLE cars (Make text,Color text, LicenseNumber text primary key);
INSERT INTO "cars" VALUES('Volvo','Green','ABC 123');
INSERT INTO "cars" VALUES('Honda','Silver','HND 900');
INSERT INTO "cars" VALUES('Porsche','Green','BBC 666');
INSERT INTO "cars" VALUES('Ferrari','Red','FST 667');
INSERT INTO "cars" VALUES('Honda','Gold','BLK 000');
COMMIT;
$ cat > cars.sql
PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
CREATE TABLE cars (Make text,Color text, LicenseNumber text primary key);
INSERT INTO "cars" VALUES('Volvo','Green','ABC 123');
INSERT INTO "cars" VALUES('Honda','Silver','HND 900');
INSERT INTO "cars" VALUES('Porsche','Green','BBC 666');
INSERT INTO "cars" VALUES('Ferrari','Red','FST 667');
INSERT INTO "cars" VALUES('Honda','Gold','BLK 000');
COMMIT;
$ sqlite3 cars.db < cars.sql
Check 02
Delete all cars whose make is Porsche or whose color is either Gold or Silver (regardless of make).
Expected result:
sqlite> SELECT * FROM cars;
Make Color LicenseNumber
---------- ---------- -------------
Volvo Green ABC 123
Ferrari Red FST 667
Suggested solutions:
sqlite> DELETE FROM cars WHERE Make = 'Porsche' OR Color IN ('Gold', 'Silver');
sqlite> .headers on
sqlite> .mode column
sqlite> SELECT * FROM cars;
Make Color LicenseNumber
---------- ---------- -------------
Volvo Green ABC 123
Ferrari Red FST 667
OR
sqlite> DELETE FROM cars WHERE Make = 'Porsche' OR Color = 'Gold' OR Color = 'Silver';
sqlite> select * from cars;
Make Color LicenseNumber
---------- ---------- -------------
Volvo Green ABC 123
Ferrari Red FST 667
Check 03
Restore the cars table from file.
sqlite> DROP TABLE cars;
sqlite> .read cars.sql
sqlite> select * from cars;
Make Color LicenseNumber
---------- ---------- -------------
Volvo Green ABC 123
Honda Silver HND 900
Porsche Green BBC 666
Ferrari Red FST 667
Honda Gold BLK 000
Check 04
Delete all green cars whose license number starts with the character 'B';
sqlite> DELETE FROM Cars WHERE Color = 'Green' AND LicenseNumber LIKE 'B%';
sqlite> SELECT * FROM cars;
Make Color LicenseNumber
---------- ---------- -------------
Volvo Green ABC 123
Honda Silver HND 900
Ferrari Red FST 667
Honda Gold BLK 000
Check 05
Issue the command DELETE * FROM cars;. What was the result?
sqlite> DELETE * FROM cars;
Error: near "*": syntax error
Explain the message in terms of the syntax for the DELETE statement.
The DELETE syntax doesn't have a * after the DELETE keyword. In SQL, the * means "every column" and is used in the SELECT statement. DELETE, however, operates on whole rows of data. Thus it makes no sense to talk about "every column" since deleting a row obviously deletes all columns for that row.
What was wrong?
The error message said that there was a syntax error near the * symbol. The reason for the syntax error was that there is no such symbol allowed between DELETE and FROM (in fact, there cannot be anything between DELETE and FROM, look at the syntax chart!)
