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

Using COALESCE for NULL Handling in PostgreSQL

📖 Scenario: You are managing a small online store database. Some customers have not provided their phone numbers, so those entries are NULL. You want to create a query that shows each customer's name and phone number, but if the phone number is missing, it should show 'No Phone Provided' instead.

🎯 Goal: Build a SQL query using COALESCE to replace NULL phone numbers with the text 'No Phone Provided'.

📋 What You'll Learn

Create a table called customers with columns id (integer), name (text), and phone (text).

Insert exactly three customers with these details: (1, 'Alice', '123-456-7890'), (2, 'Bob', NULL), (3, 'Charlie', NULL).

Write a SELECT query that shows name and phone but replaces NULL phone values with 'No Phone Provided' using COALESCE.

Alias the replaced phone column as contact_phone.

💡 Why This Matters

🌍 Real World

Handling missing or NULL data is common in real databases. COALESCE helps show friendly default values instead of NULL.

💼 Career

Knowing how to manage NULL values with COALESCE is essential for database querying and reporting in many jobs.

1

Create the customers table and insert data

Write SQL statements to create a table called customers with columns id (integer), name (text), and phone (text). Then insert these three rows exactly: (1, 'Alice', '123-456-7890'), (2, 'Bob', NULL), and (3, 'Charlie', NULL).

PostgreSQL

-- Create the customers table and insert the three rows
-- Your code here

Need a hint?

Use CREATE TABLE to define the table and INSERT INTO to add the rows with exact values.

2

Set up the SELECT query base

Write a SELECT statement that selects the name and phone columns from the customers table. This will be the base query before adding COALESCE.

PostgreSQL

CREATE TABLE customers (
    id INTEGER,
    name TEXT,
    phone TEXT
);

INSERT INTO customers (id, name, phone) VALUES
(1, 'Alice', '123-456-7890'),
(2, 'Bob', NULL),
(3, 'Charlie', NULL);

-- Write SELECT name, phone FROM customers
-- Your code here

Need a hint?

Use SELECT name, phone FROM customers; to get the basic data.

3

Use COALESCE to handle NULL phone numbers

Modify the SELECT query to use COALESCE on the phone column. Replace phone with COALESCE(phone, 'No Phone Provided') to show 'No Phone Provided' when phone is NULL.

PostgreSQL

CREATE TABLE customers (
    id INTEGER,
    name TEXT,
    phone TEXT
);

INSERT INTO customers (id, name, phone) VALUES
(1, 'Alice', '123-456-7890'),
(2, 'Bob', NULL),
(3, 'Charlie', NULL);

-- Replace phone with COALESCE(phone, 'No Phone Provided')
-- Your code here

Need a hint?

Use COALESCE(phone, 'No Phone Provided') in the SELECT list.

4

Alias the replaced phone column as contact_phone

Add an alias to the COALESCE expression so the column name in the result is contact_phone. Use AS contact_phone after the COALESCE expression.

PostgreSQL

CREATE TABLE customers (
    id INTEGER,
    name TEXT,
    phone TEXT
);

INSERT INTO customers (id, name, phone) VALUES
(1, 'Alice', '123-456-7890'),
(2, 'Bob', NULL),
(3, 'Charlie', NULL);

-- Add AS contact_phone alias to COALESCE expression
-- Your code here

Need a hint?

Use AS contact_phone to rename the column.