SQL Quiz: Checking Null Rates

9 questions · exam conditions

0:00

Checking Null RatesQuestion 1 of 9

A customers table has $100$ customers. Exactly $10$ have region IS NULL. Each of those $10$ customers has $10$ orders, while each of the other $90$ customers has one order. An analyst joins customers to orders and calculates AVG(CASE WHEN c.region IS NULL THEN 1.0 ELSE 0 END) over the joined rows.

What does the query report, and how should the customer-level NULL rate be obtained?

It reports about

52.6\%

; calculate the rate from customers before performing the one-to-many join

It reports exactly

10.0\%

; the average automatically gives each customer equal weight after the join

It reports about

47.4\%

; calculate the rate from orders because orders determine the joined denominator

It reports exactly

5.0\%

; replace AVG with COUNT(DISTINCT c.customer_id) after the join

← Back to quizzes

SQL Quiz

SQL Quiz: Checking Null Rates

Practice Checking Null Rates in SQL with focused quiz questions that help you check what you know, review explanations, and build confidence with test-style prompts.

What this quiz covers

This quiz focuses on Checking Null Rates, giving you a quick way to practice the rules, question types, and explanations that matter most for SQL.

How to use this quiz

Try each quiz question before looking at the correct answer. Use the explanations to review missed ideas, then come back to similar questions until the pattern feels familiar.

All questions

Question 1

What does the query report, and how should the customer-level NULL rate be obtained?

It reports about $52.6\%$ ; calculate the rate from customers before performing the one-to-many join (correct answer)
It reports exactly $10.0\%$ ; the average automatically gives each customer equal weight after the join
It reports about $47.4\%$ ; calculate the rate from orders because orders determine the joined denominator
It reports exactly $5.0\%$ ; replace AVG with COUNT(DISTINCT c.customer_id) after the join

Explanation: 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.

Question 2

In orders, $12$ rows have customer_id IS NULL, and $8$ additional rows have non-NULL customer IDs that do not exist in customers. customers.customer_id is a non-NULL primary key. The goal is to return all $20$ orders with either an absent foreign key value or an unmatched foreign key.

Which query returns exactly the intended set?

SELECT o.* FROM orders o JOIN customers c ON c.customer_id = o.customer_id WHERE c.customer_id IS NULL;
SELECT o.* FROM orders o LEFT JOIN customers c ON c.customer_id = o.customer_id WHERE c.customer_id IS NULL; (correct answer)
SELECT o.* FROM orders o WHERE o.customer_id IS NULL AND o.customer_id NOT IN (SELECT customer_id FROM customers);
SELECT o.* FROM orders o WHERE o.customer_id IS NOT NULL AND NOT EXISTS (SELECT 1 FROM customers c WHERE c.customer_id = o.customer_id);

Explanation: 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.

Question 3

A child table has a composite foreign key (region_id, account_id). Five rows contain these pairs: (1, 2), (1, NULL), (NULL, 2), (3, 4), and (NULL, NULL). The parent contains (1, 2) but not (3, 4). A key is incomplete if either component is NULL. An orphan must have both components present but no matching parent pair.

Which classification logic produces $3$ incomplete keys and $1$ orphan?

Incomplete: both components are NULL; orphan: both are non-NULL and at least one parent component matches
Incomplete: both components are NULL; orphan: either component is non-NULL and no parent row matches either component
Incomplete: either component is NULL; orphan: every row for which a left join produces any NULL parent column
Incomplete: either component is NULL; orphan: both are non-NULL and no parent row matches both components (correct answer)

Explanation: 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.

Question 4

Region East has $1{,}000$ records with a phone NULL rate of $10\%$ . Region West has $100$ records with a phone NULL rate of $40\%$ . An analyst averages the two regional rates and reports $25\%$ as the company-wide NULL rate.

Which assessment correctly recalculates the company-wide row-level NULL rate?

The reported rate is correct because each region contributes one independently calculated percentage
The correct rate is about $12.7\%$ because the regional missing counts must be divided by all records (correct answer)
The correct rate is $15.0\%$ because the larger region's rate should receive twice the smaller region's weight
The correct rate is $30.0\%$ because the difference in regional rates must be added to the smaller rate

Explanation: 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}

Question 5

A transaction extract contains $1{,}000$ rows. COUNT(customer_id) returns $920$ , and COUNT(DISTINCT customer_id) returns $700$ . Multiple transactions may legitimately belong to the same customer. The requested metric is the percentage of transaction rows with a missing customer key.

Which conclusion is supported by these results?

The missing-key rate is $30\%$ because only $700$ unique customer keys occur in the extract
The missing-key rate is $20\%$ because the difference between total rows and distinct keys is missing
The missing-key rate is $8\%$ because $80$ transaction rows have customer_id IS NULL (correct answer)
The missing-key rate cannot be calculated because duplicate customer IDs make COUNT(customer_id) unusable

Explanation: 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.

Question 6

A column contains $500$ values: $30$ SQL NULLs, $20$ empty strings, $10$ strings containing only spaces, $5$ occurrences of 'N/A', and $435$ usable values. The data-quality rule defines all four exceptional forms as missing.

Which result and predicate correctly implement the business-defined missing-value rate?

$6\%$ using only value IS NULL, because SQL NULL is the only database-level missing value
$10\%$ using value IS NULL OR TRIM(value) = '', excluding the documented sentinel value
$12\%$ using NULLIF(TRIM(value), '') IS NULL, which also treats 'N/A' as empty
$13\%$ using value IS NULL OR TRIM(value) = '' OR TRIM(value) = 'N/A' (correct answer)

Explanation: 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.

Question 7

A profiling query reports that sales_leads contains $240$ rows and that COUNT(email) returns $198$ . Duplicate email addresses are allowed, and no rows are filtered.

Which SQL expression correctly calculates the NULL rate for email?

1.0 * (COUNT(*) - COUNT(email)) / COUNT(*), which returns $17.5\%$ (correct answer)
1.0 * COUNT(email) / COUNT(*), which returns $82.5\%$
1.0 * (COUNT(*) - COUNT(email)) / COUNT(email), which returns about $21.2\%$
1.0 * COUNT(DISTINCT email) / COUNT(*), which cannot be derived from the given counts

Explanation: 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.

Question 8

A staging table incoming_codes(code) must be checked against valid_codes(code). Both columns are nullable, and valid_codes currently contains at least one NULL. The required output is every row whose incoming code is non-NULL and has no equal non-NULL value in valid_codes.

Which query reliably identifies the missing reference keys despite the NULL in valid_codes?

SELECT s.* FROM incoming_codes s WHERE s.code IS NOT NULL AND s.code NOT IN (SELECT code FROM valid_codes);
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); (correct answer)
SELECT s.* FROM incoming_codes s WHERE s.code IN (SELECT code FROM valid_codes WHERE code IS NOT NULL);
SELECT s.* FROM incoming_codes s WHERE s.code IS NULL OR EXISTS (SELECT 1 FROM valid_codes v WHERE v.code = s.code);

Explanation: 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.

Question 9

An analyst intends to measure the NULL rate of status after excluding only rows whose status is 'cancelled'. The analyst runs: SELECT AVG(CASE WHEN status IS NULL THEN 1.0 ELSE 0.0 END) FROM jobs WHERE status <> 'cancelled'; The result is $0\%$ even though the source contains rows with status IS NULL.

Which modification preserves the intended denominator and correctly exposes the NULL statuses?

Keep the filter and replace the average with COUNT(status) * 1.0 / COUNT(*)
Change the filter to WHERE status <> 'cancelled' AND status IS NULL and keep the same average
Change the filter to WHERE status <> 'cancelled' OR status IS NULL and keep the same average (correct answer)
Change the filter to WHERE status = 'cancelled' OR status IS NOT NULL and invert the average

Explanation: 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.