Module 06 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. The main practical advantage of a CTE over a nested subquery is…
- A. It is always faster
- B. It names each step, so the query can be read and tested in stages
- C. It avoids joins
- D. It removes duplicates
2. A correlated subquery differs from a plain subquery because it…
- A. Returns multiple columns
- B. References a column from the outer query, so it is evaluated per outer row
- C. Cannot use WHERE
- D. Must be in the FROM clause
3. WHERE id IN (SELECT customer_id FROM ...) can behave unexpectedly when the subquery returns NULLs, because…
- A. IN cannot handle NULL
- B. NOT IN with a NULL in the list yields no rows at all
- C. It errors
- D. NULLs become zero
4. Building a query in small testable steps means…
- A. Writing it all then debugging
- B. Checking the row count and a sample after each stage
- C. Using more subqueries
- D. Avoiding CTEs
5. When does a CTE genuinely change results rather than just readability?
- A. Never
- B. When it changes the grain - e.g. aggregating before a join
- C. When it is recursive only
- D. When it uses ORDER BY
---
Answer Key and Explanations
Check these only after attempting every question.
1. B - It names each step, so the query can be read and tested in stages
Readability and testability matter more than micro-performance in analysis work.
2. B - References a column from the outer query, so it is evaluated per outer row
That per-row dependency is both its power and its performance cost.
3. B - NOT IN with a NULL in the list yields no rows at all
NOT IN with any NULL present evaluates to unknown for every row - a classic silent-empty-result bug.
4. B - Checking the row count and a sample after each stage
Verifying each stage localises the error instead of debugging a 60-line query at once.
5. B - When it changes the grain - e.g. aggregating before a join
Pre-aggregating inside a CTE is exactly the fan-out fix; that is a change in meaning, not style.
Practical Check
Apply this module to your own work: complete the module activity for *Subqueries and Common Table Expressions*, 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.