Aggregate with NULL handling in SQL - Interactive Code Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the code to count all non-NULL values in the column 'score'.

SQL

SELECT COUNT([1]) FROM results;

Drag options to blanks, or click blank then click option'

Ascore

B*

CDISTINCT score

DNULL

Attempts:

3 left

💡 Hint

Common Mistakes

Using COUNT(*) counts all rows, not just non-NULL values.

Using COUNT(NULL) returns zero always.

The COUNT function counts only non-NULL values when a column name is specified. Using 'score' counts all non-NULL scores.

2fill in blank

medium

Complete the code to calculate the average of 'score', ignoring NULL values.

SQL

SELECT AVG([1]) FROM results;

Drag options to blanks, or click blank then click option'

ADISTINCT score

B*

Cscore

DNULL

Attempts:

3 left

💡 Hint

Common Mistakes

Using AVG(*) causes syntax error.

Using AVG(NULL) returns NULL.

AVG(column) calculates the average ignoring NULLs. Using 'score' computes average of non-NULL scores.

3fill in blank

hard

Fix the error in the code to count all rows including those with NULL in 'score'.

SQL

SELECT COUNT([1]) FROM results;

Drag options to blanks, or click blank then click option'

Ascore

BNULL

CDISTINCT score

D*

Attempts:

3 left

💡 Hint

Common Mistakes

Using COUNT(score) counts only non-NULL scores, missing rows with NULL.

Using COUNT(NULL) returns zero.

COUNT(*) counts all rows regardless of NULLs in any column.

4fill in blank

hard

Fill both blanks to calculate the total sum of 'score' treating NULL as zero.

SQL

SELECT SUM(COALESCE([1], [2])) FROM results;

Drag options to blanks, or click blank then click option'

Ascore

B0

CNULL

D1

Attempts:

3 left

💡 Hint

Common Mistakes

Using COALESCE(score, NULL) does not replace NULLs.

Replacing NULL with 1 changes the sum incorrectly.

COALESCE replaces NULL with 0 so SUM adds zeros instead of ignoring NULLs.

5fill in blank

hard

Fill all three blanks to count distinct non-NULL 'score' values greater than 50.

SQL

SELECT COUNT(DISTINCT [1]) FROM results WHERE [2] > [3];

Drag options to blanks, or click blank then click option'

Ascore

C50

DNULL

Attempts:

3 left

💡 Hint

Common Mistakes

Using NULL in WHERE clause causes no rows to match.

Counting without DISTINCT counts duplicates.

COUNT(DISTINCT score) counts unique non-NULL scores. The WHERE clause filters scores greater than 50.

Practice

1. Which aggregate function counts all rows including those with NULL values in any column?

easy

A. COUNT(column_name)

B. COUNT(*)

C. SUM(column_name)

D. AVG(column_name)

Solution

1. Step 1: Understand COUNT(*) behavior

   COUNT(*) counts every row in the table regardless of NULL values in any column.

2. Step 2: Compare with COUNT(column_name)

   COUNT(column_name) counts only rows where the specified column is NOT NULL.

3. Final Answer:

   COUNT(*) -> Option B

4. Quick Check:

   COUNT(*) counts all rows including NULLs [OK]

Hint: Use COUNT(*) to count all rows including NULLs [OK]

Common Mistakes:

• Thinking COUNT(column) counts all rows
• Confusing SUM with COUNT
• Assuming AVG counts NULLs

2. Which SQL expression correctly replaces NULL values with zero before summing a column sales?

easy

A. SUM(NULLIF(sales, 0))

B. SUM(sales)

C. SUM(COALESCE(sales, 0))

D. SUM(ISNULL(sales))

Solution

1. Step 1: Understand COALESCE usage

   COALESCE(sales, 0) replaces NULL values in sales with 0 before summing.

2. Step 2: Check other options

   SUM(sales) ignores NULLs, NULLIF returns NULL if sales=0, ISNULL(sales) is incomplete syntax.

3. Final Answer:

   SUM(COALESCE(sales, 0)) -> Option C

4. Quick Check:

   Use COALESCE to replace NULLs before aggregation [OK]

Hint: Use COALESCE(column, 0) to treat NULL as zero in sums [OK]

Common Mistakes:

• Using SUM(sales) and expecting NULLs counted as zero
• Confusing NULLIF with COALESCE
• Using ISNULL without second argument

3. Given the table orders with column discount containing values (10, NULL, 10, NULL, 15), what is the result of this query?

SELECT AVG(COALESCE(discount, 0)) FROM orders;

medium

A. 7

B. 10

C. 15

D. NULL

Solution

1. Step 1: Replace NULLs with 0 using COALESCE

   Values become 10, 0, 10, 0, 15.

2. Step 2: Calculate average of these values

   Sum = 10 + 0 + 10 + 0 + 15 = 35; Count = 5; Average = 35 / 5 = 7.

3. Final Answer:

   7 -> Option A

4. Quick Check:

   COALESCE replaces NULLs, AVG includes zeros [OK]

Hint: Replace NULLs with zero before AVG to include them [OK]

Common Mistakes:

• Ignoring NULLs and averaging only non-NULL values
• Assuming AVG ignores zeros
• Miscounting number of rows

4. Identify the error in this query that tries to count all rows including NULLs in score column:

SELECT COUNT(score) + COUNT(NULL) FROM results;

medium

A. The query sums counts correctly

B. COUNT(score) counts all rows including NULLs

C. COUNT(NULL) counts NULLs as 1

D. COUNT(NULL) returns 0

Solution

1. Step 1: Understand COUNT(NULL) behavior

   COUNT(NULL) always returns 0 because the NULL expression is always NULL and thus never counted.

2. Step 2: Analyze COUNT(score)

   COUNT(score) counts only non-NULL values in score column, not all rows.

3. Final Answer:

   COUNT(NULL) returns 0 -> Option D

4. Quick Check:

   COUNT(NULL) always returns 0 [OK]

Hint: COUNT(NULL) always returns zero, use COUNT(*) for all rows [OK]

Common Mistakes:

• Thinking COUNT(NULL) counts NULLs
• Assuming COUNT(column) counts NULLs
• Adding COUNT(NULL) to count rows

5. You have a table employees with a nullable bonus column. You want to calculate the total bonus, treating NULL as zero, but only for employees with a salary above 50000. Which query correctly does this?

hard

A. SELECT SUM(COALESCE(bonus, 0)) FROM employees WHERE salary > 50000;

B. SELECT SUM(bonus) FROM employees WHERE COALESCE(salary, 0) > 50000;

C. SELECT SUM(COALESCE(bonus, 0)) WHERE salary > 50000 FROM employees;

D. SELECT SUM(bonus) FROM employees WHERE salary > 50000;

Solution

1. Step 1: Use COALESCE to treat NULL bonus as zero

   SUM(COALESCE(bonus, 0)) replaces NULL bonuses with 0 before summing.

2. Step 2: Filter employees with salary > 50000

   The WHERE clause correctly filters rows before aggregation.

3. Step 3: Check query syntax

   SELECT SUM(COALESCE(bonus, 0)) FROM employees WHERE salary > 50000; has correct syntax.

4. Final Answer:

   SELECT SUM(COALESCE(bonus, 0)) FROM employees WHERE salary > 50000; -> Option A

5. Quick Check:

   Use COALESCE in SUM and filter with WHERE [OK]

Hint: Use COALESCE in SUM and filter rows with WHERE [OK]

Common Mistakes:

• Placing WHERE clause after FROM incorrectly
• Not using COALESCE to handle NULLs
• Filtering on COALESCE(salary, 0) unnecessarily