meet it

Real database jobs use every piece, one after another.

Real jobs need several statements in a row. CREATE TABLE builds the table. INSERT fills it. UPDATE keeps it up to date. Then queries with WHERE, ORDER BY and aggregates answer the questions.

This bake sale log builds a table and adds three cakes. Five more scones get sold, so an UPDATE records them. The last two queries list the best sellers and add up the takings.

When an answer looks wrong, you : read the error or the result, find the bug, fix it and run again. Every database expert does this every day.

CREATE TABLE sales (id INTEGER PRIMARY KEY, cake TEXT NOT NULL, price REAL, sold INTEGER);
INSERT INTO sales (cake, price, sold) VALUES ('Brownie', 1.5, 12), ('Scone', 1.25, 8), ('Flapjack', 1.0, 14);
UPDATE sales SET sold = sold + 5 WHERE id = 2;
SELECT cake, sold FROM sales WHERE sold > 10 ORDER BY sold DESC;
SELECT SUM(price * sold) AS takings FROM sales;

Stuck? Run one statement at a time and check each result before adding the next.

guess first

Every piece at once. What will this show?

Guess before you run it. Wrong guesses are how you learn, and nothing is marked.

CREATE TABLE jars (id INTEGER PRIMARY KEY, sweet TEXT, amount INTEGER);
INSERT INTO jars (sweet, amount) VALUES ('mints', 20), ('toffees', 5), ('jelly beans', 40);
UPDATE jars SET amount = amount - 10 WHERE sweet = 'mints';
SELECT SUM(amount) AS sweets FROM jars WHERE amount >= 10;

your turn

Your turn. Stock a tiny shop.

Fill the shelf with three items and their prices in pence. The queries then find the dearest item, count the items and add up the prices.

Your code

result

Your answer appears here as a table.

fix it

Seven seats were booked, but it says NULL. Debug it!

Broken code to fix

result

Your answer appears here as a table.

NULL means no row passed the WHERE, so there was nothing to add up. Compare the film name there with the ones in the INSERT.
= needs text to match exactly, capital letters and all.
Change 'ocean cats' to 'Ocean Cats' on line 3, then press run.

nailed it

you put every piece together. :D

Statements run in order: create, insert, update, then select.
Aggregates, WHERE and ORDER BY answer real questions.
To debug, read the clue, fix one thing, run again.

new words in your collection

debug

This lesson isn’t saved. A free account remembers every lesson you finish.