COALESCE and NULLIF as CASE shortcuts in SQL - Mini Project: Build & Apply

Using COALESCE and NULLIF as CASE Shortcuts

📖 Scenario: You are managing a small customer database for a local store. Some customers have missing phone numbers or prefer to use a secondary contact number. You want to write queries that handle these cases gracefully.

🎯 Goal: Build SQL queries that use COALESCE and NULLIF to simplify CASE expressions for handling missing or default values in customer contact information.

📋 What You'll Learn

Create a table called customers with columns id, name, phone_primary, and phone_secondary.

Insert specific customer data with some NULL and empty string values in phone columns.

Write a query using COALESCE to select the first available phone number for each customer.

Write a query using NULLIF to treat empty strings as NULL in phone numbers.

💡 Why This Matters

🌍 Real World

Handling missing or default values in databases is common in real-world applications like customer management systems.

💼 Career

Knowing how to use COALESCE and NULLIF helps database developers and analysts write cleaner, more efficient queries.

1

Create the customers table and insert data

Create a table called customers with columns id (integer), name (text), phone_primary (text), and phone_secondary (text). Then insert these exact rows: (1, 'Alice', '123-4567', NULL), (2, 'Bob', NULL, '987-6543'), (3, 'Charlie', '', '555-0000'), (4, 'Diana', NULL, NULL).

SQL

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

Need a hint?

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

2

Add a query to select the first available phone number using COALESCE

Write a SQL query that selects id, name, and a new column contact_phone which uses COALESCE to return the first non-NULL value between phone_primary and phone_secondary from the customers table.

SQL

CREATE TABLE customers (
  id INTEGER,
  name TEXT,
  phone_primary TEXT,
  phone_secondary TEXT
);

INSERT INTO customers (id, name, phone_primary, phone_secondary) VALUES
  (1, 'Alice', '123-4567', NULL),
  (2, 'Bob', NULL, '987-6543'),
  (3, 'Charlie', '', '555-0000'),
  (4, 'Diana', NULL, NULL);

-- Write a SELECT query using COALESCE to get contact_phone
-- Your code here

Need a hint?

Use COALESCE(phone_primary, phone_secondary) to get the first phone number that is not NULL.

3

Use NULLIF to treat empty strings as NULL in phone_primary

Modify the previous query to use NULLIF on phone_primary so that empty strings ('') are treated as NULL. Then use COALESCE to select the first non-NULL phone number between the modified phone_primary and phone_secondary.

SQL

CREATE TABLE customers (
  id INTEGER,
  name TEXT,
  phone_primary TEXT,
  phone_secondary TEXT
);

INSERT INTO customers (id, name, phone_primary, phone_secondary) VALUES
  (1, 'Alice', '123-4567', NULL),
  (2, 'Bob', NULL, '987-6543'),
  (3, 'Charlie', '', '555-0000'),
  (4, 'Diana', NULL, NULL);

-- Modify the SELECT query to use NULLIF on phone_primary
-- Your code here

Need a hint?

Use NULLIF(phone_primary, '') to convert empty strings to NULL before using COALESCE.

4

Complete the query with ordering by customer name

Add an ORDER BY clause to the previous query to sort the results by name in ascending order.

SQL

CREATE TABLE customers (
  id INTEGER,
  name TEXT,
  phone_primary TEXT,
  phone_secondary TEXT
);

INSERT INTO customers (id, name, phone_primary, phone_secondary) VALUES
  (1, 'Alice', '123-4567', NULL),
  (2, 'Bob', NULL, '987-6543'),
  (3, 'Charlie', '', '555-0000'),
  (4, 'Diana', NULL, NULL);

SELECT id, name, COALESCE(NULLIF(phone_primary, ''), phone_secondary) AS contact_phone FROM customers
-- Add ORDER BY name ascending
-- Your code here

Need a hint?

Use ORDER BY name ASC to sort the results by customer name.