Chapter 10 — Exercises
Run against
mercado. (answer in Appendix) = worked solution in Answers. ⭐ = stretch.
Group A — UNION / UNION ALL
10.1 Build a single contact list (name, email) combining customers and suppliers. (answer in Appendix)
10.2 Produce a combined list of all category_ids that either contain a product over $1000 or contain a discontinued product. Use UNION (deduped).
10.3 Repeat 10.2 with UNION ALL and explain how (and why) the row count differs.
10.4 ⭐ Why would UNION ALL be both faster and correct if the two queries are guaranteed to produce disjoint sets? Give a Mercado example of disjoint queries.
Group B — INTERSECT
10.5 Customer ids that appear in both orders and reviews (customers who have ordered and reviewed). (answer in Appendix)
10.6 Product ids that have been both ordered and reviewed.
10.7 ⭐ Customer ids that are gold-tier and have placed a delivered order (combine a filter with INTERSECT).
Group C — EXCEPT
10.8 Product ids that have never been ordered (use EXCEPT). (answer in Appendix)
10.9 Customer ids who have ordered but never reviewed.
10.10 Category ids that contain no products. (Hint: all category ids EXCEPT those used by products.)
10.11 ⭐ Compare your 10.8 answer to the NOT EXISTS version from Chapter 9. Are the results identical? When would you prefer NOT EXISTS over EXCEPT?
Group D — Ordering & combining
10.12 Stack the names and prices of category 3 and category 5 products, sorted by price descending, in one query. (answer in Appendix)
10.13 ⭐ Write a three-way set operation with parentheses: customers who (ordered AND reviewed) but are NOT standard-tier.
Group E — Fix / predict
10.14 Errors — fix it: (answer in Appendix)
SELECT first_name, last_name FROM customers
UNION
SELECT name FROM suppliers;
10.15 A report combining two transaction sources used UNION and the totals came out low. Explain what UNION did that UNION ALL would not, and when each is correct.
10.16 ⭐ Predict whether these return the same number of rows, then verify:
SELECT category_id FROM products UNION SELECT category_id FROM products;
SELECT category_id FROM products UNION ALL SELECT category_id FROM products;
Group F — Set ops vs. alternatives
10.17 Express "products never ordered" as (a) EXCEPT and (b) NOT EXISTS. Note one advantage of each.
10.18 ⭐ You need "customers who ordered but never reviewed, with their email." Why can't EXCEPT give you the email directly, and what would you use instead?
Group G — Progressive project
10.19 Write a UNION ALL report, an INTERSECT "in both" query, and an EXCEPT difference for your domain.
10.20 ⭐ For each, justify your choice of UNION vs UNION ALL (or the ALL variants).
Self-check. If you choose
UNION ALLby default (andUNIONonly when you need dedup), and can reach forEXCEPT/INTERSECTto read like the English question, you're set. Next: naming and recursion with CTEs.