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.

Practice

1. What does the SQL function COALESCE do when used in a query?

easy

A. Converts all values to NULL

B. Counts the number of NULL values in a column

C. Deletes rows with NULL values

D. Returns the first non-NULL value from the given list of expressions

Solution

1. Step 1: Understand the purpose of COALESCE

   COALESCE checks each value in order and returns the first one that is not NULL.

2. Step 2: Compare with other options

   Counting NULLs, deleting rows, or converting values to NULL are not what COALESCE does.

3. Final Answer:

   Returns the first non-NULL value from the given list of expressions -> Option D

4. Quick Check:

   COALESCE = first non-NULL value [OK]

Hint: COALESCE picks the first value that is not NULL [OK]

Common Mistakes:

• Thinking COALESCE counts NULLs
• Confusing COALESCE with DELETE
• Assuming COALESCE converts values to NULL

2. Which of the following is the correct syntax to use COALESCE in SQL?

easy

A. COALESCE(column1, column2, 'default')

B. COALESCE(column1; column2; 'default')

C. COALESCE(column1 column2 'default')

D. COALESCE[column1, column2, 'default']

Solution

1. Step 1: Recall COALESCE syntax

   COALESCE uses parentheses and commas to separate arguments: COALESCE(value1, value2, ...).

2. Step 2: Identify correct punctuation

   Only COALESCE(column1, column2, 'default') uses commas and parentheses correctly; others use semicolons, spaces, or brackets incorrectly.

3. Final Answer:

   COALESCE(column1, column2, 'default') -> Option A

4. Quick Check:

   Correct syntax uses commas inside parentheses [OK]

Hint: Use commas inside parentheses for COALESCE arguments [OK]

Common Mistakes:

• Using semicolons instead of commas
• Omitting commas between arguments
• Using square brackets instead of parentheses

3. Given the table Employees with columns FirstName, MiddleName, and LastName, what will this query return?

SELECT FirstName, COALESCE(MiddleName, 'N/A') AS Middle, LastName FROM Employees;

Assuming one row has FirstName='John', MiddleName=NULL, LastName='Doe'.

medium

A. John | N/A | Doe

B. John | NULL | Doe

C. John | '' | Doe

D. John | MiddleName | Doe

Solution

1. Step 1: Understand COALESCE behavior on NULL

   COALESCE returns the first non-NULL value. Since MiddleName is NULL, it returns 'N/A'.

2. Step 2: Apply to the given row

   FirstName is 'John', MiddleName replaced by 'N/A', LastName is 'Doe'.

3. Final Answer:

   John | N/A | Doe -> Option A

4. Quick Check:

   NULL replaced by 'N/A' using COALESCE [OK]

Hint: COALESCE replaces NULL with first non-NULL value [OK]

Common Mistakes:

• Expecting NULL instead of replacement
• Confusing empty string with NULL
• Thinking COALESCE returns column name

4. Identify the error in this SQL query:

SELECT COALESCE(FirstName MiddleName, 'Unknown') FROM Employees;

medium

A. COALESCE cannot have string literals

B. Missing comma between FirstName and MiddleName

C. COALESCE requires only one argument

D. Missing FROM clause

Solution

1. Step 1: Check COALESCE argument syntax

   Arguments must be separated by commas. Here, FirstName and MiddleName are missing a comma.

2. Step 2: Verify other parts

   String literals are allowed, multiple arguments allowed, and FROM clause is present.

3. Final Answer:

   Missing comma between FirstName and MiddleName -> Option B

4. Quick Check:

   Arguments need commas in COALESCE [OK]

Hint: Separate COALESCE arguments with commas [OK]

Common Mistakes:

• Forgetting commas between arguments
• Thinking COALESCE only takes one argument
• Ignoring syntax errors in argument list

5. You have a table Orders with columns OrderID, Discount, and SpecialDiscount. Some rows have NULL in both discount columns. You want to write a query that returns OrderID and the first available discount value, or 0 if both are NULL. Which query correctly uses COALESCE to achieve this?

hard

A. SELECT OrderID, COALESCE(Discount, 0) + COALESCE(SpecialDiscount, 0) AS FinalDiscount FROM Orders;

B. SELECT OrderID, COALESCE(Discount + SpecialDiscount, 0) AS FinalDiscount FROM Orders;

C. SELECT OrderID, COALESCE(Discount, SpecialDiscount, 0) AS FinalDiscount FROM Orders;

D. SELECT OrderID, COALESCE(Discount, SpecialDiscount) AS FinalDiscount FROM Orders;

Solution

1. Step 1: Understand the requirement

   Return the first non-NULL discount from Discount or SpecialDiscount, or 0 if both are NULL.

2. Step 2: Analyze each option

   SELECT OrderID, COALESCE(Discount, SpecialDiscount, 0) AS FinalDiscount FROM Orders; uses COALESCE with three arguments correctly. SELECT OrderID, COALESCE(Discount + SpecialDiscount, 0) AS FinalDiscount FROM Orders; sums discounts before COALESCE, which is wrong if both are NULL. SELECT OrderID, COALESCE(Discount, 0) + COALESCE(SpecialDiscount, 0) AS FinalDiscount FROM Orders; sums COALESCE results, which adds discounts instead of picking first non-NULL. SELECT OrderID, COALESCE(Discount, SpecialDiscount) AS FinalDiscount FROM Orders; misses the default 0.

3. Final Answer:

   SELECT OrderID, COALESCE(Discount, SpecialDiscount, 0) AS FinalDiscount FROM Orders; -> Option C

4. Quick Check:

   COALESCE with fallback 0 picks first non-NULL or 0 [OK]

Hint: Use COALESCE with all options and default last [OK]

Common Mistakes:

• Adding discounts instead of picking first non-NULL
• Forgetting default fallback value
• Using COALESCE incorrectly with expressions