Chapter 13 — Exercises

Run against mercado — but wrap modifications in BEGIN ... ROLLBACK so you don't permanently change your practice data (or reload seed-sample.sql afterward). (answer in Appendix) = worked solution in Answers. ⭐ = stretch.

Safety habit for every exercise: before any UPDATE/DELETE, run its WHERE as a SELECT first.

Group A — INSERT

13.1 Insert a new supplier (name, country, contact_email). Then SELECT it back. (answer in Appendix)

13.2 Insert three new categories in a single statement.

13.3 Insert a new customer and use RETURNING to capture the generated customer_id and created_at. (answer in Appendix)

13.4 ⭐ Using INSERT ... SELECT, copy all cancelled orders' ids and dates into a new table cancelled_orders (create it first with two columns).

Group B — UPDATE (verify WHERE first!)

13.5 Raise the price of all Audio (category 5) products by 10%. First run the WHERE as a SELECT. (answer in Appendix)

13.6 Set customer 6's phone to '555-0106' and tier to 'silver' in one statement.

13.7 Use UPDATE ... FROM to set loyalty_tier = 'platinum' for customers whose total spend exceeds $1,500.

13.8 ⭐ Wrap an UPDATE in BEGIN; ... ROLLBACK;, verify with a SELECT between, and confirm the change is undone after ROLLBACK.

Group C — DELETE & TRUNCATE

13.9 Delete all reviews with a rating below 2 (verify the WHERE first). (answer in Appendix)

13.10 Explain what would happen if you ran DELETE FROM customers WHERE customer_id = 1; (customer 1 has orders). Which constraint is involved?

13.11 When would you use TRUNCATE instead of DELETE? Give a concrete Mercado-adjacent example (hint: a staging table).

13.12 ⭐ Explain three differences between DELETE FROM t; and TRUNCATE t;.

Group D — UPSERT

13.13 Upsert an inventory row for (product_id=1, warehouse_id=1) with quantity 99 — update the quantity if the row exists. (answer in Appendix)

13.14 Insert category ('Books','books') but do nothing if the slug already exists (ON CONFLICT DO NOTHING).

13.15 ⭐ Explain why ON CONFLICT is safer than "SELECT to check, then INSERT" in a multi-user system.

Group E — Safety & transactions

13.16 Describe the exact sequence of steps you'd take to safely run a risky UPDATE in production. (answer in Appendix)

13.17 You ran UPDATE products SET price = 0; (no WHERE) inside a transaction you haven't committed. What do you do?

13.18 ⭐ Why does naming columns in INSERT INTO t (a,b) VALUES (...) matter for long-term safety?

Group F — Fix the bug

13.19 This was meant to discount one product but changed them all — what's missing, and how would you have caught it before running? (answer in Appendix)

UPDATE products SET price = price * 0.5;

13.20 ⭐ A nightly "set today's exchange rate" job sometimes errors with a duplicate-key violation under load. Rewrite it as an upsert.

Group G — Progressive project

13.21 Insert sample rows into your project tables (with RETURNING), do a verified UPDATE, and a BEGIN/ROLLBACK-protected DELETE.

13.22 Use an upsert for an idempotent operation in your domain.

Self-check. If "run the WHERE as a SELECT first, then wrap in a transaction" is now automatic, you've learned the most important habit in this chapter. Next: building the tables themselves.