COALESCE for NULL handling in SQL - Interactive Code Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the code to return the first non-null value between 'email' and 'phone'.

SQL

SELECT COALESCE([1], phone) AS contact FROM users;

Drag options to blanks, or click blank then click option'

Aphone

Bname

Caddress

Demail

Attempts:

3 left

💡 Hint

Common Mistakes

Using 'phone' as the first argument will change the order of preference.

Using a column not in the SELECT list.

The COALESCE function returns the first non-null value. Here, it checks 'email' first.

2fill in blank

medium

Complete the code to replace NULL 'salary' values with 0.

SQL

SELECT employee_id, COALESCE(salary, [1]) AS salary FROM employees;

Drag options to blanks, or click blank then click option'

A0

BNULL

C-1

D1000

Attempts:

3 left

💡 Hint

Common Mistakes

Using NULL again does not change the value.

Using a negative number might be confusing.

COALESCE replaces NULL with the value you provide. Here, 0 is used to indicate no salary.

3fill in blank

hard

Fix the error in the code to return the first non-null value among 'nickname', 'firstname', and 'lastname'.

SQL

SELECT COALESCE(nickname, [1], lastname) AS display_name FROM contacts;

Drag options to blanks, or click blank then click option'

Amiddlename

Bfirstname

Cnickname

Dlastname

Attempts:

3 left

💡 Hint

Common Mistakes

Repeating 'lastname' twice.

Using a column not in the table.

The COALESCE function checks values in order. 'firstname' should be the second argument to check after 'nickname'.

4fill in blank

hard

Fill both blanks to return the first non-null value among 'city', 'state', and 'country'.

SQL

SELECT COALESCE([1], [2], country) AS location FROM addresses;

Drag options to blanks, or click blank then click option'

Acity

Bzipcode

Cstate

Dregion

Attempts:

3 left

💡 Hint

Common Mistakes

Using 'zipcode' or 'region' which are not in the preferred order.

Swapping the order of 'city' and 'state'.

The COALESCE function checks 'city' first, then 'state', then 'country'.

5fill in blank

hard

Fill all three blanks to return the first non-null value among 'home_phone', 'mobile_phone', and 'work_phone'.

SQL

SELECT COALESCE([1], [2], [3]) AS primary_phone FROM contacts;

Drag options to blanks, or click blank then click option'

Amobile_phone

Bhome_phone

Cwork_phone

Dfax_number

Attempts:

3 left

💡 Hint

Common Mistakes

Including 'fax_number' which is not a phone contact.

Changing the order of phone numbers.

The COALESCE function checks 'home_phone' first, then 'mobile_phone', then 'work_phone'.

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