WHERE vs HAVING mental model in SQL - Hands-On Comparison

Understanding WHERE vs HAVING in SQL

📖 Scenario: You are managing a small bookstore's sales database. You want to analyze sales data to find out which books sold well and which did not. You will write SQL queries to filter data before and after grouping.

🎯 Goal: Build SQL queries that use WHERE and HAVING clauses correctly to filter rows before grouping and filter groups after aggregation.

📋 What You'll Learn

Create a table called sales with columns book_title (text), copies_sold (integer), and sale_date (date).

Insert 5 rows of sales data with specific values.

Write a query using WHERE to filter sales before grouping.

Write a query using HAVING to filter groups after aggregation.

💡 Why This Matters

🌍 Real World

Filtering sales data to analyze book performance by date and total copies sold is common in retail and business reporting.

💼 Career

Understanding WHERE vs HAVING is essential for writing correct SQL queries in data analysis, reporting, and database management roles.

1

Create the sales table and insert data

Write SQL statements to create a table called sales with columns book_title (text), copies_sold (integer), and sale_date (date). Then insert these exact rows: ('The Alchemist', 10, '2024-01-10'), ('The Alchemist', 5, '2024-01-15'), ('1984', 8, '2024-01-12'), ('1984', 12, '2024-01-20'), ('Brave New World', 7, '2024-01-18').

SQL

-- Create the sales table and insert the 5 rows below
-- Your code here

Need a hint?

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

2

Filter rows with WHERE before grouping

Write a SQL query to select book_title and the total copies_sold as total_sold from sales. Use WHERE to include only rows where sale_date is before '2024-01-16'. Group the results by book_title.

SQL

CREATE TABLE sales (
  book_title TEXT,
  copies_sold INTEGER,
  sale_date DATE
);

INSERT INTO sales (book_title, copies_sold, sale_date) VALUES
('The Alchemist', 10, '2024-01-10'),
('The Alchemist', 5, '2024-01-15'),
('1984', 8, '2024-01-12'),
('1984', 12, '2024-01-20'),
('Brave New World', 7, '2024-01-18');

-- Write a query selecting book_title and SUM(copies_sold) as total_sold
-- Use WHERE to filter sale_date before '2024-01-16'
-- Group by book_title
-- Your code here

Need a hint?

Use WHERE before GROUP BY to filter rows before grouping.

3

Filter groups with HAVING after aggregation

Write a SQL query to select book_title and the total copies_sold as total_sold from sales. Group by book_title. Use HAVING to include only groups where total_sold is greater than 15.

SQL

CREATE TABLE sales (
  book_title TEXT,
  copies_sold INTEGER,
  sale_date DATE
);

INSERT INTO sales (book_title, copies_sold, sale_date) VALUES
('The Alchemist', 10, '2024-01-10'),
('The Alchemist', 5, '2024-01-15'),
('1984', 8, '2024-01-12'),
('1984', 12, '2024-01-20'),
('Brave New World', 7, '2024-01-18');

SELECT book_title, SUM(copies_sold) AS total_sold
FROM sales
WHERE sale_date  15
-- Your code here

Need a hint?

Use HAVING after GROUP BY to filter groups based on aggregate values.

4

Compare WHERE and HAVING in one query

Write a SQL query to select book_title and total copies_sold as total_sold from sales. Use WHERE to filter rows where sale_date is on or after '2024-01-12'. Group by book_title. Use HAVING to include only groups where total_sold is at least 15.

SQL

CREATE TABLE sales (
  book_title TEXT,
  copies_sold INTEGER,
  sale_date DATE
);

INSERT INTO sales (book_title, copies_sold, sale_date) VALUES
('The Alchemist', 10, '2024-01-10'),
('The Alchemist', 5, '2024-01-15'),
('1984', 8, '2024-01-12'),
('1984', 12, '2024-01-20'),
('Brave New World', 7, '2024-01-18');

SELECT book_title, SUM(copies_sold) AS total_sold
FROM sales
GROUP BY book_title
HAVING SUM(copies_sold) > 15;

-- Write a query selecting book_title and SUM(copies_sold) as total_sold
-- Use WHERE to filter sale_date >= '2024-01-12'
-- Group by book_title
-- Use HAVING to filter groups where total_sold >= 15
-- Your code here

Need a hint?

Use WHERE to filter rows before grouping and HAVING to filter groups after aggregation.