Part 5 of 5 · 3 min read
Exercises
Easy
Easy 1: Combine with UNION (15 minutes)
Write a query listing every customer_id who ordered in January or February, with no duplicates.
Done when: a correct UNION query matching the pattern shown is given.
Hint
Combine the January and February customer_id queries with UNION.
Easy 2: UNION versus UNION ALL (15 minutes)
Explain in one sentence the difference between UNION and UNION ALL.
Done when: the explanation correctly states UNION removes duplicates and UNION ALL does not.
Hint
UNION deduplicates; UNION ALL does not.
Easy 3: Pick the right operator (15 minutes)
Name the correct set operation for "customers who ordered in January but never in February."
Done when: EXCEPT is correctly named.
Hint
EXCEPT correctly expresses "in the first but not the second."
Medium
Medium 1: INTERSECT in practice (25 minutes)
Write a query listing customer_ids that appear in both January's and February's order lists.
Done when: a correct INTERSECT query is given.
Hint
Combine the January and February customer_id queries with INTERSECT instead of UNION.
Medium 2: Why order matters for EXCEPT (25 minutes)
Explain what changes if the two queries in an EXCEPT statement are swapped.
Done when: the explanation correctly states the result flips to the opposite direction of difference.
Hint
Swapping the queries in EXCEPT reverses which side's unique rows are kept.
Medium 3: Choosing UNION ALL correctly (25 minutes)
Explain why combining "orders from 2025" and "orders from 2026" with UNION ALL, rather than UNION, is safe and faster here.
Done when: the explanation correctly notes that no order can belong to both years, so no duplicates are possible, making the duplicate check in UNION pure wasted work.
Hint
Since a single order has exactly one year, no row could ever be duplicated across the two queries, so UNION's deduplication step finds nothing to remove.
Hard
Hard 1: Rewrite EXCEPT as a subquery (30 minutes)
Rewrite "customers who ordered in January but not in February" using NOT IN or NOT EXISTS, instead of EXCEPT.
Done when: a correct rewrite using NOT IN or NOT EXISTS, producing the same result, is given.
Hint
SELECT customer_id FROM orders WHERE placed_on BETWEEN '2026-01-01' AND '2026-01-31' AND customer_id NOT IN (SELECT customer_id FROM orders WHERE placed_on BETWEEN '2026-02-01' AND '2026-02-28');
Hard 2: Column type mismatch (35 minutes)
Explain what error, or what unexpected behavior, could occur if one side of a UNION selects a numeric column and the other side selects a text column in the same position.
Done when: the explanation correctly identifies a type mismatch as either an outright error or an implicit, possibly surprising, conversion.
Hint
Most databases raise a type error for a clear mismatch like numeric versus text, though some permissive ones silently convert one side, which can produce confusing results.
Hard 3: Three-way comparison with set operations (40 minutes)
Using the three monthly order lists for January, February and March, describe how to find customers who ordered in all three months, using nested INTERSECT operations.
Done when: a correct expression chaining two INTERSECT operations across all three monthly result sets is given.
Hint
(january INTERSECT february) INTERSECT march keeps only customer_ids present in all three monthly result sets.