The preview would update every tea
a SELECT with the same WHERE before any change
Northshore Coffee's menu, staff and orders, about to be changed by someone in a hurry.
Can you
- Write the SELECT that shows exactly the rows a change will touch
- Name the rows that must stay the same and check them afterwards
- Explain why a correct count is not proof of a correct change
The reading
Read the whole chapterTwo 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;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.
The ladder
Start at the first rung even if the idea looks familiar: it is answerable from the reading alone, and the climb is where the difficulty lives. A wrong answer sends you back to the paragraph that teaches it, then to a hint, then to the worked answer.
Learn
- Practise this rung
Level 1: Watch it answered
- Practise this rung
Level 2: Fill the blank
Practise
- Practise this rung
Level 3: Predict the result
- Practise this rung
Level 4: Write it yourself
Master
- Practise this rung
Level 5: Repair it
- Practise this rung
Level 6: Transfer it
Keep practising
The whole bank for this concept, in any order, with the same review, hint and worked answer.
Practice here is evidence for you and your instructor, never a grade. Grades live in Canvas.