Chapter 7 — Quiz

14 questions. Answers at the bottom.

Multiple choice

Q1. COUNT(*) counts: - A) Non-NULL values in the first column - B) Rows (including those with NULLs) - C) Distinct rows only - D) Columns

Q2. COUNT(phone) counts: - A) All rows - B) Rows where phone is not NULL - C) Distinct phones - D) NULL phones

Q3. GROUP BY loyalty_tier produces: - A) One row total - B) One row per distinct loyalty_tier - C) One row per customer - D) An error

Q4. The cardinal rule of GROUP BY: every SELECT-list column must be: - A) Numeric - B) Inside an aggregate or listed in GROUP BY - C) Aliased - D) Indexed

Q5. WHERE filters _ and HAVING filters _: - A) groups / rows - B) rows (before grouping) / groups (after aggregation) - C) columns / rows - D) both filter rows identically

Q6. Which is the correct place for COUNT(*) > 2? - A) WHERE - B) HAVING - C) SELECT - D) ORDER BY only

Q7. SUM, AVG, MIN, MAX treat NULL values by: - A) Counting them as 0 - B) Skipping them - C) Raising an error - D) Converting them to ''

Q8. The logical evaluation order is: - A) SELECT → FROM → WHERE → GROUP BY - B) FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY - C) FROM → GROUP BY → WHERE → SELECT - D) WHERE → FROM → HAVING → SELECT

Q9. Joining orders to order_items then COUNT(*) per customer counts: - A) Orders - B) Line items (not orders) — use COUNT(DISTINCT order_id) for orders - C) Customers - D) Products

Q10. To include customers with zero orders in a per-customer total, use: - A) INNER JOIN + SUM - B) LEFT JOIN + COALESCE(SUM(...), 0) - C) CROSS JOIN - D) HAVING SUM(...) = 0

Q11. GROUP BY ROLLUP (category) adds: - A) Nothing - B) A grand-total summary row - C) A new column - D) Duplicate rows

True/False

Q12. You can put an aggregate function like AVG(price) in a WHERE clause. (True / False)

Q13. After GROUP BY customer_id (the primary key), you may select first_name without aggregating it. (True / False)

Short answer

Q14. A report averages "average order value per region" by taking AVG of each region's average. Explain why this can differ from the true overall average order value.

---

Answer key

Q1 — B. COUNT(*) counts rows regardless of NULLs.

Q2 — B. COUNT(column) counts non-NULL values of that column.

Q3 — B. One summary row per distinct grouping value.

Q4 — B. Non-aggregated select columns must be in GROUP BY (or be functionally determined by the grouped PK).

Q5 — B. WHERE filters rows before grouping; HAVING filters groups after aggregation.

Q6 — B. Aggregate conditions go in HAVING (groups don't exist yet at WHERE time).

Q7 — B. All standard aggregates skip NULLs.

Q8 — B. FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY → (LIMIT).

Q9 — B. The join fans out to line items; COUNT(*) counts those. Use COUNT(DISTINCT order_id) to count orders.

Q10 — B. LEFT JOIN keeps everyone; COALESCE(SUM(...), 0) turns the resulting NULL into 0.

Q11 — B. ROLLUP adds subtotal/grand-total rows (with NULL in the rolled-up column).

Q12 — False. Aggregates can't appear in WHERE (it runs before grouping). Use HAVING.

Q13 — True. The PK functionally determines the other columns, and the SQL standard / PostgreSQL allow selecting them after grouping by the PK.

Q14. Averaging per-group averages weights each group equally, regardless of how many orders it contains. The true overall average weights each order equally. If regions have very different order counts, the two diverge (a form of Simpson's paradox). To get the true overall average, compute SUM(value) / COUNT(*) over all orders, not the mean of the regional means.

Scoring: 12–14 strong; 9–11 re-drill WHERE vs HAVING and fan-out counting; below 9, redo Exercises B–E.