A transaction lets you test a change and then undo it completely.
What to build
A transaction that deletes cancelled orders, counts what is left, and then rolls back.
Requirements
- Inside a transaction, delete the orders whose status is
cancelled. - Still inside it, count the remaining orders.
- Roll back, then count the orders again.
Tables
Each test fills these tables with its own rows, then runs your SQL:
CREATE TABLE orders (
id INTEGER PRIMARY KEY,
customer TEXT NOT NULL,
status TEXT NOT NULL
);
Output
Two results: one column left_in_trial, then one column after_rollback. Your result is compared with the expected one, column names included, so name each column exactly as asked (use AS).
Constraints
The table may be empty.
Example
When a test starts with:
INSERT INTO orders VALUES (1, 'A', 'cancelled'), (2, 'B', 'paid'), (3, 'C', 'cancelled');
your SQL should print:
left_in_trial
-------------
1
(1 row)
after_rollback
--------------
3
(1 row)
Why it works
Inside the transaction the deletes are visible, so the first count is lower. ROLLBACK undoes every change since BEGIN, so the second count is back to the original number.
Examples
CREATE TABLE orders ( id INTEGER PRIMARY KEY, customer TEXT NOT NULL, status TEXT NOT NULL ); INSERT INTO orders VALUES (1, 'A', 'cancelled'), (2, 'B', 'paid'), (3, 'C', 'cancelled');
left_in_trial ------------- 1 (1 row) after_rollback -------------- 3 (1 row)
CREATE TABLE orders ( id INTEGER PRIMARY KEY, customer TEXT NOT NULL, status TEXT NOT NULL ); INSERT INTO orders VALUES (1, 'X', 'paid');
left_in_trial ------------- 1 (1 row) after_rollback -------------- 1 (1 row)
Each test makes its own tables, runs your SQL on them, and compares what your queries return. Changes never carry over from one test to the next.
Hints
Hint 1
Start with `BEGIN;`, and finish with `ROLLBACK;` to undo everything since then.
Hint 2
A SELECT inside the transaction sees the deleted rows as gone.
Solution
Show one correct solution (try it yourself first)
BEGIN; DELETE FROM orders WHERE status = 'cancelled'; SELECT COUNT(*) AS left_in_trial FROM orders; ROLLBACK; SELECT COUNT(*) AS after_rollback FROM orders;