Lesson 2: Queries with constraints

In the previous lesson, we explored how to retrieve data using the SELECT statement. Now it's time to make our queries smarter by filtering the data to return only the rows we're interested in. This is where the WHERE clause comes into play.

Imagine you're working with a large dataset of Oscar-winning movies. Instead of scrolling through every single film, wouldn't it be nice to ask questions like:

• Which movies were directed by James Cameron?
• What Oscar-winning dramas came out after 2000?
• Which films were longer than 2 hours and released in the 2000s?

All of these questions can be answered using WHERE.

🔎 Filtering Rows with WHERE

The WHERE clause is used to apply conditions to your query results. SQL checks each row to see if it matches the condition(s)—and only includes the ones that do.

Basic format:

SELECT column1, column2
FROM oscar_movies
WHERE condition;

Example:

SELECT title, director
FROM oscar_movies
WHERE director = 'James Cameron';

This query returns only the movies directed by James Cameron, filtering out all other films.

📊 Common Comparison Operators

You can use comparison operators to define conditions that filter your data:

Operator | Description              | Example
---------|--------------------------|---------------------------
=        | Equal to                 | director = 'James Cameron'
!=       | Not equal to             | year != 1994
<        | Less than                | year < 2010
>        | Greater than             | rating > 8.5
<=       | Less than or equal to    | length_minutes <= 120
>=       | Greater than or equal to | year >= 2000

Example queries:

-- Movies released after 2000
SELECT title, year
FROM oscar_movies
WHERE year > 2000;

-- Movies shorter than 2 hours
SELECT title, length_minutes
FROM oscar_movies
WHERE length_minutes < 120;

🔗 Combining Conditions: AND / OR

To filter based on more than one condition, you can use logical operators like AND and OR. This gives you more control over the data you retrieve.

Basic format:

SELECT column, another_column
FROM oscar_movies
WHERE condition AND/OR another_condition;

Examples:

-- Movies released after 1999 AND longer than 100 minutes
SELECT title, year, length_minutes
FROM oscar_movies
WHERE year > 1999 AND length_minutes > 100;

-- Movies released in 1997 OR 2009
SELECT title, year
FROM oscar_movies
WHERE year = 1997 OR year = 2009;

🎯 Working with Ranges: BETWEEN

If you're checking whether a value falls within a certain range—like a span of years—you can use the BETWEEN operator. It's inclusive of both the start and end values.

Basic format:

SELECT column, another_column
FROM oscar_movies
WHERE column_name BETWEEN value1 AND value2;

Examples:

-- Movies released between 2000 and 2010 (inclusive)
SELECT title, year
FROM oscar_movies
WHERE year BETWEEN 2000 AND 2010;

-- Movies NOT in the 1990s
SELECT title, year
FROM oscar_movies
WHERE year NOT BETWEEN 1990 AND 1999;

📋 Using IN for Lists

When you're checking for multiple specific values in a column (such as multiple genres or directors), use the IN keyword with a comma-separated list. This is cleaner and easier than writing multiple OR conditions.

Basic format:

SELECT column, another_column
FROM oscar_movies
WHERE column_name IN (value1, value2, value3);

Examples:

-- Movies by specific directors
SELECT title, director
FROM oscar_movies
WHERE director IN ('Steven Spielberg', 'Martin Scorsese', 'Francis Ford Coppola');

-- Movies NOT in specific genres
SELECT title, genre
FROM oscar_movies
WHERE genre NOT IN ('Comedy', 'Horror', 'Romance');

💡 Pro Tip: Even though SQL isn't case-sensitive, it's a best practice to capitalize SQL keywords like SELECT, FROM, WHERE, and AND. It helps make your queries easier to read and understand.

✅ Key Takeaways

• WHERE lets you filter rows to return only the ones that meet certain criteria
• Use comparison operators (=, !=, <, >, <=, >=) to create basic conditions
• Combine conditions with AND/OR to create more complex filters
• BETWEEN is perfect for ranges and includes both boundary values
• IN is ideal for checking multiple values instead of writing multiple OR conditions
• NOT BETWEEN and NOT IN work as the opposite of their counterparts
• Filtering improves performance by reducing the amount of data processed and returned
• Proper formatting with capitalized keywords makes queries more readable

📝 Exercise Overview

In this lesson's exercises, you'll be working with the oscar_movies table to practice applying constraints with the WHERE clause.

You'll get hands-on practice with:

• Finding movies by a specific director
• Filtering films by release year ranges
• Excluding rows based on a year range
• Combining conditions like year and movie length
• Exploring the first few records from the table
• Filtering by genre and year together

These tasks are designed to help you build confidence writing queries with real-world conditions. Try variations, tweak the values, and see how the output changes. The more you experiment, the better your SQL instincts will become.