SQL interview question: Two Changes in One Transaction
Cancel the Order, Cleanly
A customer cancelled order 12 before it shipped. Cancelling means two things at once: its order_items rows are removed, and the order's status becomes 'cancelled' with total_amount set to 0. A crash in between would leave a ghost order.
Write a transaction doing both, then COMMIT.
Note: Multiple statements allowed. Forgetting COMMIT discards the work. Runs on a scratch copy.
Tables
orders 15 rows
- order_id integer PK
- customer_id integer → customers
- order_date text
- total_amount real
- status text
| order_id | customer_id | order_date | total_amount | status |
|---|---|---|---|---|
| 1 | 101 | 2024-01-20 | 249.99 | delivered |
| 2 | 101 | 2024-03-15 | 1999.99 | delivered |
| 4 | 101 | 2024-09-05 | 79.99 | delivered |
order_items 19 rows
- id integer PK
- order_id integer → orders
- product_id integer → products
- quantity integer
| id | order_id | product_id | quantity |
|---|---|---|---|
| 1 | 1 | 3 | 1 |
| 2 | 2 | 1 | 1 |
| 3 | 2 | 6 | 1 |