SQL DELETE Check Answers

From Juneday education
Jump to: navigation, search

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!)