COALESCE and NULLIF as CASE shortcuts in SQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

Output of COALESCE with NULL values

Given the table Employees with columns id, name, and nickname, what is the output of this query?

SELECT id, COALESCE(nickname, name) AS display_name FROM Employees ORDER BY id;

Assume the data:

id | name    | nickname
1  | Alice   | NULL
2  | Bob     | Bobby
3  | Charlie | NULL

SQL

SELECT id, COALESCE(nickname, name) AS display_name FROM Employees ORDER BY id;

A [{"id":1,"display_name":null},{"id":2,"display_name":"Bobby"},{"id":3,"display_name":null}]

B [{"id":1,"display_name":"Alice"},{"id":2,"display_name":"Bobby"},{"id":3,"display_name":"Charlie"}]

C [{"id":1,"display_name":"nickname"},{"id":2,"display_name":"Bobby"},{"id":3,"display_name":"nickname"}]

D [{"id":1,"display_name":"Alice"},{"id":2,"display_name":"Bob"},{"id":3,"display_name":"Charlie"}]

💡 Hint

COALESCE returns the first non-NULL value from its arguments.

COALESCE(nickname, name) returns nickname if it is not NULL; otherwise, it returns name. For id=1 and 3, nickname is NULL, so name is used. For id=2, nickname is 'Bobby', so it is used.

❓ query_result

intermediate

Using NULLIF to avoid division by zero

Consider a table Sales with columns region, total_sales, and total_customers. What is the output of this query?

SELECT region, total_sales / NULLIF(total_customers, 0) AS avg_sale_per_customer FROM Sales ORDER BY region;

Given data:

region | total_sales | total_customers
East   | 1000        | 10
West   | 500         | 0
North  | 750         | 15

SQL

SELECT region, total_sales / NULLIF(total_customers, 0) AS avg_sale_per_customer FROM Sales ORDER BY region;

A [{"region":"East","avg_sale_per_customer":100},{"region":"North","avg_sale_per_customer":50},{"region":"West","avg_sale_per_customer":null}]

B [{"region":"East","avg_sale_per_customer":100},{"region":"North","avg_sale_per_customer":50},{"region":"West","avg_sale_per_customer":0}]

C [{"region":"East","avg_sale_per_customer":100},{"region":"North","avg_sale_per_customer":50},{"region":"West","avg_sale_per_customer":"Infinity"}]

D [{"region":"East","avg_sale_per_customer":100},{"region":"North","avg_sale_per_customer":50},{"region":"West","avg_sale_per_customer":500}]

💡 Hint

NULLIF returns NULL if the second argument matches the first, preventing division by zero.

NULLIF(total_customers, 0) returns NULL when total_customers is 0, so division by zero is avoided and result is NULL for West region.

📝 Syntax

advanced

Identify the syntax error in COALESCE usage

Which of the following SQL statements will cause a syntax error?

A SELECT COALESCE(NULL, NULL, 'fallback') AS result;

B SELECT COALESCE('value1', 'value2', NULL) AS result;

C SELECT COALESCE(NULL, 'default') AS result;

D SELECT COALESCE(NULL 'default') AS result;

💡 Hint

Check the commas separating arguments in COALESCE.

Option D misses a comma between NULL and 'default', causing a syntax error.

🧠 Conceptual

advanced

Understanding NULLIF behavior

What is the result of the expression NULLIF('abc', 'abc') in SQL?

A 'abc'

B Error

C NULL

D '' (empty string)

💡 Hint

NULLIF returns NULL if both arguments are equal.

NULLIF returns NULL when both arguments are equal, otherwise returns the first argument.

❓ optimization

expert

Optimizing CASE with COALESCE

You want to replace this CASE statement with a simpler expression:

SELECT CASE WHEN col1 IS NOT NULL THEN col1 ELSE col2 END AS result FROM table1;

Which of the following is the best equivalent SQL expression?

A SELECT COALESCE(col1, col2) AS result FROM table1;

B SELECT NULLIF(col1, col2) AS result FROM table1;

C SELECT CASE WHEN col1 = col2 THEN col1 ELSE col2 END AS result FROM table1;

D SELECT IFNULL(col2, col1) AS result FROM table1;

💡 Hint

COALESCE returns the first non-NULL value.

COALESCE(col1, col2) returns col1 if it is not NULL, else col2, matching the CASE logic.