Chapter 6 — Exercises

Joins are the skill — so this is a big set. Run everything against mercado. Build the multi-table queries one join at a time, exactly as the chapter did. (answer in Appendix) = worked solution in Answers. ⭐ = stretch.

Group A — Two-table INNER JOINs

6.1 Show each order's order_id and order_date alongside the customer's first_name and last_name. (answer in Appendix)

6.2 Show each product's name and its supplier's name.

6.3 Show each product's name and its category's name.

6.4 Show each review's rating and body with the product name it's about.

6.5 ⭐ Show each address's city with the customer's full name, only for 'gold' and 'platinum' customers.

Group B — The progression (3+ tables)

6.6 For each order, list the customer's last name, the product name, and the quantity (orders → order_items → products, plus customers). (answer in Appendix)

6.7 Extend 6.6 to also show the product's category name (add categories).

6.8 Show, for delivered orders only, the customer name, product name, and line total (quantity * unit_price * (1 - discount)).

6.9 ⭐ Reproduce the chapter's six-table query: for every delivered order, show customer, sales rep, product, category, and line total. Then change the employees join to a LEFT JOIN and explain how the result changes.

Group C — LEFT JOIN and anti-joins

6.10 List every customer and their order_ids, including customers who have never ordered (use LEFT JOIN). (answer in Appendix)

6.11 Find all customers who have never placed an order (anti-join). (answer in Appendix)

6.12 Find all products that have never been reviewed.

6.13 Find all products that have never been ordered (no row in order_items).

6.14 ⭐ List every employee with their manager's name, including the employee who has no manager (self-join + LEFT JOIN).

6.15 ⭐ Find every (warehouse, product) pair that has no inventory row (cross join + anti-join).

Group D — Self-joins

6.16 Using employees, list each employee and their manager's full name. (answer in Appendix)

6.17 List all employees who report directly to the employee named 'Evan Wright'.

6.18 ⭐ List pairs of employees in the same department (each pair once, no self-pairs). (Hint: self-join on department with e1.employee_id < e2.employee_id.)

Group E — Fix the join bug

6.19 Returns 180 rows instead of 15 — why, and how do you fix it? (answer in Appendix)

SELECT o.order_id, c.last_name FROM orders o, customers c;

6.20 Means to keep all customers and attach only delivered orders, but it drops customers with no delivered order:

SELECT c.last_name, o.order_id
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.customer_id
WHERE o.status = 'delivered';

6.21 Errors with "column reference 'customer_id' is ambiguous":

SELECT customer_id, last_name FROM orders o JOIN customers c ON c.customer_id = o.customer_id;

6.22 ⭐ Totals look doubled. The analyst joined orders to order_items and summed an order-level value:

SELECT o.order_id, SUM(p.price) FROM orders o
JOIN order_items oi ON oi.order_id = o.order_id
JOIN products p ON p.product_id = oi.product_id
GROUP BY o.order_id;

Explain why one-to-many joins can cause over-counting (you'll fix grouping properly in Chapter 7).

Group F — Choose the join type

For each question, state which join type you'd use and why.

6.23 "Every order with its customer's email." (All orders have a customer.) (answer in Appendix)

6.24 "Every product, with its average rating if it has any reviews."

6.25 "Categories that contain no products."

6.26 ⭐ "Every customer and, if they have one, their default shipping address — but don't drop customers without an address."

Group G — Reverse engineering

6.27 Describe in English what this returns:

SELECT c.last_name, COUNT(o.order_id) AS orders
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.customer_id
GROUP BY c.customer_id, c.last_name
ORDER BY orders DESC;

(Aggregation is Chapter 7 — focus on what the join + LEFT JOIN accomplishes.)

Group H — Progressive project

6.28 Write a two-table inner join answering a real question in your domain.

6.29 Extend it to 3–4 tables.

6.30 Write an anti-join ("X that never Y") and, if your domain has a hierarchy, a self-join.

Self-check. If you can build a 4+ table query incrementally, write an anti-join for any "never" question, and explain the outer-join WHERE trap, you've mastered the most important SQL skill. Next: condensing joined rows into summaries.