Now we know how to select for specific columns of data from a table, but if you had a table with a hundred million rows of data, reading through all the rows would be inefficient and perhaps even impossible.

In order to filter certain results from being returned, we need to use a WHERE clause in the query. The clause is applied to each row of data by checking specific column values to determine whether it should be included in the results or not.

Select query with constraints

SELECT column, another_column, … FROM mytable **WHERE _condition_ AND/OR _another_condition_ AND/OR …**;

More complex clauses can be constructed by joining numerous AND or OR logical keywords (ie. num_wheels >= 4 AND doors <= 2). And below are some useful operators that you can use for numerical data (ie. integer or floating point):

In addition to making the results more manageable to understand, writing clauses to constrain the set of rows returned also allows the query to run faster due to the reduction in unnecessary data being returned.

Did you know?

As you might have noticed by now, SQL doesn't require you to write the keywords all capitalized, but as a convention, it helps people distinguish SQL keywords from column and tables names, and makes the query easier to read.

Exercise

Using the right constraints, find the information we need from the Movies table for each task below.

1. Find the movie with a row `id` of 6
	1. SELECT * FROM movies WHERE id = 6;
2. Find the movies released in the `year`s between 2000 and 2010
	1. SELECT * FROM movies WHERE Year BETWEEN 2000 AND 2010;
	2. SELECT COUNT ( * ) FROM movies WHERE Year BETWEEN 2000 AND 2010;
3. Find the movies **not** released in the `year`s between 2000 and 2010
	1. SELECT * FROM movies WHERE Year NOT BETWEEN 2000 AND 2010;
4. Find the first 5 Pixar movies and their release `year`
	1. SELECT Title, Year FROM movies WHERE id <= 5;

Exercise 2 — Tasks

1. Find the movie with a row `id` of 6 ✓
2. Find the movies released in the `year`s between 2000 and 2010 ✓
3. Find the movies **not** released in the `year`s between 2000 and 2010 ✓
4. Find the first 5 Pixar movies and their release `year` ✓

The current webpage is from SQLBolt, focusing on "SQL Lesson 2: Queries with constraints (Pt. 1)." It teaches how to filter and constrain query results using the SQL WHERE clause. You learn about logical operators (AND, OR) and numerical operators (such as =, !=, <, >, BETWEEN, IN, NOT IN) to make queries more precise and efficient.

The lesson provides a sample "Movies" table with columns: Id, Title, Director, Year, and Length_minutes. There are interactive tasks where you practice constructing queries using constraints, including:

• Finding the movie with a row id of 6
• Finding movies released between 2000 and 2010
• Finding movies not released in that range
• Finding the first 5 Pixar movies and their release year

This lesson is both educational and interactive, guiding you to use SQL constraints for data filtering and selection.[1]

1

Certainly! Instead of relying on the id, you can use the ORDER BY clause to sort the movies by their release year and then use LIMIT to get the first 5 Pixar movies with their release year.

Here's the revised SQL query:

SELECT Title, Year
FROM movies
ORDER BY Year
LIMIT 5;

This query sorts all the movies by their release year from earliest to latest and returns the first 5 results.

1

Here are a few different ways you can write an SQL query to get the first 5 Pixar movies and their release year without relying on the id:

1. Using ORDER BY Year ASC (default) and LIMIT ✅

   SELECT Title, Year
   FROM movies
   ORDER BY Year ASC
   LIMIT 5;
   

   This is the most common and readable approach.

2. Using a subquery (to emphasize flexibility) ✅

   SELECT Title, Year
   FROM (
     SELECT Title, Year
     FROM movies
     ORDER BY Year
     LIMIT 5
   ) AS FirstFive;
   

   Useful if you want to manipulate or join the limited set later.

3. Filtering for unique years (if years are not unique, this gets first 5 unique years)

   SELECT DISTINCT Year
   FROM movies
   ORDER BY Year
   LIMIT 5;
   

   Just for years, not titles; you can join it back for titles if needed.

4. With OFFSET to skip a certain number (e.g., get the 6th to 10th movies) ✅

   SELECT Title, Year
   FROM movies
   ORDER BY Year
   LIMIT 5 OFFSET 0;
   

   Offset is 0 for the first 5; you can change for pagination.

5. If you want only Pixar movies and there's a column for studio

   SELECT Title, Year
   FROM movies
   WHERE Studio = 'Pixar'
   ORDER BY Year
   LIMIT 5;
   

   Filter by studio if that's a differentiating factor.

All these methods use the ordering on the release year rather than the movie ID, giving you flexibility and best practices for writing readable SQL queries.

1