COALESCE for NULL handling in SQL - Mini Project: Build & Apply

Using COALESCE for NULL Handling in SQL

📖 Scenario: You work for a small bookstore that keeps track of book sales. Some books have missing sales data (NULL values) because they are new or out of stock. You want to create a report that shows the sales numbers, but replaces any missing sales with zero to make the report clearer.

🎯 Goal: Build an SQL query that uses the COALESCE function to replace NULL sales values with zero in the sales report.

📋 What You'll Learn

Create a table called books with columns id, title, and sales.

Insert the exact data rows with some sales values as NULL.

Write a query that selects title and uses COALESCE to show sales with NULL replaced by 0.

Alias the replaced sales column as sales_report.

💡 Why This Matters

🌍 Real World

Handling missing data is common in real databases. Using COALESCE helps create clean reports without confusing NULL values.

💼 Career

Database developers and analysts often use COALESCE to ensure queries return meaningful data for reports and dashboards.

1

Create the books table and insert data

Create a table called books with columns id (integer), title (text), and sales (integer). Then insert these exact rows: (1, 'Learn SQL', 150), (2, 'Advanced SQL', NULL), (3, 'SQL for Beginners', 200).

SQL

-- Create the books table and insert data
-- Your code here

Hint

Use CREATE TABLE to define the table and INSERT INTO to add the rows exactly as given.

2

Set up the basic SELECT query

Write a SELECT query that retrieves the title and sales columns from the books table.

SQL

CREATE TABLE books (
  id INTEGER,
  title TEXT,
  sales INTEGER
);

INSERT INTO books (id, title, sales) VALUES
  (1, 'Learn SQL', 150),
  (2, 'Advanced SQL', NULL),
  (3, 'SQL for Beginners', 200);

-- Write SELECT query to get title and sales
-- Your code here

Hint

Use SELECT title, sales FROM books; to get the data.

3

Use COALESCE to replace NULL sales with zero

Modify the SELECT query to use COALESCE on the sales column to replace NULL values with 0. Alias this column as sales_report.

SQL

CREATE TABLE books (
  id INTEGER,
  title TEXT,
  sales INTEGER
);

INSERT INTO books (id, title, sales) VALUES
  (1, 'Learn SQL', 150),
  (2, 'Advanced SQL', NULL),
  (3, 'SQL for Beginners', 200);

-- Use COALESCE on sales and alias as sales_report
-- Your code here

Hint

Use COALESCE(sales, 0) to replace NULL with zero and alias it as sales_report.

4

Complete the sales report query

Add an ORDER BY clause to the query to sort the results by sales_report in descending order.

SQL

CREATE TABLE books (
  id INTEGER,
  title TEXT,
  sales INTEGER
);

INSERT INTO books (id, title, sales) VALUES
  (1, 'Learn SQL', 150),
  (2, 'Advanced SQL', NULL),
  (3, 'SQL for Beginners', 200);

-- Add ORDER BY sales_report DESC
-- Your code here

Hint

Use ORDER BY sales_report DESC to sort the results from highest to lowest sales.

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