Part 5 of 5 · 4 min read
Exercises
Easy
Easy 1: Function or procedure (15 minutes)
State whether "compute a book's price after tax" and "archive every order older than a year" should each be a function or a procedure.
Done when: the first is correctly matched to a function and the second to a procedure.
Hint
Computing a value fits a function; performing a bulk action fits a procedure.
Easy 2: When does a trigger run (15 minutes)
Explain, in one sentence, when a trigger runs, and how that differs from calling a stored procedure.
Done when: the explanation correctly notes a trigger fires automatically on a data change, not by direct call.
Hint
A trigger fires automatically in response to a data change; a procedure must be called explicitly by name.
Easy 3: Name one risk of a trigger (15 minutes)
Name one concrete risk of adding a trigger that a developer unfamiliar with the schema might not anticipate.
Done when: a real risk, such as hidden extra work or a surprising side effect, is named.
Hint
A trigger can perform hidden extra work, or reject a write, without that behavior being visible in the calling application's own code.
Medium
Medium 1: Write a function (25 minutes)
Write a stored function computing the total value of an order, given its order_id, by summing price times quantity across its order_items.
Done when: a correct function definition using a query over order_items and books is given.
Hint
CREATE FUNCTION order_total(oid INTEGER) RETURNS NUMERIC AS $$ SELECT SUM(b.price * oi.quantity) FROM order_items oi JOIN books b ON oi.book_id = b.book_id WHERE oi.order_id = oid; $$ LANGUAGE SQL;
Medium 2: Write a trigger (25 minutes)
Write a trigger that prevents an INSERT into books if the price is negative.
Done when: a correct BEFORE INSERT trigger raising an error (or otherwise rejecting the row) for a negative price is given.
Hint
A BEFORE INSERT trigger checking NEW.price < 0 and raising an exception rejects the insert before it is stored.
Medium 3: Function versus application code (25 minutes)
Explain why "a book's price after a seasonal discount" might be better kept as application logic than as a stored function, if the discount rule changes every few weeks.
Done when: the explanation correctly ties frequent change to a preference for application-layer logic over database logic.
Hint
Frequently changing logic is easier to update, test and deploy in application code than in database objects that need their own migration process.
Hard
Hard 1: Diagnosing a slow bulk update (30 minutes)
A simple UPDATE books SET price = price * 1.05; affecting 2,000 rows takes far longer than expected. Describe one database-side cause worth checking, beyond the UPDATE statement itself.
Done when: an existing trigger performing extra work per row is correctly named as a plausible cause.
Hint
An existing trigger on books, firing once per updated row, can silently multiply a simple bulk update into far more work than the UPDATE statement alone suggests.
Hard 2: A trigger that enforces integrity a CHECK constraint cannot (35 minutes)
Describe a rule, such as "a customer cannot place more than 10 orders in a single day," that a plain CHECK constraint cannot express, but a trigger can, and explain why.
Done when: the explanation correctly notes that a CHECK constraint can only see the row being inserted, not aggregate information across other rows, while a trigger's logic can query other rows.
Hint
A CHECK constraint evaluates only the row being written, with no visibility into other existing rows; a trigger can run a query counting that customer's other orders for the same day.
Hard 3: Choosing where to enforce a shared rule (40 minutes)
Two different applications both write to orders. Argue for enforcing "an order's placed_on date cannot be in the future" at the database level rather than in either application's own code.
Done when: the argument correctly notes that database-level enforcement protects both applications equally, including any future one, without needing the rule duplicated and kept in sync across each.
Hint
A database-level constraint or trigger applies to every write regardless of which application performed it, removing the risk of the rule being implemented correctly in one application and forgotten in the other.