CSE Roadmap

Core tier · about 3 hours · 5 parts

Part 5 of 5 · 3 min read

Exercises

Easy

Easy 1: Multi-row insert (15 minutes)

Write one INSERT statement adding two new authors to authors.

Done when: a correct multi-row INSERT with two rows is given.

Hint

INSERT INTO authors (author_id, name) VALUES (20, 'Name One'), (21, 'Name Two');

Easy 2: Self-referencing update (15 minutes)

Write an UPDATE that increases every book's price by 5 percent.

Done when: a correct SET price = price * 1.05 statement is given.

Hint

UPDATE books SET price = price * 1.05;

Easy 3: Rollback versus commit (15 minutes)

Explain what ROLLBACK does if run immediately after COMMIT has already completed.

Done when: the explanation correctly states ROLLBACK can no longer undo the change, since it is already permanent.

Hint

Once COMMIT has run, the change is permanent; ROLLBACK afterward has nothing left to undo.

Medium

Medium 1: Delete via subquery (25 minutes)

Write a DELETE removing every order_items row belonging to orders placed before 2024.

Done when: a correct DELETE using a subquery against orders.placed_on is given.

Hint

DELETE FROM order_items WHERE order_id IN (SELECT order_id FROM orders WHERE placed_on < '2024-01-01');

Medium 2: Safe testing pattern (25 minutes)

Describe the steps to safely test a bulk UPDATE on books before making it permanent.

Done when: the steps correctly include BEGIN, the UPDATE, a SELECT to check results, and a final COMMIT or ROLLBACK decision.

Hint

BEGIN, run the UPDATE, SELECT the affected rows to check them, then COMMIT if correct or ROLLBACK if not.

Medium 3: Conditional update with existing values (25 minutes)

Write an UPDATE that caps every book's discount at 20, for any book currently above that.

Done when: a correct SET discount = 20 WHERE discount > 20 statement is given.

Hint

UPDATE books SET discount = 20 WHERE discount > 20;

Hard

Hard 1: Multi-row insert versus a loop of single inserts (30 minutes)

Explain, conceptually, why one multi-row INSERT of 100 rows is typically faster than 100 separate single-row INSERT statements against the same table.

Done when: the explanation correctly notes the per-statement overhead paid 100 times in the second approach, avoided in the first.

Hint

Each statement pays a fixed cost to parse and start executing; running one statement instead of a hundred pays that fixed cost only once.

Hard 2: A transaction spanning three tables (35 minutes)

Write a transaction that deletes a cancelled order entirely: its order_items rows, then the order row itself, with a check in between before committing.

Done when: a correct transaction with BEGIN, both DELETE statements in the right order, and a COMMIT or ROLLBACK choice is given.

Hint

BEGIN; DELETE FROM order_items WHERE order_id = 9001; DELETE FROM orders WHERE order_id = 9001; -- check; COMMIT; (or ROLLBACK).

Hard 3: Why order matters when deleting across related tables (40 minutes)

Explain why order_items must be deleted before orders in Hard 2's transaction, rather than the other way around, given the foreign key between them.

Done when: the explanation correctly states that deleting the order first would leave order_items rows referencing a non-existent order, which referential integrity would reject.

Hint

A foreign key requires every order_items row to reference an existing order; deleting the order first would leave a dangling reference, which the database would reject.