Chapter 13 — Quiz

13 questions. Answers at the bottom.

Multiple choice

Q1. Which is not a data-modification statement? - A) INSERT - B) UPDATE - C) SELECT - D) DELETE

Q2. UPDATE products SET price = 0; (no WHERE) does what? - A) Updates one row - B) Updates every row in the table - C) Errors - D) Nothing

Q3. Best practice before running an UPDATE/DELETE is to: - A) Add an index - B) Run the same WHERE as a SELECT to see affected rows - C) Use TRUNCATE - D) Drop the table first

Q4. RETURNING lets you: - A) Roll back a transaction - B) Get back columns from the rows you just modified (e.g., a generated id) - C) Filter rows - D) Create a table

Q5. To safely make a change reversible, wrap it in: - A) A subquery - B) BEGIN ... COMMIT/ROLLBACK - C) A CTE - D) TRUNCATE

Q6. TRUNCATE differs from DELETE in that it: - A) Can use a WHERE - B) Removes all rows fast, can reset identity, fires no row triggers - C) Is slower - D) Cannot be undone in a transaction (it can, in PostgreSQL)

Q7. INSERT ... ON CONFLICT (cols) DO UPDATE is used for: - A) Deleting duplicates - B) Atomic insert-or-update (upsert) - C) Joining tables - D) Creating indexes

Q8. In ON CONFLICT ... DO UPDATE SET x = EXCLUDED.x, EXCLUDED refers to: - A) The existing row - B) The row you attempted to insert - C) A deleted row - D) The previous transaction

Q9. Deleting a customer whose orders reference them (with a restricting FK) will: - A) Silently delete the orders - B) Fail, preserving referential integrity - C) Set the orders' customer_id to 0 - D) Succeed and orphan the orders

Q10. The safest way to insert is to: - A) Use positional INSERT INTO t VALUES (...) - B) Name the columns: INSERT INTO t (a, b) VALUES (...) - C) Always insert all columns - D) Insert one row at a time only

True/False

Q11. A multi-row INSERT is generally faster than many single-row INSERTs. (True / False)

Q12. ON CONFLICT (upsert) is just convenience — a "SELECT then INSERT" check is equally safe under concurrency. (True / False)

Short answer

Q13. Describe the professional sequence for running a risky UPDATE in production, and why each step matters.

---

Answer key

Q1 — C. SELECT reads; the others modify (DML).

Q2 — B. No WHERE = every row. The classic disaster.

Q3 — B. Preview the rows with a SELECT using the same WHERE.

Q4 — B. RETURNING returns columns from modified rows.

Q5 — B. Transactions give you ROLLBACK — your undo button.

Q6 — B. TRUNCATE is fast, whole-table, resets identity (optionally), and skips row triggers. (It is transactional/rollback-able in PostgreSQL.)

Q7 — B. Atomic upsert.

Q8 — B. EXCLUDED is the would-be-inserted row.

Q9 — B. A restricting foreign key blocks the delete, protecting integrity.

Q10 — B. Name the columns — robust against schema changes and far more readable.

Q11 — True. One statement with many rows beats many separate statements (fewer round trips/transactions). COPY is faster still (Ch. 31).

Q12 — False. Check-then-insert is racy: two sessions can both check, both insert, and one violates the unique constraint. ON CONFLICT is atomic.

Q13. (1) Run the WHERE as a SELECT to confirm exactly which rows are affected and the count looks right. (2) BEGIN a transaction. (3) Run the UPDATE. (4) Re-SELECT to verify the result. (5) COMMIT if correct, ROLLBACK if not. Each step turns an irreversible, blind change into a verified, reversible one.

Scoring: 11–13 you write safely; 8–10 re-read the transaction/WHERE habits; below 8, redo Group B and E.