Filtering Data with WHERE

The Power of Filtering

Why Filter?

In a real-world database, you might have millions of rows. The WHERE clause is your tool for finding exactly what you need by applying a logical filter to every row.

Imagine you have a massive list of every movie ever made. Without a filter, retrieving data is like trying to find one specific grain of sand on a beach. The WHERE clause acts as a sieve, checking every row and only keeping the ones that meet your specific criteria.

The Power of Filtering

Why Filter?

In SQL, SELECT picks columns, but the WHERE clause acts as a filter for rows. Imagine a database with millions of movies; you usually only need a specific subset, like those from a certain year or genre.

Welcome to Module 2! Previously, you learned how to grab columns, but often you don't want to see every single row in a database. The WHERE clause is your gatekeeper, allowing you to find exactly what you're looking for, like movies from a specific era.

The Movie Database Schema

Know Your Data

Before filtering, we must understand our schema. We have two tables: Movie and Rating. They are connected via the RatingCode.

Let's look at our workspace. We have the Movie table and the Rating table. Notice how the RatingCode in our Movie table connects to the Code in the Rating table. This relationship is key to how databases organize information.

Understanding the Schema

Our Movie Database

Before we write queries, we must understand our schema. We will primarily work with the Movie table and its relationship to Rating.

Here is our database map. Notice the Movie table on the left. It contains the Title, Genre, and Year. The RatingCode links it to the Rating table, where we find the full descriptions of movie ratings.

The Syntax of WHERE

Syntax and Comparison

The WHERE clause must always follow the FROM clause. We use comparison operators to set our rules.

In SQL, order matters. You SELECT your columns, tell the database which table to look in with FROM, and finally, define your filter with WHERE. We use these standard operators to compare values, like checking if a year is greater than 2010 or if a title matches a specific name.

The WHERE Clause Syntax

The Gatekeeper

The WHERE clause always follows the FROM clause. It checks every row and only keeps those that satisfy your condition.

Think of WHERE as a gatekeeper. In SQL, the order is strict: first you SELECT your columns, then you specify the table with FROM, and finally, you apply your filter with WHERE.

Example: Filtering by Text

Searching for Text

When filtering by text, like a Title or Genre, you must wrap the value in single quotes (' ').

SELECT Title, Year
FROM Movie
WHERE Title = 'Inception';

Let's find a specific movie. To find 'Inception', we use the equals operator. Notice the single quotes around the name. Without these, the database would think 'Inception' is a column name instead of a piece of text, resulting in an error.

Comparison Operators

Comparing Values

To filter data, we use comparison operators. For numeric columns like Year, we can find exact matches or ranges.

To filter effectively, we use math! You can find movies from a specific year using the equals sign, or look for modern films using 'greater than'. Note that for numbers like 1994, we don't use quotes.

Example: Filtering by Numbers

Searching by Year

Numeric values, like the Year, do not require quotes. This allows the database to perform mathematical comparisons.

SELECT Title, Year
FROM Movie
WHERE Year > 2010;

Now, let's look for modern movies. By using the 'greater than' operator with the number 2010, we retrieve every film released after that year. Note that there are no quotes here; we only use those for text.

Build a Query: 1994 Hits

Practical Example

Find the Title of all movies released in the year 1994. Drag the clauses into the correct order.

Let's try it out. Your goal is to find titles of movies from 1994. Drag these query blocks into the correct sequence. Perfect! SELECT Title, FROM Movie, WHERE Year equals 1994. The database will now return only the movies from that specific year.

Spot the Error

Common Pitfalls

Beginners often make mistakes with clause order, quotes, or case sensitivity. Can you fix this query?

This query is trying to find all 'Sci-Fi' movies, but it's full of errors. Click on the parts of the code that look wrong to fix them. Perfect. The query is now valid and ready to run. Great! The WHERE clause must always follow the FROM clause. Exactly. 'Sci-Fi' is a text string, so it needs those single quotes.

Common Pitfalls

Watch Out!

Beginners often make these three mistakes:

Even pros make mistakes. Don't mix up the order! And remember, numbers like Year are naked—no quotes required. Finally, while SELECT is flexible, the actual movie titles or genres in the data are usually case-sensitive.

Challenge: Modern Movies

Write Your Query

Write a query to retrieve the Title and Year of all movies released after 2010.

It's your turn. Write a query to find the titles and years for all movies released after 2010. Use the editor and submit when you're ready.

Exercise: Finding Classic Movies

Complete the Query

We want to find the Title and Genre of all movies released in or before the year 1995.

It's your turn. Complete the WHERE clause to filter for movies released in 1995 or earlier. Type the missing operator and value into the box.

Challenge: The Movie Detective

Final Challenge

A user is looking for a movie but only remembers it was a Drama released in the year 1994. Write the full query to find it.

Can you find the movie? Write a query that selects all columns from the Movie table where the Genre is 'Drama'. For now, just focus on the Genre filter.