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'.