Database:Exercise - Changing rows of data - SQL UPDATE

From Juneday education
Jump to: navigation, search

Work in progress

Remove this section when the page is production-ready.

Exercise for the Changing rows of data - SQL UPDATE 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 - Set up the cars database

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.

As usual, start by creating a fresh directory for this exercise and cd to that directory.

Put the following in a text file called cars.sql (in your exercises directory):

PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
CREATE TABLE IF NOT EXISTS cars (make TEXT, color TEXT, licensenumber TEXT PRIMARY KEY);
INSERT INTO "cars" VALUES('Volvo','Green','ABC 123');
INSERT INTO "cars" VALUES('Honda','Blue','ABC 124');
INSERT INTO "cars" VALUES('Porsche','Green','BBC 666');
INSERT INTO "cars" VALUES('Ferrari','Red','FST 667');
COMMIT;

Create the database my_cars by issuing the following command from the same directory as where your text file was created:

$ sqlite3 my_cars < cars.sql

Task 2 - Change the color of the Porsche

When issuing an SQL UPDATE statement, it is important to remember the WHERE clause, as it determines what rows should be updated. If the WHERE clause is left out entirely, all rows will be updated. We’ll use the LicenseNumber for the WHERE clause, since it is the primary key and guaranteed to be unique.

Log in to your database:

$ sqlite3 my_cars

Issue an SQL UPDATE statement to change the color of the Porsche to "Yellow":

sqlite> UPDATE cars SET color='Yellow' WHERE LicenseNumber='BBC 666';

You may want to verify that this worked, by selecting the whole row with said license number:

sqlite> SELECT * FROM cars WHERE LicenseNumber='BBC 666';

Task 3 - Change color and license number for the Volvo

It is possible to change more than one column (attribute or field) with a single UPDATE statement. If we were to update the Volvo to reflect that it now is "Grey" and has a new license number "AAA 111", we can do that with a single statement. Issue the following statement:

sqlite> UPDATE cars SET color='Grey', LicenseNumber='AAA 111' WHERE LicenseNumber='ABC 123';

Verify that the correct row was updated (for instance by selecting all rows from car).

4. Make a backup of your database my_cars

Since we have poked around and changed stuff in the cars table, and will continue to do so, we may want to make a backup. Just in case we later on make a mistake and mess things up. Redirect output from the shell to the file cars.bak and dump the cars table into that file:

sqlite> .output cars.bak
sqlite> .dump cars

Exit the interactive shell and examin the file cars.bak:

sqlite> .exit
$ cat cars.bak

The file contains a create table statement and insert statements for rows of cars representing the state of the table at the time of the dump.

Task 5 - Open the database again and mess things up

Enter the my_cars database again:

$ sqlite3 my_cars

Now, let’s make a mistake! We want to change the color of the Honda but forget to include the WHERE clause:

sqlite> UPDATE cars SET color='Orange';

To see what effect the statement (lacking the where clause) had, we’ll request all rows of the cars table:

sqlite> SELECT * FROM cars;
Volvo|Orange|AAA 111
Honda|Orange|ABC 124
Porsche|Orange|BBC 666
Ferrari|Orange|FST 667

Oops! Now all cars are Orange and our manager is looking annoyed. Let’s be quick and restore the table from the backup:

sqlite> DROP TABLE cars;
sqlite> .exit
$ sqlite3 my_cars < cars.bak
$ sqlite3 my_cars
sqlite> SELECT * FROM cars;
Volvo|Grey|AAA 111
Honda|Blue|ABC 124
Porsche|Yellow|BBC 666
Ferrari|Red|FST 667

What we actually did was to drop the table cars entirely (delete the table and all its contents). Then we exited from the interactive shell and ran all the statements from the cars.bak backup file. Remember, when you run the program sqlite3 you can give it the database to use (or create) as an argument. You may also redirect input from a file with SQL statements using the < operator in your shell. So, the line $ sqlite3 my_cars < cars.bak can be understood as: "Start sqlite3 on the database my_cars but run the commands in cars.bak instead of opening the interactive shell".

Task 6 - Things to think about for future lectures

From a testing perspective, it is possible to look at a table in a database and think of things that possibly could go wrong with it. The simple tables we have seen so far are far from perfect from a data integrity perspective. Try for instance to issue the following statement:

sqlite> UPDATE cars SET color='Gray' WHERE LicenseNumber='FST 667';

Did it work? Can you see what the problem with it was?

Here’s a hint. Let’s examine what unique names for Color exists:

sqlite> SELECT DISTINCT color FROM cars ORDER BY color;
Blue
Gray
Grey
Yellow

Hmm, Gray versus Grey. What does this mean? Gray is how the Americans (the Northern kind) spell the color (colour). Grey is how the English (British) spell it. But to our database, they are two completely different "Color" values, because Color was defined as just some text...

For that matter, even this is a perfectly valid statement:

sqlite> UPDATE cars SET color='Bengt Bedrup' WHERE LicenseNumber='FST 667';

We will talk more about such inconsistencies in future video lectures (and chapters) and what can be done to avoid such problems.

Check your progress

Check 01

Create a small cars.db database from the following SQL:

PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
CREATE TABLE IF NOT EXISTS cars (make TEXT, color TEXT, licensenumber TEXT PRIMARY KEY);
INSERT INTO "cars" VALUES('Volvo','Green','ABC 123');
INSERT INTO "cars" VALUES('Honda','Blue','ABC 124');
INSERT INTO "cars" VALUES('Porsche','Green','BBC 666');
INSERT INTO "cars" VALUES('Ferrari','Red','FST 667');
COMMIT;

Check 02

Change the Honda's color to Silver and its license number to "HND 900" using one single update statement.

Verify that only the Honda was updated.

Check 03

Insert a new Honda with color Black and license number 'BLK 000' (look at the SQL above for insert syntax!).

Check that it was inserted. Now change the color on this new Honda to 'Gold'.

Hint: think about how to update only this car!

Check that only the new Honda was updated.

Answers

You can find suggested answers here.

Links

Where to go next

« PreviousBook TOCNext »