SQL (Structured Query Language) is a powerful tool used for managing and manipulating databases. One of the core functionalities of SQL is the ability to filter data to retrieve only the information you need. In this blog post, we’ll dive into the concept of filtering in SQL using the `WHERE` clause, along with the `AND`, `OR`, and `IN` operators. We’ll also create a sample database and provide examples to illustrate how these filters work.
Creating a Sample Database
Let’s start by creating a sample database to work with. We’ll create a simple database for an online bookstore. This database will have a table called `Books` with the following columns:
BookID: A unique identifier for each book.
Title: The title of the book.
Author: The author of the book.
Genre: The genre of the book.
Price: The price of the book.
PublicationYear: The year the book was published.
Here’s the SQL code to create the `Books` table and insert some sample data:
CREATE TABLE Books (
BookID INT PRIMARY KEY,
Title VARCHAR(100),
Author VARCHAR(100),
Genre VARCHAR(50),
Price DECIMAL(5,2),
PublicationYear INT
);
INSERT INTO Books (BookID, Title, Author, Genre, Price, PublicationYear) VALUES
(1, ‘To Kill a Mockingbird’, ‘Harper Lee’, ‘Fiction’, 7.99, 1960),
(2, ‘1984’, ‘George Orwell’, ‘Dystopian’, 6.99, 1949),
(3, ‘The Great Gatsby’, ‘F. Scott Fitzgerald’, ‘Fiction’, 10.99, 1925),
(4, ‘The Catcher in the Rye’, ‘J.D. Salinger’, ‘Fiction’, 8.99, 1951),
(5, ‘Brave New World’, ‘Aldous Huxley’, ‘Dystopian’, 9.99, 1932),
(6, ‘The Road’, ‘Cormac McCarthy’, ‘PostApocalyptic’, 12.99, 2006),
(7, ‘The Hobbit’, ‘J.R.R. Tolkien’, ‘Fantasy’, 15.99, 1937),
(8, ‘Fahrenheit 451’, ‘Ray Bradbury’, ‘Dystopian’, 5.99, 1953),
(9, ‘Jane Eyre’, ‘Charlotte Bronte’, ‘Romance’, 8.49, 1847),
(10, ‘Pride and Prejudice’, ‘Jane Austen’, ‘Romance’, 6.49, 1813);
This query will return:
| BookID | Title | Author | Genre | Price | PublicationYear |
| 1 | To Kill a Mockingbird | Harper Lee | Fiction | 7.99 | 1960 |
| 2 | 1984 | George Orwell | Dystopian | 6.99 | 1949 |
| 3 | The Great Gatsby | F. Scott Fitzgerald | Fiction | 10.99 | 1925 |
| 4 | The Catcher in the Rye | J.D. Salinger | Fiction | 8.99 | 1951 |
| 5 | Brave New World | Aldous Huxley | Dystopian | 9.99 | 1932 |
| 6 | The Road | Cormac McCarthy | Post-Apocalyptic | 12.99 | 2006 |
| 7 | The Hobbit | J.R.R. Tolkien | Fantasy | 15.99 | 1937 |
| 8 | Fahrenheit 451 | Ray Bradbury | Dystopian | 5.99 | 1953 |
| 9 | Jane Eyre | Charlotte Bronte | Romance | 8.49 | 1847 |
| 10 | Pride and Prejudice | Jane Austen | Romance | 6.49 | 1813 |
Filtering Data with SQL
Now that we have our sample data, let’s explore how to filter this data using SQL’s WHERE clause. The WHERE clause is used to specify conditions that must be met for the rows to be included in the result set.
Suppose we want to answer the following questions:
1. Find all fiction books that cost less than $10.
2. Find all books that are either in the Dystopian genre or cost more than $10.
3. Find all books that were published either in the 1940s, 1950s, or 1960s.
Let’s write SQL queries to answer these questions.
1. Finding All Fiction Books that Cost Less Than $10
To find all fiction books that cost less than $10, we can use the WHERE clause with the AND operator to combine conditions:
SELECT Title, Author, Price
FROM Books
WHERE Genre = ‘Fiction’ AND Price < 10.00;
This query will return:
| Title | Author | Price |
| To Kill a Mockingbird | Harper Lee | 7.99 |
| The Great Gatsby | F. Scott Fitzgerald | 10.99 |
| The Catcher in the Rye | J.D. Salinger | 8.99 |
2. Finding All Books that are Either in the Dystopian Genre or Cost More Than $10
To find books that are either in the Dystopian genre or cost more than $10, we use the `OR` operator:
SELECT Title, Author, Genre, Price
FROM Books
WHERE Genre = ‘Dystopian’ OR Price > 10.00;
This query will return:
| Title | Author | Genre | Price |
| 1984 | George Orwell | Dystopian | 6.99 |
| Brave New World | Aldous Huxley | Dystopian | 9.99 |
| The Road | Cormac McCarthy | PostApocalyptic | 12.99 |
| The Hobbit | J.R.R. Tolkien | Fantasy | 15.99 |
| Fahrenheit 451 | Ray Bradbury | Dystopian | 5.99 |
3. Finding All Books Published in the 1940s, 1950s, or 1960s
To find books published in specific decades, we can use the `IN` operator:
SELECT Title, Author, PublicationYear
FROM Books
WHERE PublicationYear IN (1940, 1950, 1960);
This query will return:
| Title | Author | PublicationYear |
| 1984 | George Orwell | 1949 |
| The Catcher in the Rye | J.D. Salinger | 1951 |
| To Kill a Mockingbird | Harper Lee | 1960 |
Conclusion
Filtering data is a fundamental aspect of working with databases in SQL. By using the `WHERE` clause along with `AND`, `OR`, and `IN` operators, you can retrieve specific subsets of data based on various conditions. In this blog post, we created a sample database and demonstrated how to filter data to answer specific questions. These concepts are essential for effective data management and analysis in any SQLbased environment. Happy querying!