Chapter 15 — Exercises

Run against mercado (wrap object creation in BEGIN ... ROLLBACK or DROP afterward to keep things tidy). (answer in Appendix) = worked solution in Answers. ⭐ = stretch.

Group A — Views

15.1 Create a view order_summary showing order_id, customer full name, order date, status, and order total (join + aggregate). (answer in Appendix)

15.2 Create a view active_products exposing only active products' name, price, and category name. Query it with a WHERE price < 100.

15.3 Explain three reasons to use a view (simplification, security, abstraction) with a Mercado example of each. (answer in Appendix)

15.4 ⭐ Create an updatable view gold_customers with WITH CHECK OPTION. Try to update a row's tier to 'silver' through it and explain what happens.

Group B — Materialized views

15.5 Create a materialized view category_sales (revenue and order count per category). Query it. (answer in Appendix)

15.6 Refresh it after changing some data, and observe that the numbers only change after the refresh.

15.7 When should you use a materialized view instead of a plain view? Give two criteria. (answer in Appendix)

15.8 ⭐ Why does REFRESH MATERIALIZED VIEW CONCURRENTLY require a unique index on the matview? What does it buy you?

Group C — Functions

15.9 Write a SQL function order_total(order_id) returning the order's total. Use it in a SELECT. (answer in Appendix)

15.10 Write a PL/pgSQL function tier_for_spend(spend numeric) returning a tier name by thresholds.

15.11 ⭐ Write a function category_path(category_id) that returns the full category path as text (e.g., 'Electronics > Computers > Laptops') using a recursive CTE inside the function.

Group D — Triggers

15.12 Create a price_history table and a trigger that logs every products.price change (old and new). Test it with an UPDATE. (answer in Appendix)

15.13 Inside a trigger function, what do NEW and OLD refer to? When is each available (INSERT vs UPDATE vs DELETE)?

15.14 ⭐ Give two cases where a trigger is the right tool and two where a CHECK constraint or foreign key is better. Why prefer constraints when possible?

Group E — Choose the tool

For each need, pick view / materialized view / function / trigger and justify.

15.15 "A dashboard tile showing total revenue per category, refreshed hourly." (answer in Appendix)

15.16 "Hide the cost column from analysts but let them query everything else about products."

15.17 "Automatically stamp updated_at = now() on every row update."

15.18 ⭐ "A reusable calculation of shipping cost from weight and destination, used in many queries."

Group F — Pitfalls

15.19 A dashboard on a materialized view shows last week's numbers. What's wrong, and how do you fix it? (answer in Appendix)

15.20 ⭐ An INSERT into orders is mysteriously slow and also writes to a notifications table no one mentioned. What's the likely cause, and how would you investigate?

Group G — Progressive project

15.21 Create a view hiding a useful join in your domain, and a materialized view for a dashboard summary.

15.22 Write one function encapsulating a business rule, and (optional) one audit trigger.

Self-check. If you can choose correctly among view / matview / function / trigger for a given need — and explain the staleness and hidden-logic trade-offs — you can package SQL logic professionally. Next: PostgreSQL's advanced superpowers.