COALESCE for NULL handling in PostgreSQL - Interactive Code Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the code to select the first non-null value between column1 and 'default'.

PostgreSQL

SELECT COALESCE(column1, [1]) FROM table1;

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

Acolumn2

BNULL

C'default'

D0

Attempts:

3 left

💡 Hint

Common Mistakes

Using NULL as the second argument returns NULL if column1 is NULL.

Using a column name instead of a default value may not handle NULL properly.

The COALESCE function returns the first non-null value. Here, if column1 is null, it returns the string 'default'.

2fill in blank

medium

Complete the code to replace NULL in salary with 0.

PostgreSQL

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

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

A0

B'0'

Csalary

DNULL

Attempts:

3 left

💡 Hint

Common Mistakes

Using '0' (a string) instead of 0 (a number) for numeric columns.

Using NULL as the replacement does not change NULL values.

Using 0 replaces NULL salaries with zero as a number, which is appropriate for numeric columns.

3fill in blank

hard

Fix the error in the code to return the first non-null value between address and 'Unknown'.

PostgreSQL

SELECT COALESCE([1], 'Unknown') FROM customers;

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

Aaddress

B'address'

CNULL

Dcustomer_id

Attempts:

3 left

💡 Hint

Common Mistakes

Putting the column name in single quotes makes it a string literal.

Using NULL as the first argument returns NULL always.

The column name address should not be in quotes. Quotes make it a string literal, not a column reference.

4fill in blank

hard

Fill both blanks to select the first non-null value between phone and email, or 'N/A' if both are null.

PostgreSQL

SELECT COALESCE([1], [2], 'N/A') FROM contacts;

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

Aphone

Bemail

C'phone'

D'email'

Attempts:

3 left

💡 Hint

Common Mistakes

Using quoted strings instead of column names.

Reversing the order of columns changes priority.

Use column names phone and email without quotes to check their values. The COALESCE returns the first non-null among them or 'N/A'.

5fill in blank

hard

Fill all three blanks to select the first non-null value among city, state, and 'Unknown'.

PostgreSQL

SELECT COALESCE([1], [2], [3]) FROM locations;

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

Acity

Bstate

C'Unknown'

D'city'

Attempts:

3 left

💡 Hint

Common Mistakes

Using quoted strings for column names.

Putting the string literal in the wrong position.

The COALESCE function checks city and state columns first, then returns the string 'Unknown' if both are null.