Database:Exercise - Deleting rows of data -SQL DELETE
- 1 Work in progress
- 2 Exercise for the Deleting rows of data -SQL DELETE chapter
- 3 Check your progress
- 4 Links
Work in progress
Remove this section when the page is production-ready.
Exercise for the Deleting rows of data -SQL DELETE chapter
Notes on typography
Commands are written in a
fixed width font and commands issued in a terminal in your computer shell are prefixed with a
Commands issued inside the SQLite3 interactive shell are prefixed with
Example: List the files in the current directory using the command
.tables command inside the SQLite3 interactive shell:
Task 1 - Open up the cars table and restore it from backup
Hint: We have a page about Absolute and relative Paths which can be helpful for understanding how to copy files from one place to another
As usual, create a fresh empty directory for this exercise and
cd to that directory.
NOTE: you need to copy the cars.sql etc from the previous exercise directory, in order to have a database to play with!
Example (copy the sql file from ../update/my_cars.sql to current directory:
$ cp ../update/my_cars.sql .
Or if your cars.sql (or whatever you named it) is in a directory called ../UPDATE/cars.sql:
$ cp ../UPDATE/cars.sql .
Since we played around in the last exercises with various ways to mess up our data in the cars table, let’s drop the table cars and restore it from the backed up data in the
cars.bak file (which you created in the last exercise session - if you don’t have it, ask a friend or teacher to give it to you).
We will do this in two steps:
Open the my_cars database and drop the table cars:
$ sqlite3 my_cars
sqlite> DROP TABLE cars; sqlite> .exit
Now restore the table cars from the cars.bak file.
Expand using link to the right if you forgot how to restore the database.
$ sqlite3 my_cars < cars.bak
You may "login" (start the database interactively) and verify that the cars table should now look like this:
sqlite> SELECT * FROM cars; Volvo|Grey|AAA 111 Honda|Blue|ABC 124 Porsche|Yellow|BBC 666 Ferrari|Red|FST 667
Task 2 - Let’s delete all we know about Hondas!
If we would like to delete the row with the Honda, there are several ways we could do that. We could issue a delete statement and in the where clause put a criteria on either Make, Color or LicenseNumber. But, LicenseNumber is the only column (field or attribute in a row) that is unique to the whole table, because it is that primary key (and primary keys guarantee unique values). Therefore, I recommend that we use LicenseNumber as primary key. We will see why later.
The following will delete the row with the Honda (since it has the LicenseNumber of ABC 124):
sqlite> DELETE FROM cars WHERE LicenseNumber='ABC 124';
We may check if there are any Hondas at all left in the table (there was however only one before):
sqlite> SELECT * FROM cars WHERE make='Honda';
No rows will be returned if all went well. Alternatively, we could count occurrences of rows with Honda for
sqlite> SELECT COUNT(*) FROM cars WHERE make='Honda'; 0
If we want stats for various makes, we could also do this:
sqlite> SELECT COUNT(*), make FROM cars GROUP BY make; 1|Ferrari 1|Porsche 1|Volvo
Task 3 - Insert a new Honda
Let’s insert back a new Honda with the same properties as the one we just deleted:
sqlite> INSERT INTO "cars" VALUES('Honda','Blue','ABC 124');
Task 4 - Change the color of the Volvo to Blue
Now, let’s do a paint job on the Volvo and update the row for the Volvo to show that it is now Blue (same as the Honda):
sqlite> UPDATE cars SET color='Blue' WHERE LicenseNumber='AAA 111';
Let’s run some stats on color:
sqlite> SELECT COUNT(*), color FROM cars GROUP BY color; 2|Blue 1|Red 1|Yellow
Make a mental note of the syntax above, using COUNT(*) and the GROUP BY clause. When you want to aggregate rows, for instance for counting, you use GROUP BY in order to specify on what column you want to aggregate.
Task 5 - Delete the Honda again, in a less smart manner
Read Q2 again and think about the advice to carefully write the criteria in the WHERE clause. Let’s try what would happen if we’d were to do something careless as using Color for criteria now that we know that we have two Blue cars:
sqlite> DELETE FROM cars WHERE color='Blue';
Next, list all the cars. What happened to the Volvo? Why?
Expand using link to the right to see the answer.
color='Blue' as the criteria for which rows to delete, and there were two rows which matched this criteria. You should always use a unique column (like LicenseNumber) if you only aim to delete one row of data. If you are unsure, there's a great way to play it safe before performing a DELETE statement - Use the same WHERE clause in a SELECT statement and verify that you get the right rows back. In the example above, that would be performing the following SELECT statement:
sqlite> SELECT * FROM cars WHERE color='Blue';
That would have shown you both the Honda and the Volvo, which would have alerted you about your mistake (since you planned to delete only the Honda). Restore the cars table and try it out!
Check your progress
Note: the coding style of this example is inconsistent with other examples. We are reviewing this book in order to make names more consistent. For instance,
LicenseNumber is a horrible choice, where
license_number would have been much better. Note, however, that SQLite is case insensitive, when it comes to names. Note also, that there is little consensus on coding style for SQL, but some standards have been proposed by various sources.
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;
Delete all cars whose make is Porsche or whose color is either Gold or Silver (regardless of make).
sqlite> SELECT * FROM cars; make color LicenseNumber ---------- ---------- ------------- Volvo Green ABC 123 Ferrari Red FST 667
Restore the cars table from file.
Delete all green cars whose license number starts with the character 'B';
Check the result.
Issue the command
DELETE * FROM cars;. What was the result?
Explain the message in terms of the syntax for the DELETE statement.
What was wrong?