Checking NULL Rates - SQL

1. Which classification logic produces $3$ incomplete keys and $1$ orphan?
   Answer: Incomplete: either component is NULL; orphan: both are non-NULL and no parent row matches both components When working with composite foreign keys, you need to track two separate concepts: incomplete keys (where NULL makes a key unknowable) and orphan keys (where a real, complete key has no matching parent). Getting the counts right depends entirely on how precisely each term is defined. Under option D's definitions, a key is incomplete if either component is NULL. Scanning the five pairs: $(1, \text{NULL})$, $(\text{NULL}, 2)$, and $(\text{NULL}, \text{NULL})$ each contain at least one NULL — that's exactly 3 incomplete keys. The remaining pairs are $(1, 2)$ and $(3, 4)$, both fully non-NULL. Of those, $(1, 2)$ has a matching parent row, but $(3, 4)$ does not — giving exactly 1 orphan. D is the only classification that produces $3 + 1$. Option A fails immediately because it defines incomplete as both components being NULL — only $(\text{NULL}, \text{NULL})$ qualifies, giving just 1 incomplete key, not 3. Option B makes the same "both NULL" mistake for incomplete keys, and compounds it with a broken orphan definition (matching on individual components rather than the full pair). Option C gets the incomplete definition right ("either component is NULL"), but defines orphans using left-join NULL output, which would catch incomplete rows too — conflating the two categories and producing the wrong orphan count. A useful rule of thumb: in composite key problems, NULL in any component poisons the entire key (making it incomplete), while orphan status only applies when the key is fully known but unmatched. Keeping these two ideas cleanly separate will help you navigate any foreign key classification question.
2. Which modification preserves the intended denominator and correctly exposes the NULL statuses?
   Answer: Change the filter to WHERE status <> 'cancelled' OR status IS NULL and keep the same average Whenever SQL filters out rows, those rows vanish from both the numerator and denominator of any aggregate — including AVG. The core issue here is how <> interacts with NULL values in SQL's three-valued logic. In SQL, NULL <> 'cancelled' evaluates to UNKNOWN, not TRUE. This means the WHERE status <> 'cancelled' clause silently eliminates all NULL rows — exactly the rows you're trying to measure. That's why the result is $0\%$: no NULL rows survive to be counted. The fix is answer C: changing the filter to WHERE status <> 'cancelled' OR status IS NULL. This explicitly rescues NULL rows by adding a second condition. Since status IS NULL evaluates to TRUE for those rows, the OR ensures they pass the filter regardless of the first condition's UNKNOWN result. Your denominator now includes all non-cancelled rows plus NULL rows, and the AVG correctly exposes the NULL rate. A is wrong because COUNT(status) skips NULLs by definition — it counts only non-NULL values — so the ratio still hides NULL rows rather than measuring them. B is wrong because WHERE status <> 'cancelled' AND status IS NULL is logically contradictory: it keeps only NULL rows, making the denominator just the NULL rows themselves and destroying the meaningful rate calculation. D is wrong because WHERE status = 'cancelled' OR status IS NOT NULL does the opposite — it includes cancelled rows and excludes NULLs, completely inverting the intended population. Study tip: Always remember that <> value silently drops NULLs in SQL. Whenever your filter might interact with NULLs, explicitly handle them with IS NULL or IS NOT NULL.
3. What does the query report, and how should the customer-level NULL rate be obtained?
   Answer: It reports about $52.6\%$; calculate the rate from customers before performing the one-to-many join Whenever you join a one-to-many relationship and then aggregate, you must think carefully about what each row in the result set represents — not each original entity. Here's the math. After joining, the result has $10 \times 10 + 90 \times 1 = 190$ rows. Of those, $100$ rows come from the $10$ NULL-region customers (each contributing 10 orders), and $90$ rows come from the non-NULL customers. So AVG(CASE WHEN c.region IS NULL THEN 1.0 ELSE 0 END) computes $\frac{100}{190} \approx 52.6\%$. Answer A is correct — it reports roughly $52.6\%$, and to get the true customer-level rate you should calculate the NULL proportion directly from the customers table before joining. Answer B is wrong because the join does not give each customer equal weight. Customers with more orders appear in more rows, so high-order customers are over-represented in the average. Answer C is wrong in its reasoning. While $190$ is indeed the joined denominator, the NULL rows total $100$ (not $90$), so $100/190 \approx 52.6\%$, not $47.4\%$. That figure would arise from mistakenly counting the non-NULL rows as the numerator. Answer D is wrong because COUNT(DISTINCT c.customer_id) would count distinct customers in each group, not compute a rate, and $5\%$ doesn't follow from any coherent calculation here. Study tip: Any time you compute a rate or average on a column from the "one" side of a one-to-many join, pre-aggregate on that table first. Otherwise, entities with more related rows silently inflate their weight.
4. Which conclusion is supported by these results?
   Answer: The missing-key rate is $8\%$ because $80$ transaction rows have customer_id IS NULL When working with COUNT() in SQL, the most important distinction to internalize is that COUNT(column_name) counts only non-NULL values, while COUNT(*) counts every row regardless of NULLs. This question tests whether you understand what each aggregate function actually measures. Here's the key logic: the extract has $1{,}000$ total rows, and COUNT(customer_id) returns $920$. Because COUNT(customer_id) skips NULL values, it tells you directly that $920$ rows have a valid customer key. That means $1{,}000 - 920 = 80$ rows have customer_id IS NULL. The missing-key rate is therefore $\frac{80}{1{,}000} = 8\%$, confirming C as the correct answer. A is wrong because the $700$ distinct customer IDs describe uniqueness among valid keys — not missingness. The gap between $1{,}000$ and $700$ reflects both NULLs and repeat customers, so you cannot isolate missing keys this way. B makes a similar error by subtracting $920$ (non-NULL count) from $1{,}000$ and calling the $80$-row gap a $20\%$ missing rate — but $\frac{80}{1{,}000} = 8\%$, not $20\%$. This is a simple arithmetic mistake in the final step. D is a trap for students who distrust COUNT(column_name) in the presence of duplicates. Duplicates are irrelevant here — COUNT(customer_id) ignores NULLs and counts duplicates normally, which is exactly the behavior you need. Study tip: Whenever you need to count missing values, remember that COUNT(*) - COUNT(column) isolates NULLs precisely — DISTINCT is a separate concern about uniqueness, not completeness.
5. Which result and predicate correctly implement the business-defined missing-value rate?
   Answer: $13\%$ using value IS NULL OR TRIM(value) = '' OR TRIM(value) = 'N/A' When a business defines "missing" more broadly than SQL does natively, your predicate must capture every form the business considers missing — not just what the database considers NULL. Here, four categories are defined as missing: SQL NULLs (30), empty strings (20), space-only strings (10), and 'N/A' sentinel values (5), totaling $30 + 20 + 10 + 5 = 65$ missing values out of 500. That gives a rate of $65 \div 500 = 13\%$, achieved with value IS NULL OR TRIM(value) = '' OR TRIM(value) = 'N/A'. The TRIM() call collapses space-only strings to empty strings, catching both empty and whitespace-only entries in a single condition, and the final clause catches the documented sentinel. That makes D the correct answer. Choice A only flags SQL NULLs — that's $30 \div 500 = 6\%$ — completely ignoring the three other business-defined missing forms. Choice B uses value IS NULL OR TRIM(value) = '', which catches NULLs, empty strings, and space-only strings $(30 + 20 + 10 = 60)$, reaching $12\%$, but it deliberately excludes 'N/A', contradicting the stated data-quality rule. Choice C applies NULLIF(TRIM(value), '') IS NULL, which converts empty-after-trim values to NULL and then checks for NULL — but 'N/A' trimmed is still 'N/A', not an empty string, so it is not caught by this predicate. C reaches the same $12\%$ as B, not $12\%$ with 'N/A' included as the answer claims. When you see a missing-value rate problem, always inventory all exceptional forms the business defines, then verify your predicate covers each one individually. A single overlooked sentinel can shift your rate — and your answer.
6. Which assessment correctly recalculates the company-wide row-level NULL rate?
   Answer: The correct rate is about $12.7\%$ because the regional missing counts must be divided by all records When aggregating data quality metrics across groups of different sizes, you must always weight by record count — never simply average the percentages. This question tests whether you understand the difference between a simple average of rates and a true row-level NULL rate. The correct approach is to count total NULL records across all regions, then divide by total records. Region East contributes $1{,}000 \times 0.10 = 100$ NULL values; Region West contributes $100 \times 0.40 = 40$ NULL values. Combined: $\frac{100 + 40}{1{,}000 + 100} = \frac{140}{1{,}100} \approx 12.7\%$. This is exactly what B describes — the correct company-wide NULL rate is approximately $12.7\%$. A is wrong because averaging two independently calculated percentages ignores the fact that East has ten times as many records as West. When group sizes differ, simple averaging introduces a mathematical distortion called Simpson's paradox-adjacent bias — the smaller, high-NULL region gets equal weight when it shouldn't. C is wrong because "twice the weight" is an arbitrary rule. Proper weighting isn't based on a fixed multiplier; it's based on actual record counts, which in this case give East a $\frac{1{,}000}{1{,}100}$ weight, not simply $2\times$. D is wrong because adding rate differences has no statistical basis — it's a fabricated operation that produces a meaningless result. A useful rule of thumb: whenever you see percentages calculated from groups of unequal size, always go back to raw counts before combining. The formula is $\frac{\sum(\text{NULLs per group})}{\sum(\text{records per group})}$, not $\frac{\sum(\text{rates})}{n}$.
7. Which SQL expression correctly calculates the NULL rate for email?
   Answer: 1.0 * (COUNT(*) - COUNT(email)) / COUNT(*), which returns $17.5\%$ When profiling data quality, the NULL rate measures what fraction of rows are missing a value — not what fraction have one. To find it, you need to count the gap between total rows and non-NULL values, then express that gap as a share of total rows. COUNT(*) counts every row regardless of NULLs, while COUNT(column) counts only non-NULL values. So the number of NULL rows is simply $\text{COUNT(*)} - \text{COUNT(email)} = 240 - 198 = 42$. Dividing by the total gives the NULL rate: $42 / 240 = 0.175$, or $17.5\%$. The 1.0 * prefix forces floating-point division so you don't get integer truncation. That's exactly what A computes, making it correct. B inverts the question entirely — it calculates the fill rate (how often email is present, $82.5\%$), not the NULL rate. It's measuring the complement of what you want. C uses the right numerator ($42$ missing rows) but divides by COUNT(email) ($198$) instead of COUNT(*) ($240$). That denominator represents only the non-NULL rows, so the result — about $21.2\%$ — has no meaningful real-world interpretation as a rate. D introduces COUNT(DISTINCT email), which removes duplicates. Since the passage explicitly allows duplicate emails, this count is unknown from the given information, so the calculation can't even be completed. A reliable memory anchor: NULL rate = missing ÷ total, and "total" always means COUNT(*). Any formula using COUNT(column) in the denominator is almost certainly a trap.
8. Which query returns exactly the intended set?
   Answer: SELECT o.* FROM orders o LEFT JOIN customers c ON c.customer_id = o.customer_id WHERE c.customer_id IS NULL; When working with "missing relationship" problems in SQL, your first instinct should be to reach for a LEFT JOIN. The core idea is: a LEFT JOIN preserves every row from the left table, filling the right table's columns with NULL whenever no match exists. That NULL is your signal that the foreign key relationship broke down. Option B is correct because it covers both failure cases in one clean pattern. The LEFT JOIN keeps all orders, and WHERE c.customer_id IS NULL then filters to only the rows where no matching customer was found — which happens both when o.customer_id IS NULL (no join is possible) and when o.customer_id has a value that simply doesn't exist in customers (the join attempted but failed). Both of your $20$ target rows satisfy this condition. Option A uses an INNER JOIN, which only returns rows where a match exists on both sides. Since it requires a valid customer match, c.customer_id can never be NULL after an inner join — so this query returns zero rows. Option C applies IS NULL AND NOT IN simultaneously, which is logically contradictory: if o.customer_id IS NULL, the NOT IN subquery behaves unpredictably (NULL comparisons in NOT IN can suppress all results), and more importantly the two conditions can never both be true for the same row. Option C captures neither group. Option D only retrieves the $8$ rows with non-NULL unmatched IDs — it explicitly excludes NULL customer IDs with o.customer_id IS NOT NULL, missing the $12$ NULL rows entirely. Your takeaway: whenever you need to find "unmatched or absent" foreign keys, LEFT JOIN + WHERE right-side key IS NULL is the reliable, all-in-one pattern.
9. Which query reliably identifies the missing reference keys despite the NULL in valid_codes?
   Answer: SELECT s.* FROM incoming_codes s WHERE s.code IS NOT NULL AND NOT EXISTS (SELECT 1 FROM valid_codes v WHERE v.code = s.code); Whenever you see NOT IN with a subquery that might return NULLs, stop and think carefully — this is one of SQL's most common and dangerous traps. Here's why it matters: SQL uses three-valued logic (TRUE, FALSE, UNKNOWN). When you compare any value to NULL using =, the result is UNKNOWN, not FALSE. The NOT IN operator internally checks whether your value equals any value in the list. If that list contains even one NULL, every comparison produces UNKNOWN, and NOT IN returns UNKNOWN rather than TRUE — meaning no rows are returned at all, regardless of what's actually in valid_codes. Option B is correct because NOT EXISTS sidesteps this entirely. It checks for the presence of a matching row, returning FALSE when no match is found and ignoring NULLs that don't participate in the join condition v.code = s.code. The s.code IS NOT NULL guard ensures you're only checking meaningful incoming values, giving you exactly the rows that are non-NULL and absent from valid references. Option A fails precisely because of the NULL trap described above — the NOT IN subquery pulls the NULL from valid_codes, poisoning every comparison and returning zero rows. Option C inverts the logic entirely: it returns rows whose code is found in valid_codes, which is the opposite of what's needed. Option D returns rows that are NULL or already matched — essentially identifying rows that should probably be excluded, not flagged as missing. Your takeaway: **never use NOT IN with a subquery unless you can guarantee the subquery returns no NULLs**. When in doubt, reach for NOT EXISTS instead — it's NULL-safe by design.

Start this quiz