Practise · The preview would update every tea

Opening your saved work…

The reading

Two languages, one database

SQL has two families of verbs, and it helps to keep the names straight because the project checklist uses them. DML, data manipulation, is INSERT, UPDATE and DELETE (and SELECT): it changes the rows, and the tables stay as they were. DDL, data definition, is CREATE, ALTER and DROP: it changes the tables themselves, their columns, types and constraints.

A database you can rebuild is a database you can experiment on. The browser gives you that for free: each change-mode exercise opens a fresh copy, runs your one statement, and shows you what the copy looks like after. The desktop shell gives it to you too, as long as you keep the build script that made the database.

Rule of thumb. DML changes rows. DDL changes tables. Both run without asking twice.

Look first

SELECT id, name, size, price
FROM menu_items
WHERE category = 'Tea'
ORDER BY id;

The seven rows the tea raise should touch, and nothing else. Open in the playground

Before any UPDATE or DELETE, run a SELECT with the same WHERE. It shows you the exact rows the change will touch, it shows you how many, and it costs nothing. The tea raise's preview is SELECT id, name, price FROM menu_items WHERE category = 'Tea': seven rows. If the preview shows 45, the WHERE is wrong, and you found out before anything moved.

Then name the rows that must stay the same. 'Only tea changes' means 'coffee, pastries, sandwiches, smoothies and seasonal items do not', and that is a second SELECT you can run after the change to prove it. A change you cannot describe as 'these rows, and not those' is a change you are not ready to make.

Counts are not enough. changes() tells you how many rows a statement touched, and seven is the right number for the tea raise. Seven is also the number you get if you raise seven of the wrong items. Look at the rows, not only the count.

Rule of thumb. Preview with the same WHERE, name what must not change, then change.

Change only what you meant

UPDATE menu_items
SET price = ROUND(price * 1.05, 2)
WHERE category = 'Tea';

Seven rows change. Run the preview's SELECT again to see the new prices. Open in the playground

UPDATE menu_items SET price = ROUND(price * 1.05, 2) WHERE category = 'Tea' raises seven prices. SET takes one or more column = value pairs, and the value can be an expression that uses the row's current values. WHERE picks the rows. Leave the WHERE off and the statement is still valid SQL, it still reports success, and it changes every row in the table. There is no confirmation step. The WHERE is the statement.

Read changes() immediately after. It is the number of rows the last statement touched, seven for the tea raise, 45 for the version without a WHERE. A zero means the WHERE matched nothing, which is a result to notice, not to accept.

An UPDATE has to satisfy every constraint an insert does. Set a price to NULL and the engine refuses: NOT NULL constraint failed, and the row is untouched.

Rule of thumb. An UPDATE without a WHERE changes every row and says nothing. Write the WHERE first.

The preview would update every tea · a SELECT with the same WHERE before any change · level 5 of 6

Catch the scope error

Level 5: Repair it · Exercise 1 of 1

A teammate's preview for the tea raise shows twelve rows. There are seven teas.

This preview is meant to show the rows that UPDATE menu_items SET price = price * 1.05 WHERE price < 5 would change if it were the tea raise. It shows items under five dollars, tea or not. Fix the preview so it shows id, name and price for Tea items priced below 5, ordered by id, and say what the original was previewing.

Finish line: 3 rows · columns id, name, price · in the order the question gives

Data: Northshore Coffee.

I would like help with this exercise

Table & column names only · ↑ ↓ choose · Tab insert · Esc dismiss

Open in the playground

Query results

Run your query to see the result here.

Practice here is evidence for you and your instructor, never a grade. Grades live in Canvas.