Write your response in your own notes, then compare it with the sample answer. This practice is separate from the scored exam and does not affect your certificate.
Practice 1
A flexible table counts (plant TEXT, qty INTEGER) holds Lavender with qty 12, Fern with qty '7' and Basil with qty 'seven'. Explain what typeof(qty) shows for each, why sum(qty) gives 19.0, why WHERE qty > 10 returns Basil, and what a STRICT table would have done with the same inserts.
Compare with a sample answer
In a flexible table the declared INTEGER type is only a preference, its affinity. 12 is stored as an integer, '7' converts without loss to the integer 7, and 'seven' cannot be read as a number so it stays text. sum() treats the text as zero, giving 12 + 7 + 0 = 19.0, and because SQLite sorts all text after numbers, 'seven' counts as greater than 10, so Basil passes the filter. A STRICT table would accept 12 and convert '7' to 7, because nothing is lost, but would refuse 'seven' with cannot store TEXT value in INTEGER column.
The sample works the sum and links each result to affinity and to STRICT conversion.
Practice 2
An order for customer 99, who does not exist, has been accepted into nursery.db. Explain how this can happen, how you would check, and the habit that prevents it.
Compare with a sample answer
SQLite enforces foreign keys only on connections that switch them on, and the setting is not stored in the file, so a session that never ran PRAGMA foreign_keys = ON; accepts an orphan order. A mistyped PRAGMA is silently ignored, so it looks as if they were on when they were not. PRAGMA foreign_keys; reports 0 when they are off. The habit is to switch foreign keys on at the start of every session and in every program, and if an orphan has slipped in, rename nursery.db and rebuild it.
The sample ties the orphan to per-connection enforcement and the silently ignored mistyped PRAGMA.
Practice 3
Orders 1 to 4 have delivery charges of 0, 500, 500 and 0 pence and 2, 1, 3 and 1 order lines. Show by working why summing delivery across orders JOIN order_lines gives 2000 pence rather than 1000, and explain how a subquery fixes it.
Compare with a sample answer
The join produces one row per order line, so each order's delivery charge appears once per line: 2 × 0 + 1 × 500 + 3 × 500 + 1 × 0 = 2000, while the orders table alone gives 0 + 500 + 500 + 0 = 1000. Order 3's 500 pence is counted three times without any warning. The fix is to total order_lines per order_id in a subquery first, so there is one row per order, and then join that to orders; the result is (1000, 6750). Counting a join's rows before summing across it would have shown the problem.
The sample repeats each charge once per line and explains totalling each table at its own level.
Practice 4
Describe a safe routine for deleting the customers who have never ordered, from making a copy to being able to undo the change.
Compare with a sample answer
First make a copy with VACUUM INTO 'practice.db'; and work there with foreign keys switched on. Preview with SELECT id, name FROM customers WHERE id NOT IN (SELECT customer_id FROM orders); which lists Dev Rowan. Then type BEGIN;, run the DELETE with exactly the same WHERE, and check SELECT changes(); which should print (1,). If anything looks wrong, ROLLBACK; discards the change; if it is right, COMMIT; keeps it. Without a WHERE the DELETE would hit every row, and without BEGIN the shell saves each statement at once with no undo.
The sample covers the copy, the preview with the same WHERE, changes(), and BEGIN with ROLLBACK or COMMIT.
Practice 5
Using a lookup that pastes the typed town into WHERE town = '{town}', explain what the input x' OR '1'='1 does, why the town Bishop's Stortford also breaks it, and how a placeholder prevents both.
Compare with a sample answer
Pasting the input makes the query read WHERE town = 'x' OR '1'='1': the quote ends the text early and OR '1'='1' is true for every row, so every customer is returned. That is SQL injection, which can let an attacker read, change or destroy data. Bishop's Stortford breaks it innocently, because its apostrophe also ends the text early and leaves broken SQL, a syntax error. With a ? placeholder the value is sent separately, as data only, so the injection text simply matches no town and returns [], and Bishop's Stortford is looked up as an ordinary name.
The sample explains the early-closing quote, the always-true condition, the innocent apostrophe and data-only placeholders.
Practice 6
An assistant asked to raise plants under £5 by 10 per cent wrote the statement UPDATE plants SET price_pence = price_pence * 1.1; for the job. List what is wrong, write a corrected UPDATE, and say how many of the five plants it changes and their new prices.
Compare with a sample answer
The UPDATE has no WHERE, so it would change all five plants, and multiplying by 1.1 makes fractional pence, which the STRICT INTEGER column refuses. A corrected version is UPDATE plants SET price_pence = round(price_pence * 1.1) WHERE price_pence < 500; run after previewing the same WHERE, inside BEGIN so it can be rolled back. Under 500 pence are Lavender 450, Rosemary 350 and Basil 250, so it changes 3 rows: 450 × 1.1 = 495, 350 × 1.1 = 385 and 250 × 1.1 = 275. Fern (600) and Olive tree (2500) are left alone.
The sample names the missing WHERE and fractional pence, gives the corrected statement and works the three new prices.