Chapter 9 — Exercises

Run against mercado. (answer in Appendix) = worked solution in Answers. ⭐ = stretch.

Group A — Scalar subqueries

9.1 List products priced above the overall average price. (answer in Appendix)

9.2 For each product, show its price and how far above/below the average it is.

9.3 Show the single most expensive product's name (use a scalar subquery with MAX, or ORDER BY ... LIMIT 1).

9.4 ⭐ Show each product's price as a percentage of the most expensive product's price.

Group B — IN / list subqueries

9.5 All orders placed by gold customers (use IN). (answer in Appendix)

9.6 All products in categories whose name contains 'Audio' or 'Laptops'.

9.7 All reviews written by customers in the platinum tier.

9.8 ⭐ Explain why WHERE customer_id NOT IN (SELECT customer_id FROM orders) could misbehave, and rewrite it safely.

Group C — Derived tables (subquery in FROM)

9.9 Compute the average order value (average of per-order totals) using a derived table. (answer in Appendix)

9.10 Find the maximum number of items in any single order (per-order item count, then MAX).

9.11 ⭐ List the top 3 customers by total revenue using a derived table of per-customer totals.

Group D — Correlated subqueries & EXISTS

9.12 For each product, show the number of reviews it has (correlated subquery). (answer in Appendix)

9.13 Customers who have placed at least one order (use EXISTS). (answer in Appendix)

9.14 Customers who have never placed an order (use NOT EXISTS).

9.15 Products that have never been reviewed (use NOT EXISTS).

9.16 ⭐ Products that have been ordered but never reviewed (combine EXISTS and NOT EXISTS).

Group E — ANY / ALL

9.17 Products more expensive than every product in category 5 (Audio). Which operator? (answer in Appendix)

9.18 Products more expensive than at least one book (category 8).

9.19 ⭐ Rewrite WHERE category_id IN (3, 5) using = ANY.

Group F — Subquery vs join

9.20 Write "orders from gold customers" three ways: IN, JOIN, and EXISTS. Confirm they return the same rows. (answer in Appendix)

9.21 You need each gold order plus the customer's email in the output. Which of the three forms can do this directly, and why?

9.22 ⭐ Explain when EXISTS is safer than a JOIN for a "has any matching row" question (hint: fan-out).

Group G — Fix the bug

9.23 Errors with "more than one row returned by a subquery used as an expression" — why, and how to fix? (answer in Appendix)

SELECT name FROM products WHERE price = (SELECT price FROM products WHERE category_id = 3);

9.24 ⭐ "Products never ordered" returns nothing even though some products have no orders:

SELECT name FROM products WHERE product_id NOT IN (SELECT product_id FROM order_items);

(In Mercado order_items.product_id is NOT NULL, so reason about the general risk and rewrite with NOT EXISTS.)

Group H — Progressive project

9.25 Write a scalar-subquery query and an IN-subquery query for your domain.

9.26 Write a NOT EXISTS anti-join and a derived-table aggregate for your domain.

Self-check. If you reach for NOT EXISTS (not NOT IN) for anti-joins and can turn an aggregate-of-an-aggregate into a derived table, you've internalized closure. Next: combining whole result sets.