Why filtering is essential in SQL - See It in Action

Why Filtering is Essential in SQL Queries

📖 Scenario: You work at a small bookstore that keeps track of all book sales in a database table called sales. The table has columns book_title, author, genre, and copies_sold.The store owner wants to see only the sales data for books that sold more than 50 copies to understand which books are popular.

🎯 Goal: Build a SQL query step-by-step that filters the sales table to show only books with more than 50 copies sold.

📋 What You'll Learn

Create the sales table with the exact columns and sample data

Add a variable to set the minimum copies sold threshold

Write a SQL query that selects all columns from sales where copies_sold is greater than the threshold

Complete the query with an ORDER BY clause to sort results by copies_sold descending

💡 Why This Matters

🌍 Real World

Filtering is essential in real databases to find relevant information quickly, like best-selling books or customers from a specific city.

💼 Career

Database professionals use filtering daily to write queries that extract meaningful data for reports, dashboards, and decision-making.

1

Create the sales table with sample data

Write a SQL statement to create a table called sales with columns book_title (text), author (text), genre (text), and copies_sold (integer). Insert these exact rows: ('The Great Gatsby', 'F. Scott Fitzgerald', 'Classic', 120), ('Modern JavaScript', 'John Doe', 'Programming', 45), ('Cooking 101', 'Jane Smith', 'Cooking', 75).

SQL

-- Create the sales table and insert sample data
-- Your code here

Need a hint?

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

2

Set the minimum copies sold threshold

Create a variable called min_copies and set it to 50 using a CTE. This will be used to filter books that sold more than 50 copies.

SQL

CREATE TABLE sales (
  book_title TEXT,
  author TEXT,
  genre TEXT,
  copies_sold INTEGER
);

INSERT INTO sales (book_title, author, genre, copies_sold) VALUES
('The Great Gatsby', 'F. Scott Fitzgerald', 'Classic', 120),
('Modern JavaScript', 'John Doe', 'Programming', 45),
('Cooking 101', 'Jane Smith', 'Cooking', 75);

-- Set the minimum copies sold threshold
-- Your code here

Need a hint?

Use a WITH clause (CTE) to define the parameter: WITH params AS (SELECT 50 AS min_copies) followed by a SELECT to make it valid.

3

Write a query to filter books with copies sold greater than the threshold

Modify the query to select all columns from sales joined with params where copies_sold is greater than min_copies. Use FROM sales, params and WHERE copies_sold > min_copies.

SQL

CREATE TABLE sales (
  book_title TEXT,
  author TEXT,
  genre TEXT,
  copies_sold INTEGER
);

INSERT INTO sales (book_title, author, genre, copies_sold) VALUES
('The Great Gatsby', 'F. Scott Fitzgerald', 'Classic', 120),
('Modern JavaScript', 'John Doe', 'Programming', 45),
('Cooking 101', 'Jane Smith', 'Cooking', 75);

-- Select books with copies_sold greater than min_copies using params
WITH params AS (SELECT 50 AS min_copies)
-- Your code here

Need a hint?

Replace the SELECT with SELECT * FROM sales, params WHERE copies_sold > min_copies. The comma creates a cross join.

4

Add ORDER BY to sort results by copies sold descending

Complete the SQL query by adding ORDER BY copies_sold DESC to sort the filtered results from highest to lowest copies sold.

SQL

CREATE TABLE sales (
  book_title TEXT,
  author TEXT,
  genre TEXT,
  copies_sold INTEGER
);

INSERT INTO sales (book_title, author, genre, copies_sold) VALUES
('The Great Gatsby', 'F. Scott Fitzgerald', 'Classic', 120),
('Modern JavaScript', 'John Doe', 'Programming', 45),
('Cooking 101', 'Jane Smith', 'Cooking', 75);

-- Select books with copies_sold greater than min_copies using params and order by copies_sold descending
WITH params AS (SELECT 50 AS min_copies)
SELECT * FROM sales, params WHERE copies_sold > min_copies;
-- Your code here

Need a hint?

Add ORDER BY copies_sold DESC at the end of the query.