Module 05 Knowledge Check
5 questions. Pass mark 4 out of 5. Answer every question before checking the answer key below, then retry after reading the feedback.
1. SELECT SUM(o.order_total) FROM orders o JOIN order_items i ON i.order_id = o.order_id returns roughly 3× the true revenue. Why?
- A. The totals column is wrong
- B. Fan-out - each order row is duplicated once per item, so its total is counted repeatedly
- C. SUM cannot be used with joins
- D. The join key is wrong
2. Which check catches fan-out every time?
- A. Reading the query aloud
- B. Comparing row counts before and after the join
- C. Adding an index
- D. Using SELECT *
3. Best fix when you need order revenue AND item counts together?
- A. Add DISTINCT to the SELECT
- B. Aggregate order_items to one row per order, then join
- C. Use RIGHT JOIN
- D. Use LIMIT
4. Why is SUM(DISTINCT order_total) a poor fix for fan-out?
- A. It is slower
- B. Two different orders can legitimately share the same total, so one gets dropped
- C. DISTINCT is invalid inside SUM
- D. It only works on text
5. On a client's database, a LEFT JOIN null-check returns 47 orders with no matching customer. What does that mean?
- A. 47 customers never ordered
- B. 47 orders reference a customer that does not exist - the foreign-key promise is broken in the data
- C. The join syntax is wrong
- D. 47 rows are duplicated
---
Answer Key and Explanations
Check these only after attempting every question.
1. B - Fan-out - each order row is duplicated once per item, so its total is counted repeatedly
Joining to a many-side lifts the grain to that side. Aggregating afterwards multiplies the one-side values.
2. B - Comparing row counts before and after the join
If the count changed and you did not intend it, the grain changed and any aggregate is now suspect.
3. B - Aggregate order_items to one row per order, then join
Pre-aggregating restores a one-to-one join so neither measure is duplicated.
4. B - Two different orders can legitimately share the same total, so one gets dropped
DISTINCT deduplicates values, not rows - it destroys legitimate repeated amounts.
5. B - 47 orders reference a customer that does not exist - the foreign-key promise is broken in the data
Orphan rows usually indicate an upstream deletion or import problem, and they quietly change results. The starter database returns 0 here - its foreign keys are intact - so this is what a broken one would look like by comparison.
Practical Check
Apply this module to your own work: complete the module activity for *Joins, Keys, Row Counts, and Duplicate Traps*, then write one sentence naming what your result shows and one naming what it does not.
Strong Answer Pattern
A strong answer names the task, the evidence used, the check performed, and the remaining limitation. It avoids "proved", "guaranteed", or "always" unless the evidence genuinely supports it.