SQL UPDATE Check Answers

From Juneday education
Jump to: navigation, search

Answers to check your progress for SQL UPDATE

These are proposed answers to the check your progress questions from the UPDATE exercise chapter.

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;

Suggested solution:

$ cat > cars.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;
$ sqlite3 cars.db < cars.sql

Check 02

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

sqlite> UPDATE cars SET Color='Silver', LicenseNumber='HND 900' WHERE LicenseNumber='ABC 124';
sqlite> select * from cars;
Volvo|Green|ABC 123
Honda|Silver|HND 900
Porsche|Green|BBC 666
Ferrari|Red|FST 667

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.

sqlite> INSERT INTO "cars" VALUES('Honda','Black','BLK 000');
sqlite> .headers on
sqlite> .mode column
sqlite> SELECT * from cars WHERE LicenseNumber = 'BLK 000';
Make        Color       LicenseNumber
----------  ----------  -------------
Honda       Black       BLK 000
      
sqlite> UPDATE cars SET Color = 'Gold' WHERE LicenseNumber = 'BLK 000';
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