NULL behavior in aggregate functions in SQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

Output of COUNT with NULL values

Consider a table Employees with a column Bonus that contains some NULL values. What is the output of the following query?

SELECT COUNT(Bonus) FROM Employees;

SQL

CREATE TABLE Employees (ID INT, Bonus INT);
INSERT INTO Employees VALUES (1, 100), (2, NULL), (3, 200), (4, NULL);

A 2

B 4

C 0

D NULL

💡 Hint

COUNT(column) counts only non-NULL values.

COUNT(column) counts only rows where the column is NOT NULL. Here, Bonus has 2 non-NULL values (100 and 200).

❓ query_result

intermediate

SUM ignoring NULL values

Given a table Sales with a column Amount containing some NULLs, what is the output of this query?

SELECT SUM(Amount) FROM Sales;

SQL

CREATE TABLE Sales (ID INT, Amount INT);
INSERT INTO Sales VALUES (1, 50), (2, NULL), (3, 150), (4, NULL);

A NULL

B 200

C 0

D 50

💡 Hint

SUM ignores NULL values and sums only non-NULL entries.

SUM adds only non-NULL values: 50 + 150 = 200.

🧠 Conceptual

advanced

Behavior of AVG with NULL values

Which statement correctly describes how the AVG aggregate function treats NULL values in a column?

A AVG includes NULL values as zero in the calculation.

B AVG returns NULL if any NULL values exist in the column.

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

D AVG counts NULL values as one in the denominator.

💡 Hint

Think about how average is calculated when some values are missing.

AVG ignores NULLs and calculates the average only over non-NULL values.

❓ query_result

advanced

MIN function with all NULL values

What is the output of this query when all values in the Score column are NULL?

SELECT MIN(Score) FROM Results;

SQL

CREATE TABLE Results (ID INT, Score INT);
INSERT INTO Results VALUES (1, NULL), (2, NULL), (3, NULL);

A Minimum integer value

B 0

C Error

D NULL

💡 Hint

MIN returns NULL if no non-NULL values exist.

MIN returns NULL when all values are NULL because there is no minimum value to find.

❓ query_result

expert

Aggregate functions and NULL in WHERE clause

Consider a table Orders with a column Discount that contains some NULL values. What is the output of the following query?

SELECT AVG(Discount) FROM Orders WHERE Discount > 10;

SQL

CREATE TABLE Orders (ID INT, Discount INT);
INSERT INTO Orders VALUES (1, 5), (2, NULL), (3, 20), (4, NULL), (5, 10), (6, 30);

A 25

B NULL

C Error

D 0

💡 Hint

NULL > 10 evaluates to UNKNOWN (not true), so rows with NULL are excluded. AVG is then computed on qualifying non-NULL values.

WHERE Discount > 10 selects rows with 20 and 30. AVG(20, 30) = 25. NULL values do not cause an error; they are simply filtered out.