Skip to content

exercises.md

Latest commit

 

History

History
224 lines (136 loc) · 6.27 KB

exercises.md

File metadata and controls

224 lines (136 loc) · 6.27 KB

Section 5 — Modifying Data

What you'll practice: the "U" and "D" of CRUD. You already know how to INSERT (create) and SELECT (read) — this section is all about UPDATE and DELETE. You'll also learn the one habit that separates senior SQL developers from the rest: always write a SELECT first, then swap it for an UPDATE or DELETE. The reason is simple — an unbounded DELETE FROM product; erases the table.

Learning objectives

  • Write UPDATE ... SET ... WHERE ... statements
  • Write DELETE FROM ... WHERE ... statements
  • Update multiple columns in one statement
  • Apply updates that reference the column's own current value (e.g. stock = stock - 1)
  • Understand why WHERE is mandatory in practice
  • Use RETURNING to see the rows you just changed
  • Wrap destructive changes in a transaction and ROLLBACK if you got it wrong

How to run these exercises

psql -U postgres -d sql_exercise \
     -f 02-sql-fundamentals/05-modifying-data/schema.sql

psql -U postgres -d sql_exercise \
     -f 02-sql-fundamentals/05-modifying-data/seed.sql
SET search_path TO sqlf_modifying;
SELECT COUNT(*) FROM product;  -- expect 30

Heads up: because you'll be modifying the data, it's perfectly normal to re-run schema.sql + seed.sql between exercises to get back to a clean slate. Lean on that.

Exercises

Exercise 1 — Raise the price of one product

Use UPDATE to set the price of product id = 1 (the Wireless Mouse) to 34.99.

Verify:

SELECT id, name, price FROM product WHERE id = 1;

Exercise 2 — Update multiple columns at once

For product id = 4 (USB-C Hub), set stock to 50 and last_restocked to today (CURRENT_DATE).

Hint: SET stock = 50, last_restocked = CURRENT_DATE.

Verify: SELECT * FROM product WHERE id = 4;

Exercise 3 — Apply a 10% discount to every Book

Discount every row where category = 'Books' by 10%. Round the new price to 2 decimals.

Hint: SET price = ROUND(price * 0.9, 2) with WHERE category = 'Books'.

Verify:

SELECT id, name, price FROM product WHERE category = 'Books';

Exercise 4 — Increment stock in place

For every product in the 'Grocery' category, increase stock by 50.

Hint: SET stock = stock + 50.

Verify: pick two grocery rows — they should each have 50 more stock than the seed.

Exercise 5 — Use RETURNING

Write an UPDATE that deactivates every product whose stock = 0 (sets is_active = FALSE), and uses RETURNING id, name, stock to show which rows were changed.

Hint: UPDATE ... SET ... WHERE ... RETURNING ....

Question: in what situations is RETURNING more useful than running a follow-up SELECT?

Exercise 6 — Delete a single row

Delete product id = 29 (Broken Lamp).

Verify: SELECT COUNT(*) FROM product; should be 29.

Exercise 7 — Delete by condition

Delete every product that is is_active = FALSE.

Safety first: write it as a SELECT first to see exactly which rows you're about to remove, then convert it to a DELETE.

Verify: SELECT COUNT(*) FROM product WHERE is_active = FALSE; should be 0.

Exercise 8 — Delete by category and stock

Delete every 'Grocery' product with stock < 100.

Safety first: always run the matching SELECT first.

Exercise 9 — Try a DELETE with no WHERE

Don't actually commit this. Open a transaction, run DELETE FROM product;, inspect the row count, then ROLLBACK.

BEGIN;
DELETE FROM product;
SELECT COUNT(*) FROM product;  -- expect 0
ROLLBACK;
SELECT COUNT(*) FROM product;  -- expect back to whatever you had

Question: what happens if you COMMIT; instead of ROLLBACK;?

Exercise 10 — Reset to the seed

Re-run schema.sql and seed.sql to restore the original 30 rows. You'll use this reset pattern a lot going forward.

Exercise 11 — Bulk mark expensive items as inactive

For every product with price >= 100, set is_active = FALSE. Use RETURNING name, price so you can see which products were flipped.

Exercise 12 — Conditional price change

For every 'Clothing' product with stock > 100, drop the price by 15% (it's time to clear inventory). Round to 2 decimals.

Verify:

SELECT id, name, stock, price FROM product
WHERE category = 'Clothing' AND stock > 100;

Exercise 13 — Update with CASE

Write a single UPDATE that touches every product and sets a new price according to this rule:

  • if stock < 10, raise the price by 5%
  • if stock BETWEEN 10 AND 100, leave it alone
  • if stock > 100, drop the price by 10%

Hint: SET price = CASE WHEN ... THEN ... WHEN ... THEN ... ELSE price END.

Exercise 14 — Update based on another row (correlated)

Set the price of product id = 10 (Pragmatic Programmer) to be exactly the same as product id = 11 (Clean Code).

Hint:

UPDATE product
SET price = (SELECT price FROM product WHERE id = 11)
WHERE id = 10;

Verify: both rows should have the same price.

Exercise 15 — Delete with a subquery

Delete every product whose category has fewer than 3 products in it.

Hint:

DELETE FROM product
WHERE category IN (
    SELECT category FROM product GROUP BY category HAVING COUNT(*) < 3
);

Run the SELECT form first to confirm what you're about to wipe out.

Bonus challenges

Bonus 1 — TRUNCATE

Read \h TRUNCATE in psql. What's the difference between TRUNCATE product; and DELETE FROM product;? Which one is faster on a large table? Which one can be rolled back?

Bonus 2 — UPSERT

Read the Postgres docs for INSERT ... ON CONFLICT. Try to insert (id, name, category, price, stock, is_active) VALUES (1, 'Wireless Mouse v2', 'Electronics', 39.99, 100, TRUE) and have it update the existing row instead of erroring. (You'll need to add a PRIMARY KEY on id first — this foreshadows section 14.)

Bonus 3 — Audit the damage

Before running a destructive DELETE, write a query that saves the affected rows to a new table:

CREATE TABLE product_backup AS SELECT * FROM product WHERE is_active = FALSE;

What's this pattern called, and why is it a good habit?

Cleanup

DROP SCHEMA sqlf_modifying CASCADE;