Quizz
The full text of this chapter, to read or revisit. To practise, do the exercises.
▶ Do the exercisesExercise 1 — Spot the valid INSERT
Which statement is valid in SQLite?
INSERT INTO trainers (name, badges) VALUES ('Sam', 1);INSERT trainers (name, badges) VALUES ('Sam', 1);INSERT INTO trainers SET name = 'Sam', badges = 1;INSERT INTO trainers VALUES name = 'Sam', badges = 1;Hint : INSERT INTO table (columns) VALUES (values).
Exercise 2 — The two keywords
Complete the statement so that it adds Sam, from Azure City.
Hint : The verb that adds rows, then the keyword that introduces the values.
Exercise 3 — Not listed
An optional column is not in the column list of an INSERT and has no default value. What does the new row contain for it?
NULL0Hint : Unless NOT NULL forbids it, a missing value is NULL.
Exercise 4 — Who changes?
UPDATE codemon SET level = 1; — which rows change?
Hint : SET without WHERE applies to every row.
Exercise 5 — Moving Jordan
Complete the statement so that Jordan now lives in Stone Ridge.
Hint : What changes, then in which rows.
Exercise 6 — Below ten
Codémon levels: 15, 10, 14, 11, 12, 13, 9 (Firochu) and 8 (Plantachu). What does DELETE FROM codemon WHERE level < 10; remove?
Hint : Strictly below 10.
Exercise 7 — Four verbs
Match each statement with what it does.
INSERTUPDATEDELETESELECTExercise 8 — changes()
What does SELECT changes(); return right after an UPDATE?
Hint : A count of affected rows — 0 is a warning sign.
Exercise 9 — Badgeless in Azure City
Complete the statement so that it removes the Azure City trainers who have no badge (only Jordan).
Hint : Both conditions must be true for the same row.
Exercise 10 — Lowercase alex
UPDATE trainers SET badges = 10 WHERE name = 'alex'; — the trainer is stored as 'Alex'. What happens?
Hint : = is case-sensitive, and a WHERE that matches nothing is not an error.
Exercise 11 — Two at once
How do you insert two rows with a single INSERT?
Hint : One pair of parentheses per row, commas between them.
Exercise 12 — Count the rows
Trainers: Alex (3 badges), Morgan (5), Taylor (2), Jordan (0); Morgan and Jordan are from Azure City. Match each statement with the number of rows it affects.
UPDATE trainers SET badges = badges + 1UPDATE trainers SET badges = 0 WHERE badges < 3DELETE FROM trainers WHERE hometown = 'Azure City' AND badges > 10DELETE FROM trainers WHERE name = 'Alex'4201Exercise 13 — After the big delete
Right after DELETE FROM codemon;, what does SELECT COUNT(*) FROM codemon; return?
0NULL8Hint : The rows are gone, the table is still there.
Exercise 14 — One more level
Complete the statement so that every fire Codémon gains exactly one level.
Hint : The new value is computed from the current one.
Exercise 15 — Mismatch
INSERT INTO codemon (name, type) VALUES ('Phantochu', 'ghost', 6); — what happens?
Hint : Every value needs a column.
Exercise 16 — Setting Firochu free
Which statement makes Firochu a wild Codémon (no trainer) without removing it?
UPDATE codemon SET trainer_id = NULL WHERE name = 'Firochu';DELETE FROM codemon WHERE name = 'Firochu';UPDATE codemon SET trainer_id = 0 WHERE name = 'Firochu';INSERT INTO codemon (trainer_id) VALUES (NULL);Hint : Wild means a NULL trainer_id — not 0, and not deleted.
Exercise 17 — Copies from a query
Complete the statement so that it adds a trainer named <name> II with 0 badges for every trainer who has at least 3 badges.
Hint : The rows come from a query, and the juniors start from zero.
Exercise 18 — Counting the damage
Two Codémons are fire type. Right after DELETE FROM codemon WHERE type = 'fire';, what does SELECT changes(); return?
2608Hint : changes() counts the rows the last statement affected.
Exercise 19 — Read the error
Match each statement with the error it produces (trainer_id 1 already exists, name is NOT NULL).
INSERT INTO trainers (hometown) VALUES ('Azure City')INSERT INTO trainers (trainer_id, name) VALUES (1, 'Sam')UPDATE trainers SET badge = 1DELETE trainers WHERE name = 'Sam'NOT NULL constraint failed: trainers.nameUNIQUE constraint failed: trainers.trainer_idno such column: badgenear "trainers": syntax errorExercise 20 — Copies from Azure City
Morgan and Jordan live in Azure City. How many rows does this statement add?
INSERT INTO trainers (name, hometown, badges)
SELECT name, hometown, 0
FROM trainers
WHERE hometown = 'Azure City';214Hint : One row per row returned by the SELECT.
Exercise 21 — January captures
Complete the statement so that it removes the Codémons captured in January 2023.
Hint : The keyword that follows DELETE, then the operator that understands %.
Exercise 22 — The latest id
What does last_insert_rowid() return?
Hint : Useful to link a second row to the one you just added.
Exercise 23 — Two changes in one
Is UPDATE trainers SET badges = badges + 1, hometown = 'Azure City' WHERE name = 'Alex'; valid?
Hint : SET a = …, b = …
Exercise 24 — Morgan's new team
Complete the script so that it adds Leafchu to Morgan's team (trainer_id 2), then lists Morgan's Codémons sorted by name.
Hint : Filter on the trainer column, sort on the name.
Exercise 25 — In a real database
Outside this sandbox, nothing resets between runs. You run INSERT INTO trainers (name) VALUES ('Sam'); twice. What is in the table?
Hint : Nothing forbids two trainers with the same name; the id makes them different.