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

Need a 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

Need a 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

Need a 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

Need a hint?

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