Database:Exercise - Deleting rows of data -SQL DELETE

From Juneday education
Jump to: navigation, search

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 sqlite>

Example: List the files in the current directory using the command ls:

$ ls

Issue the .tables command inside the SQLite3 interactive shell:

sqlite> .tables

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 make:

sqlite> SELECT COUNT(*) FROM cars WHERE make='Honda';

If we want stats for various makes, we could also do this:

sqlite> SELECT COUNT(*), make FROM cars GROUP BY make;

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;

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.

You used 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

Check 01

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;
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');

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

Check 03

Restore the cars table from file.

Check 04

Delete all green cars whose license number starts with the character 'B';

Check the result.

Check 05

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?




Where to go next

« PreviousBook TOCNext »