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.

Practice

1. What does the COALESCE function do in PostgreSQL?

easy

A. Returns the first non-NULL value from the list of arguments

B. Returns the last value in the list regardless of NULL

C. Counts the number of NULL values in a column

D. Deletes rows with NULL values

Solution

1. Step 1: Understand COALESCE purpose

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

2. Step 2: Compare options with definition

   Only Returns the first non-NULL value from the list of arguments correctly describes this behavior; others describe unrelated functions.

3. Final Answer:

   Returns the first non-NULL value from the list of arguments -> Option A

4. Quick Check:

   COALESCE = first non-NULL value [OK]

Hint: COALESCE picks first non-NULL value in order [OK]

Common Mistakes:

• Thinking COALESCE counts NULLs
• Assuming it returns last value
• Confusing with DELETE or COUNT functions

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

easy

A. COALESCE(value1; value2; ...; valueN)

B. COALESCE[value1, value2, ..., valueN]

C. COALESCE(value1, value2, ..., valueN)

D. COALESCE{value1, value2, ..., valueN}

Solution

1. Step 1: Recall function call syntax in SQL

   Functions use parentheses with comma-separated arguments.

2. Step 2: Match options to syntax rules

   Only COALESCE(value1, value2, ..., valueN) uses parentheses and commas correctly; others use invalid separators or brackets.

3. Final Answer:

   COALESCE(value1, value2, ..., valueN) -> Option C

4. Quick Check:

   Function syntax = parentheses + commas [OK]

Hint: Use parentheses and commas for SQL functions [OK]

Common Mistakes:

• Using semicolons instead of commas
• Using square or curly brackets
• Omitting parentheses

3. Given the table users with columns id, nickname, and fullname, what will this query return?

SELECT id, COALESCE(nickname, fullname, 'Anonymous') AS display_name FROM users;

Assuming a row has nickname = NULL and fullname = 'John Doe'.

medium

A. NULL

B. 'John Doe'

C. 'Anonymous'

D. nickname value

Solution

1. Step 1: Evaluate COALESCE arguments in order

   First argument is nickname which is NULL, so move to next.

2. Step 2: Check fullname value

   fullname is 'John Doe', which is not NULL, so COALESCE returns 'John Doe'.

3. Final Answer:

   'John Doe' -> Option B

4. Quick Check:

   First non-NULL = 'John Doe' [OK]

Hint: COALESCE returns first non-NULL value in order [OK]

Common Mistakes:

• Assuming NULL is returned if first value is NULL
• Ignoring order of arguments
• Choosing default 'Anonymous' incorrectly

4. Identify the error in this query:

SELECT id, COALESCE nickname, fullname, 'Guest' AS display_name FROM users;

medium

A. AS keyword misplaced

B. Wrong keyword instead of COALESCE

C. No error, query is correct

D. Missing parentheses around COALESCE arguments

Solution

1. Step 1: Check COALESCE function syntax

   COALESCE requires parentheses around its arguments.

2. Step 2: Analyze given query

   Query uses COALESCE without parentheses, causing syntax error.

3. Final Answer:

   Missing parentheses around COALESCE arguments -> Option D

4. Quick Check:

   Functions need parentheses [OK]

Hint: Always use parentheses with COALESCE arguments [OK]

Common Mistakes:

• Omitting parentheses for functions
• Misplacing AS keyword
• Confusing COALESCE with other functions

5. You want to display a user's preferred contact method from columns email, phone, and address. If all are NULL, show 'No contact info'. Which query correctly uses COALESCE to achieve this?

hard

A. SELECT id, COALESCE(email, phone, address, 'No contact info') AS contact FROM users;

B. SELECT id, COALESCE(email, phone, address) OR 'No contact info' AS contact FROM users;

C. SELECT id, COALESCE(email, phone, address) ?? 'No contact info' AS contact FROM users;

D. SELECT id, COALESCE(email, phone, address, NULL) AS contact FROM users;

Solution

1. Step 1: Understand COALESCE usage for defaults

   COALESCE returns first non-NULL; adding 'No contact info' as last argument provides default.

2. Step 2: Evaluate each option's correctness

   SELECT id, COALESCE(email, phone, address, 'No contact info') AS contact FROM users; correctly lists all contact columns plus default string. Options B and C use invalid operators, D ends with NULL, no default.

3. Final Answer:

   SELECT id, COALESCE(email, phone, address, 'No contact info') AS contact FROM users; -> Option A

4. Quick Check:

   Default last in COALESCE list [OK]

Hint: Put default last inside COALESCE parentheses [OK]

Common Mistakes:

• Using OR or ?? instead of COALESCE
• Not providing a default value
• Ending COALESCE with NULL instead of default