WHERE with IS NULL and IS NOT NULL in SQL - Mini Project: Build & Apply

Filter Database Records Using WHERE with IS NULL and IS NOT NULL

📖 Scenario: You are managing a small customer database for a local store. Some customers have provided their email addresses, but others have not. You want to learn how to find customers who have or have not shared their email addresses.

🎯 Goal: Build SQL queries step-by-step to filter customers based on whether their email field is NULL or not.

📋 What You'll Learn

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

Insert specific customer records with some email values as NULL.

Write a query to select customers where email is NULL.

Write a query to select customers where email is NOT NULL.

💡 Why This Matters

🌍 Real World

Filtering records with missing or present information is common in real databases, such as finding customers who have not provided contact details.

💼 Career

Database professionals often write queries to handle NULL values correctly to ensure accurate data analysis and reporting.

1

Create the customers table

Write a SQL statement to create a table called customers with three columns: id as an integer primary key, name as text, and email as text that can be NULL.

SQL

-- Write the CREATE TABLE statement for customers
-- Your code here

Need a hint?

Use CREATE TABLE customers and define id INTEGER PRIMARY KEY, name TEXT, and email TEXT. Remember, email can be NULL by default.

2

Insert customer records with some emails NULL

Insert these exact three customers into the customers table: (1, 'Alice', '[e-mail]'), (2, 'Bob', NULL), and (3, 'Charlie', '[e-mail]'). Use three separate INSERT statements.

SQL

CREATE TABLE customers (
    id INTEGER PRIMARY KEY,
    name TEXT,
    email TEXT
);
-- Insert the three customers below
-- Your code here

Need a hint?

Use three INSERT INTO customers (id, name, email) VALUES (...) statements. For Bob, set email to NULL without quotes.

3

Select customers with NULL emails

Write a SQL query to select all columns from customers where the email is NULL. Use WHERE email IS NULL.

SQL

CREATE TABLE customers (
    id INTEGER PRIMARY KEY,
    name TEXT,
    email TEXT
);

INSERT INTO customers (id, name, email) VALUES (1, 'Alice', '[e-mail]');
INSERT INTO customers (id, name, email) VALUES (2, 'Bob', NULL);
INSERT INTO customers (id, name, email) VALUES (3, 'Charlie', '[e-mail]');

-- Select customers where email is NULL
-- Your code here

Need a hint?

Use SELECT * FROM customers WHERE email IS NULL; to find customers without emails.

4

Select customers with NOT NULL emails

Write a SQL query to select all columns from customers where the email is NOT NULL. Use WHERE email IS NOT NULL.

SQL

CREATE TABLE customers (
    id INTEGER PRIMARY KEY,
    name TEXT,
    email TEXT
);

INSERT INTO customers (id, name, email) VALUES (1, 'Alice', '[e-mail]');
INSERT INTO customers (id, name, email) VALUES (2, 'Bob', NULL);
INSERT INTO customers (id, name, email) VALUES (3, 'Charlie', '[e-mail]');

SELECT * FROM customers WHERE email IS NULL;

-- Select customers where email is NOT NULL
-- Your code here

Need a hint?

Use SELECT * FROM customers WHERE email IS NOT NULL; to find customers who have emails.