COALESCE for NULL handling in SQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

Output of COALESCE with multiple NULLs

Given the table Employees with columns id, name, and bonus where some bonus values are NULL, what is the output of this query?

SELECT id, COALESCE(bonus, 0) AS bonus_amount FROM Employees ORDER BY id;

SQL

CREATE TABLE Employees (id INT, name VARCHAR(20), bonus INT);
INSERT INTO Employees VALUES (1, 'Alice', NULL), (2, 'Bob', 100), (3, 'Charlie', NULL);

A [{"id":1,"bonus_amount":null},{"id":2,"bonus_amount":100},{"id":3,"bonus_amount":null}]

B [{"id":1,"bonus_amount":0},{"id":2,"bonus_amount":100},{"id":3,"bonus_amount":0}]

C [{"id":1,"bonus_amount":100},{"id":2,"bonus_amount":100},{"id":3,"bonus_amount":100}]

D [{"id":1,"bonus_amount":null},{"id":2,"bonus_amount":null},{"id":3,"bonus_amount":null}]

💡 Hint

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

The COALESCE function replaces NULL bonus values with 0, so rows with NULL bonus show 0 instead.

🧠 Conceptual

intermediate

Understanding COALESCE with multiple arguments

What will the following SQL expression return?

SELECT COALESCE(NULL, NULL, 'first non-null', 'second non-null');

A 'first non-null'

B NULL

C 'second non-null'

D Syntax error

💡 Hint

COALESCE returns the first argument that is not NULL.

COALESCE checks each argument in order and returns the first one that is not NULL, which is 'first non-null'.

📝 Syntax

advanced

Identify the syntax error in COALESCE usage

Which option contains a syntax error when using COALESCE in SQL?

A SELECT COALESCE(column1, column2, 0) FROM table1;

B SELECT COALESCE(NULL, 5) FROM table1;

C SELECT COALESCE(column1, , 0) FROM table1;

D SELECT COALESCE(column1, 'default') FROM table1;

💡 Hint

Look for missing arguments or commas in the COALESCE function.

Option C has two commas with no argument between them, causing a syntax error.

❓ query_result

advanced

COALESCE with different data types

Consider a table Orders with columns order_id, discount (INT), and coupon_code (VARCHAR). What is the output of this query?

SELECT order_id, COALESCE(CAST(discount AS VARCHAR), coupon_code, 'No discount') AS discount_info FROM Orders ORDER BY order_id;

Given data:

order_id | discount | coupon_code
1        | NULL     | 'SAVE10'
2        | 15       | NULL
3        | NULL     | NULL

A [{"order_id":1,"discount_info":"15"},{"order_id":2,"discount_info":"SAVE10"},{"order_id":3,"discount_info":"No discount"}]

B [{"order_id":1,"discount_info":"No discount"},{"order_id":2,"discount_info":"15"},{"order_id":3,"discount_info":"SAVE10"}]

C [{"order_id":1,"discount_info":null},{"order_id":2,"discount_info":"15"},{"order_id":3,"discount_info":"No discount"}]

D [{"order_id":1,"discount_info":"SAVE10"},{"order_id":2,"discount_info":"15"},{"order_id":3,"discount_info":"No discount"}]

💡 Hint

COALESCE returns the first non-NULL value, casting discount to string to match coupon_code type.

For order 1, discount is NULL, coupon_code is 'SAVE10', so returns 'SAVE10'. For order 2, discount is 15 (cast to '15'), so returns '15'. For order 3, both are NULL, so returns 'No discount'.

🔧 Debug

expert

Why does this COALESCE query return unexpected NULLs?

A developer wrote this query:

SELECT user_id, COALESCE(last_login, 'Never') AS last_login_date FROM Users;

But the output shows NULL for some last_login_date values even though last_login is NULL in those rows. What is the most likely cause?

A The column last_login is of DATE type, and 'Never' is a string causing type mismatch, so COALESCE returns NULL.

B The COALESCE function only works with numeric columns, so it fails here.

C The query is missing a WHERE clause to filter NULLs.

D The database does not support COALESCE with string literals.

💡 Hint

Think about data types and how COALESCE returns a value matching the first argument's type.

COALESCE returns a value with the type of the first argument. If last_login is DATE and 'Never' is a string, the database cannot convert 'Never' to DATE, so it returns NULL.