SQL, lesson 7
Until now you only read data. Three statements change it: INSERT adds rows, UPDATE changes
values in existing rows, and DELETE removes rows. They print nothing themselves, so to see the
effect you run a SELECT afterwards.
Name the table, list the columns you are filling, then give the values in the same order:
Listing the columns is optional, but it makes the statement safe if the table later gains a
column, and any column you leave out gets its default value (or NULL). When a column is
declared INTEGER PRIMARY KEY, SQLite fills it with the next free number if you leave it out.
UPDATE sets new values on every row that matches the condition. The new value can be
computed from the old one:
2 exercises to try what you just read.
WHEREAll right-hand sides are worked out from the row as it was before the update, so the order
of assignments in SET does not change the result.
Both UPDATE and DELETE without a WHERE apply to every row. DELETE FROM pantry;
empties the whole table, and there is no undo button. A good habit: write the WHERE first as a
SELECT, check that it returns exactly the rows you mean, then turn it into an UPDATE or
DELETE.
Text values in conditions are compared exactly, so WHERE item = 'rice' does not match 'Rice'.
In this lesson the harness runs a SELECT after your statements and prints the whole table,
so the expected output shows what the table must look like when you are done. Your own code
should not print anything.
The table stock has the columns item and qty:
A delivery has arrived. Write two statements:
qty of Bread.Butter with a qty of 4.After your code, the harness runs SELECT item, qty FROM stock ORDER BY item;. Expected output:
The table tasks has the columns id, title and done (1 means finished, 0 means not):
Write two statements:
4 as done.After your code, the harness runs SELECT id, title, done FROM tasks ORDER BY id;. Expected
output:
INSERT INTO pantry (item, qty) VALUES ('Rice', 3);
INSERT INTO pantry (item, qty) VALUES ('Oats', 1), ('Pasta', 4); -- several rows at onceUPDATE pantry SET qty = qty + 2 WHERE item = 'Rice';
UPDATE pantry SET qty = 0, note = 'used up' WHERE item = 'Oats'; -- two columns at onceDELETE FROM pantry WHERE qty = 0;SELECT * FROM pantry WHERE qty = 0; -- check first
DELETE FROM pantry WHERE qty = 0; -- then actitem | qty
Milk | 2
Eggs | 12
Bread | 0Bread|3
Butter|4
Eggs|12
Milk|2id | title | done
1 | Wash the car | 1
2 | Fix the fence | 0
3 | Sort the garage | 1
4 | Paint the shed | 0
5 | Clean windows | 02|Fix the fence|0
4|Paint the shed|1
5|Clean windows|0