NULL behavior in aggregate functions in SQL - Interactive Code Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the code to count only non-NULL values in the column.

SQL

SELECT COUNT([1]) FROM employees;

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

ADISTINCT salary

Bsalary

CNULL

D*

Attempts:

3 left

💡 Hint

Common Mistakes

Using COUNT(*) which counts all rows regardless of NULLs.

Using COUNT(NULL) which always returns 0.

The COUNT function counts only non-NULL values when a column name is specified. Using COUNT(salary) counts all non-NULL salaries.

2fill in blank

medium

Complete the code to calculate the average salary ignoring NULL values.

SQL

SELECT AVG([1]) FROM employees;

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

Asalary

B*

CNULL

DDISTINCT salary

Attempts:

3 left

💡 Hint

Common Mistakes

Using AVG(*) which is not valid.

Using AVG(NULL) which returns NULL.

AVG ignores NULL values and calculates the average of non-NULL salaries.

3fill in blank

hard

Fix the error in the query to count all rows including those with NULL salaries.

SQL

SELECT COUNT([1]) FROM employees;

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

ANULL

Bsalary

C*

DDISTINCT salary

Attempts:

3 left

💡 Hint

Common Mistakes

Using COUNT(salary) which skips NULL salaries.

Using COUNT(NULL) which returns 0.

COUNT(*) counts all rows, including those where salary is NULL.

4fill in blank

hard

Fill both blanks to select the maximum salary and minimum salary ignoring NULLs.

SQL

SELECT MAX([1]), MIN([2]) FROM employees;

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

Asalary

B*

CNULL

DDISTINCT salary

Attempts:

3 left

💡 Hint

Common Mistakes

Using MAX(*) or MIN(*) which is invalid syntax.

Using MAX(NULL) or MIN(NULL) which returns NULL.

MAX and MIN ignore NULL values and operate on the specified column.

5fill in blank

hard

Fill all three blanks to select the sum of salaries, count of non-NULL salaries, and average salary.

SQL

SELECT SUM([1]), COUNT([2]), AVG([3]) FROM employees;

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

A*

Bsalary

CNULL

DDISTINCT salary

Attempts:

3 left

💡 Hint

Common Mistakes

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

Using NULL inside aggregate functions which returns NULL.

SUM, COUNT(column), and AVG all operate on the salary column ignoring NULLs (except COUNT(*) counts all rows).

Practice

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

easy

A. COUNT(*)

B. COUNT(column_name)

C. SUM(column_name)

D. AVG(column_name)

Solution

1. Step 1: Understand COUNT(*) behavior

   COUNT(*) counts every row in the table regardless of NULLs.

2. Step 2: Compare with other aggregate functions

   COUNT(column_name) counts only non-NULL values; SUM and AVG ignore NULLs but do not count rows.

3. Final Answer:

   COUNT(*) -> Option A

4. Quick Check:

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

Hint: COUNT(*) counts all rows, NULL or not [OK]

Common Mistakes:

• Thinking COUNT(column) counts NULLs
• Assuming SUM counts NULLs as zero
• Confusing AVG with COUNT(*)

2. Which of the following is the correct syntax to count only non-NULL values in the column salary?

easy

A. SELECT COUNT(salary) FROM employees;

B. SELECT COUNT(*) FROM employees;

C. SELECT COUNT(salary) FROM employees WHERE salary IS NULL;

D. SELECT COUNT(*) FROM employees WHERE salary IS NULL;

Solution

1. Step 1: Understand COUNT(column) syntax

   COUNT(column) counts only non-NULL values in that column automatically.

2. Step 2: Analyze other options

   SELECT COUNT(*) FROM employees; counts all rows including those with NULL salary; SELECT COUNT(salary) FROM employees WHERE salary IS NULL; counts non-NULL salaries where salary is NULL (returns 0); SELECT COUNT(*) FROM employees WHERE salary IS NULL; counts rows with NULL salary.

3. Final Answer:

   SELECT COUNT(salary) FROM employees; -> Option A

4. Quick Check:

   COUNT(column) counts non-NULL values [OK]

Hint: Use COUNT(column) to count non-NULL values directly [OK]

Common Mistakes:

• Using WHERE clause unnecessarily with COUNT(column)
• Confusing COUNT(*) with COUNT(column)
• Counting NULL values by mistake

3. Given the table sales with column amount containing values (100, NULL, 200, NULL), what is the result of SELECT SUM(amount) FROM sales;?

medium

A. Syntax Error

B. NULL

C. 0

D. 300

Solution

1. Step 1: Identify values in amount column

   Values are 100, NULL, 200, NULL.

2. Step 2: Understand SUM behavior with NULLs

   SUM ignores NULLs and adds only 100 + 200 = 300.

3. Final Answer:

   300 -> Option D

4. Quick Check:

   SUM ignores NULLs and sums non-NULLs [OK]

Hint: SUM skips NULLs, adds only real numbers [OK]

Common Mistakes:

• Assuming SUM returns NULL if any NULL present
• Confusing SUM with COUNT
• Expecting 0 instead of sum of non-NULLs

4. Consider this query: SELECT AVG(score) FROM tests; where all score values are NULL. What is the issue and how to fix it?

medium

A. AVG returns 0; fix by casting scores to integers

B. AVG returns NULL; fix by filtering NULLs with WHERE score IS NOT NULL

C. Syntax error; fix by adding GROUP BY

D. AVG returns NULL; fix by using COUNT(score) instead

Solution

1. Step 1: Understand AVG behavior with all NULLs

   AVG returns NULL if no non-NULL values exist.

2. Step 2: Fix by filtering NULLs

   Use WHERE score IS NOT NULL to exclude NULLs before averaging.

3. Final Answer:

   AVG returns NULL; fix by filtering NULLs with WHERE score IS NOT NULL -> Option B

4. Quick Check:

   AVG returns NULL if no non-NULL values [OK]

Hint: Filter NULLs before AVG to avoid NULL result [OK]

Common Mistakes:

• Expecting AVG to return 0 with all NULLs
• Trying to fix with GROUP BY unnecessarily
• Using COUNT instead of AVG to get average

5. You want to find the average salary of employees but return 0 if all salaries are NULL. Which query achieves this?

hard

A. SELECT SUM(salary)/COUNT(*) FROM employees;

B. SELECT AVG(COALESCE(salary, 0)) FROM employees;

C. SELECT COALESCE(AVG(salary), 0) FROM employees;

D. SELECT COUNT(salary) FROM employees WHERE salary IS NOT NULL;

Solution

1. Step 1: Understand AVG with all NULL salaries

   AVG returns NULL if no non-NULL salaries exist.

2. Step 2: Use COALESCE to replace NULL with 0

   COALESCE(AVG(salary), 0) returns 0 instead of NULL when AVG is NULL.

3. Step 3: Analyze other options

   SELECT AVG(COALESCE(salary, 0)) FROM employees; averages zeros which skews result; SELECT SUM(salary)/COUNT(*) FROM employees; divides by total rows including NULLs; SELECT COUNT(salary) FROM employees WHERE salary IS NOT NULL; counts non-NULL salaries only.

4. Final Answer:

   SELECT COALESCE(AVG(salary), 0) FROM employees; -> Option C

5. Quick Check:

   Use COALESCE to handle NULL AVG results [OK]

Hint: Wrap AVG with COALESCE to replace NULL with zero [OK]

Common Mistakes:

• Replacing NULL salaries with zero before AVG skews average
• Dividing SUM by total rows including NULLs
• Using COUNT instead of AVG for average