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).