Filtering Data with the WHERE Clause

Introduction to the WHERE Clause

The Filter for Your Data

In SQL, the WHERE clause acts as a powerful filter. While SELECT and FROM grab columns and tables, WHERE allows you to pick specific rows based on conditions you define.

Think of it as a sieve that only lets the data you actually need pass through to your results.

Welcome to the lesson on filtering data. In the real world, databases contain millions of records, but you usually only need a small subset. Imagine this full Movie table being poured into a funnel. The WHERE clause is that funnel—it ensures that only the rows meeting your criteria come out the other side.

• The WHERE clause filters records at the row level.
• It returns only the rows that satisfy a specific condition.
• It is essential for handling large datasets efficiently.

Syntax and the Movie Schema

The Syntax Template

SELECT column1, column2
FROM table_name
WHERE condition;

Our Dataset: The Movie Table

We will use the Movie table for all examples. Note the data types for each column, as they determine how you write your filters.

Let's look at the syntax. The WHERE clause always follows the FROM clause. We'll be working with the Movie table, which includes columns like ID, Title, Genre, RatingCode, and Year. Pay close attention to whether a column is an integer or a varchar, as this changes how we handle the filter values.

• WHERE always comes after the FROM clause.
• The Movie table contains both numeric (Year) and text (Genre, RatingCode) columns.
• Conditions consist of a column name, an operator, and a value.

Filtering by Numeric Conditions

Working with Numbers

When filtering numeric columns like Year, you use standard comparison operators: =, >, <, >=, <=.

Rule: Do NOT use quotes for numeric values.

To filter numbers, like the Year column, we use operators like 'greater than' or 'equals'. For example, to find movies released after the year 2000, we write: WHERE Year > 2000. Notice that 2000 is not in quotes. Only the rows for Inception and The Dark Knight remain because they satisfy the condition.

• Use comparison operators for numbers.
• Numeric values are written plainly (e.g., 2000).
• Example: WHERE Year > 2000 finds modern movies.

Filtering by Text Matches

Working with Text

When filtering text columns like Genre or RatingCode, you must wrap the value in single quotes (' ').

SQL is case-sensitive for data: 'Sci-Fi' is different from 'sci-fi'.

Filtering text is slightly different. Let's look for movies in the Sci-Fi genre. We use single quotes around the word Sci-Fi. If you forget these quotes, SQL will mistake the text for a column name and throw an error. This query returns Jurassic Park, The Matrix, and Inception.

• Text values must be enclosed in single quotes.
• Use the equals operator (=) for exact text matches.
• Example: WHERE Genre = 'Sci-Fi'.

Spot the Syntax Error

Examine the SQL queries below. One of them will cause an error. Click the query that is written incorrectly.

It's easy to make small mistakes when starting out. Look at these three queries carefully. Can you spot which one will fail? Click on the incorrect code. Actually, that query is perfectly fine. Remember, numbers don't need quotes, and text values like 'Sci-Fi' require single quotes. Try again. That's right! You cannot use double quotes for text values in standard SQL, and you definitely need quotes for strings like 'Action'. Excellent eye!

• Identify common WHERE clause mistakes.
• Differentiate between text and numeric formatting.
• Recognize correct operator usage.

Hands-on Coding: Rating Filter

Your Turn!

Write a query to retrieve the Title of all movies that have a RatingCode of 'PG-13'.

Remember to capitalize keywords and use single quotes for text values.

Now it's your turn to write some SQL. Use the editor to find the titles of all movies rated 'PG-13'. Be careful with your quotes and capitalization!

• Apply the WHERE clause to a text column.
• Select a specific column (Title).
• Use correct single quote syntax.