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

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

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

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

Hint

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

Practice

1. Why is filtering data using the WHERE clause important in SQL?

easy

A. It automatically sorts the data alphabetically.

B. It changes the structure of the database tables.

C. It creates new tables from existing ones.

D. It helps retrieve only the rows that meet specific conditions.

Solution

1. Step 1: Understand the purpose of filtering

   Filtering is used to select only the rows that match certain criteria from a table.

2. Step 2: Identify what WHERE does

   The WHERE clause sets conditions to limit the rows returned by a query.

3. Final Answer:

   It helps retrieve only the rows that meet specific conditions. -> Option D

4. Quick Check:

   Filtering = Select specific rows [OK]

Hint: Remember: WHERE filters rows to get only what you need [OK]

Common Mistakes:

• Thinking WHERE changes table structure
• Confusing filtering with sorting
• Believing WHERE creates new tables

2. Which of the following is the correct syntax to filter rows where the column age is greater than 30?

easy

A. SELECT * FROM table FILTER age > 30;

B. SELECT * FROM table HAVING age > 30;

C. SELECT * FROM table WHERE age > 30;

D. SELECT * FROM table WHERE age => 30;

Solution

1. Step 1: Recall correct filtering syntax

   The WHERE clause is used with a condition like age > 30 to filter rows.

2. Step 2: Check each option's syntax

   SELECT * FROM table WHERE age > 30; uses correct syntax. SELECT * FROM table FILTER age > 30; uses FILTER which is invalid in SQL. SELECT * FROM table HAVING age > 30; uses HAVING which is for groups, not rows. SELECT * FROM table WHERE age => 30; uses => which is invalid operator.

3. Final Answer:

   SELECT * FROM table WHERE age > 30; -> Option C

4. Quick Check:

   WHERE + condition = correct filter [OK]

Hint: Use WHERE with correct operators like >, =, < [OK]

Common Mistakes:

• Using FILTER instead of WHERE
• Using HAVING without GROUP BY
• Using wrong comparison operators like =>

3. Given the table Employees with columns id, name, and salary, what will this query return?

SELECT name FROM Employees WHERE salary >= 50000;

medium

A. All employee names with salary equal to 50000 only

B. All employee names with salary greater than or equal to 50000

C. All employee names with salary less than 50000

D. All employee names regardless of salary

Solution

1. Step 1: Understand the WHERE condition

   The condition salary >= 50000 selects employees with salary 50000 or more.

2. Step 2: Analyze the SELECT clause

   The query selects only the name column for those filtered employees.

3. Final Answer:

   All employee names with salary greater than or equal to 50000 -> Option B

4. Quick Check:

   salary >= 50000 filters employees [OK]

Hint: >= means greater than or equal to, so includes 50000 [OK]

Common Mistakes:

• Thinking >= means only equal
• Confusing less than with greater than
• Selecting all columns instead of just names

4. Identify the error in this SQL query:

SELECT * FROM Products WHERE price => 100;

medium

A. The comparison operator => is invalid in SQL.

B. The WHERE clause is missing.

C. The SELECT statement should specify columns.

D. The semicolon at the end is not allowed.

Solution

1. Step 1: Check the comparison operator

   The operator => is not valid in SQL; the correct operator is >=.

2. Step 2: Verify other parts of the query

   The WHERE clause is present, SELECT * is valid, and semicolon is allowed.

3. Final Answer:

   The comparison operator => is invalid in SQL. -> Option A

4. Quick Check:

   Use >=, not => for comparisons [OK]

Hint: Use >= for 'greater or equal', not => [OK]

Common Mistakes:

• Using => instead of >=
• Removing WHERE clause accidentally
• Thinking semicolon is an error

5. You have a table Orders with columns order_id, customer_id, and status. You want to find all orders that are either 'shipped' or 'delivered'. Which query correctly filters these orders?

hard

A. SELECT * FROM Orders WHERE status = 'shipped' OR status = 'delivered';

B. SELECT * FROM Orders WHERE status = 'shipped' AND 'delivered';

C. SELECT * FROM Orders WHERE status IN (shipped, delivered);

D. SELECT * FROM Orders WHERE status = 'shipped', 'delivered';

Solution

1. Step 1: Understand filtering for multiple values

   To filter rows where status is either 'shipped' or 'delivered', use OR or IN.

2. Step 2: Analyze each option

   SELECT * FROM Orders WHERE status = 'shipped' OR status = 'delivered'; uses OR correctly. SELECT * FROM Orders WHERE status = 'shipped' AND 'delivered'; uses AND incorrectly. SELECT * FROM Orders WHERE status IN (shipped, delivered); omits quotes around strings, invalid syntax. SELECT * FROM Orders WHERE status = 'shipped', 'delivered'; has invalid syntax.

3. Final Answer:

   SELECT * FROM Orders WHERE status = 'shipped' OR status = 'delivered'; -> Option A

4. Quick Check:

   Use OR to combine conditions for filtering [OK]

Hint: Use OR or IN to filter multiple values [OK]

Common Mistakes:

• Using AND instead of OR for multiple options
• Incorrect syntax with commas in WHERE
• Confusing IN syntax