SolveWithSQL

SQL Beginner Challenge 12: Handling NULL Values with COALESCE

Difficulty: Beginner
Estimated time: 10–15 minutes
SQL concepts: NULL, COALESCE, default values
Goal: Replace missing values with safe, readable defaults.

The Scenario

While reviewing a product report, a stakeholder points out an issue:

“Some fields are empty. Can we show something meaningful instead of blank values?”

In real-world databases, NULL values are common.
COALESCE helps you replace them with a default value directly in SQL.

Database Table

`products`

column_name	type	description
product_id	INTEGER	Unique product ID
name	TEXT	Product name
category	TEXT	Product category
price	DECIMAL	Product price
supplier_notes	TEXT	Optional notes from supplier (can be NULL)

Sample Data

product_id	name	category	price	supplier_notes
101	Wireless Mouse	Accessories	24.99	NULL
102	Mechanical Keyboard	Accessories	89.00	Best seller
103	27-inch Monitor	Displays	229.99	NULL
104	USB-C Hub	Accessories	34.50	Limited stock
105	Laptop Stand	Workspace	39.99	NULL

Your Task

Write a SQL query that returns:

Product name
supplier_notes

…but if supplier_notes is NULL, show the text:

“No notes available”

Expected Output

name	supplier_notes
Wireless Mouse	No notes available
Mechanical Keyboard	Best seller
27-inch Monitor	No notes available
USB-C Hub	Limited stock
Laptop Stand	No notes available

Constraints

Use the COALESCE function
Do not modify the underlying data
Do not use SELECT *

Hint (Optional)

COALESCE returns the first non-NULL value in the list:

COALESCE(column, fallback_value)

SELECT name, COALESCE(supplier_notes, 'No notes available') AS supplier_notes FROM products;

Explanation

If supplier_notes has a value, it is returned.
If it’s NULL, the fallback text is used instead.
This keeps reports readable and avoids empty fields.

Common Mistakes

Checking for NULL with = supplier_notes = NULL -- ❌ always false
Forgetting column aliases
Trying to update data instead of handling it in the query

Optional Extension (Mini Bonus)

Try:

Replace missing prices with 0.
Use COALESCE with numeric columns.
Combine COALESCE with CASE for more complex defaults.

Next Challenge

Beginner Challenge #13: Filtering NULL Values with IS NULL and IS NOT NULL

🔗 View reference solution on GitHub
(After you’ve tried the challenge)

Want more practical SQL challenges?
Subscribe to the Solve With SQL newsletter and get new problems delivered to your inbox.