NULL in DISTINCT, GROUP BY, and ORDER BY in SQL - Mini Project: Build & Apply

Handling NULL Values in DISTINCT, GROUP BY, and ORDER BY

📖 Scenario: You are managing a small customer database for a local store. Some customers have not provided their email addresses, so those entries are NULL. You want to learn how SQL treats NULL values when using DISTINCT, GROUP BY, and ORDER BY clauses.

🎯 Goal: Build SQL queries that demonstrate how NULL values are handled in DISTINCT, GROUP BY, and ORDER BY clauses using a sample customers table.

📋 What You'll Learn

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

Insert sample data including some NULL email values

Write a query using DISTINCT on the email column

Write a query using GROUP BY on the email column

Write a query using ORDER BY on the email column

💡 Why This Matters

🌍 Real World

Handling NULL values correctly is important in real databases where some data may be missing or unknown.

💼 Career

Understanding how NULLs behave in DISTINCT, GROUP BY, and ORDER BY helps in writing accurate SQL queries for reports and data analysis.

1

Create the customers table and insert data

Create a table called customers with columns id (integer), name (text), and email (text). Insert these exact rows: (1, 'Alice', '[e-mail]'), (2, 'Bob', NULL), (3, 'Charlie', '[e-mail]'), (4, 'David', NULL), (5, 'Eve', '[e-mail]').

SQL

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

Need a hint?

Use CREATE TABLE to define the table and INSERT INTO to add rows. Use NULL for missing emails.

2

Write a query using DISTINCT on email

Write a SQL query that selects distinct email values from the customers table using SELECT DISTINCT email FROM customers.

SQL

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

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

-- Write a query to select distinct email values
-- Your code here

Need a hint?

Use SELECT DISTINCT email FROM customers; to get unique emails including NULL once.

3

Write a query using GROUP BY on email

Write a SQL query that selects email and counts how many customers have each email using SELECT email, COUNT(*) FROM customers GROUP BY email.

SQL

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

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

SELECT DISTINCT email FROM customers;

-- Write a query to group by email and count customers
-- Your code here

Need a hint?

Use GROUP BY email to group rows and COUNT(*) to count customers per email, including NULL group.

4

Write a query using ORDER BY on email

Write a SQL query that selects all columns from customers and orders the results by email using SELECT * FROM customers ORDER BY email.

SQL

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

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

SELECT DISTINCT email FROM customers;

SELECT email, COUNT(*) FROM customers GROUP BY email;

-- Write a query to select all customers ordered by email
-- Your code here

Need a hint?

Use ORDER BY email to sort customers by email. NULL values usually appear first or last depending on the database.