COUNT function behavior in SQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

COUNT with NULL values

Given the table Employees with a column ManagerID that can contain NULL values, what will be the result of this query?

SELECT COUNT(ManagerID) FROM Employees;

SQL

SELECT COUNT(ManagerID) FROM Employees;

A Returns an error because COUNT cannot be used on nullable columns

B Counts all rows including those with NULL ManagerID

C Returns the total number of NULL ManagerID values

D Counts only rows where ManagerID is NOT NULL

💡 Hint

Remember that COUNT(column) ignores NULL values in that column.

COUNT(column) counts only rows where the column is NOT NULL. NULL values are skipped.

❓ query_result

intermediate

COUNT(*) vs COUNT(column)

Consider a table Orders with 100 rows, where the column ShippedDate has 20 NULL values. What will be the result of these two queries?

1) SELECT COUNT(*) FROM Orders;
2) SELECT COUNT(ShippedDate) FROM Orders;

SQL

SELECT COUNT(*), COUNT(ShippedDate) FROM Orders;

A 1) 100, 2) 100

B 1) 80, 2) 100

C 1) 100, 2) 80

D 1) 80, 2) 80

💡 Hint

COUNT(*) counts all rows, COUNT(column) counts non-NULL values in that column.

COUNT(*) counts all rows regardless of NULLs. COUNT(ShippedDate) counts only non-NULL ShippedDate values.

🧠 Conceptual

advanced

COUNT with DISTINCT

What does the query below return?

SELECT COUNT(DISTINCT CustomerID) FROM Sales;

SQL

SELECT COUNT(DISTINCT CustomerID) FROM Sales;

A The number of unique CustomerID values, excluding NULLs

B The total number of rows in Sales including duplicates

C The number of rows where CustomerID is NULL

D The total number of unique rows in Sales

💡 Hint

DISTINCT counts unique values, COUNT ignores NULLs.

COUNT(DISTINCT column) counts unique non-NULL values in that column.

📝 Syntax

advanced

Invalid use of COUNT function

Which of the following queries will cause a syntax error?

A SELECT COUNT(DISTINCT CategoryID) FROM Products;

B SELECT COUNT() FROM Products;

C SELECT COUNT(ProductID) FROM Products;

D SELECT COUNT(*) FROM Products;

💡 Hint

COUNT requires an argument inside the parentheses.

COUNT() with empty parentheses is invalid syntax and causes an error.

❓ optimization

expert

Optimizing COUNT queries on large tables

You have a very large table Logs with millions of rows. You want to count how many rows have Status = 'Error'. Which query is generally the most efficient?

A SELECT COUNT(*) FROM Logs WHERE Status = 'Error';

B SELECT COUNT(Status) FROM Logs WHERE Status = 'Error';

C SELECT COUNT(1) FROM Logs WHERE Status = 'Error';

D SELECT COUNT(DISTINCT Status) FROM Logs WHERE Status = 'Error';

💡 Hint

COUNT(*) counts rows and is optimized by most databases.

COUNT(*) with a WHERE clause is optimized to count rows matching the condition efficiently.