This is my second set of SQL practice problems using the Sakila database. This batch is all about filtering rows with WHERE conditions, pattern matching with LIKE, handling NULL values, and controlling how many rows you get back using LIMIT and OFFSET. A solid set of problems that covers a lot of ground you will use in real work.
Simple Filtering with WHERE
Starting with the basics. Get all movies with a rental rate greater than three dollars:
SELECT *
FROM film
WHERE rental_rate > 3;
Now adding a second condition. Movies with a rental rate above three and a replacement cost below twenty:
SELECT *
FROM film
WHERE rental_rate > 3
AND replacement_cost < 20;
Using OR this time. Movies that are either rated PG or have a rental rate of 0.99:
SELECT *
FROM film
WHERE rating = 'PG'
OR rental_rate = 0.99;
The difference between AND and OR is worth keeping in mind. AND means both conditions have to be true. OR means at least one of them has to be true.
Sorting and Limiting Results
Top 10 movies sorted by rental rate from highest to lowest:
SELECT *
FROM film
ORDER BY rental_rate DESC
LIMIT 10;
Skipping Rows with OFFSET
This is where OFFSET comes in. It lets you skip a certain number of rows before starting to fetch. Skip the first 5 movies and get the next 3 sorted by rental rate ascending:
SELECT *
FROM film
ORDER BY rental_rate ASC
LIMIT 3 OFFSET 5;
LIMIT controls how many rows you get and OFFSET controls where you start from. Together they are how pagination works in SQL.
Filtering by a Range with BETWEEN
Movies with a rental duration between 3 and 7 days:
SELECT *
FROM film
WHERE rental_duration BETWEEN 3 AND 7;
BETWEEN is inclusive on both ends so this includes movies with exactly 3 days and exactly 7 days as well.
Pattern Matching with LIKE
Movies where the title starts with A and ends with e:
SELECT *
FROM film
WHERE title LIKE 'A%e';
The percent sign is a wildcard in SQL. It matches any number of characters. So A%e means anything that starts with A, has anything in the middle, and ends with e.
Movies where the title starts with A or B and ends with s:
SELECT *
FROM film
WHERE (title LIKE 'A%s' OR title LIKE 'B%s');
Movies where the title contains the word Man, Men, or Woman:
SELECT *
FROM film
WHERE title LIKE '%Man%'
OR title LIKE '%Men%'
OR title LIKE '%Woman%';
Actors whose last name contains the letters man:
SELECT *
FROM actor
WHERE last_name LIKE '%man%';
Handling NULL Values
Find all customers who do not have an email address listed:
SELECT *
FROM customer
WHERE email IS NULL;
Find all movies where special features are not listed:
SELECT *
FROM film
WHERE special_features IS NULL;
NULL in SQL means the value is missing or unknown. You cannot use equals sign to check for NULL. You have to use IS NULL or IS NOT NULL. That is a mistake a lot of people make early on.
Combining Multiple Conditions
Movies released in 2006 with a rental rate of 2.99 or 3.99 and a title starting with S, top 5 results:
SELECT title, rental_rate, release_year
FROM film
WHERE release_year = 2006
AND rental_rate IN (2.99, 3.99)
AND title LIKE 'S%'
LIMIT 5;
The IN keyword is a cleaner way to write multiple OR conditions on the same column. Instead of writing rental_rate = 2.99 OR rental_rate = 3.99, you just pass both values inside IN and it checks both at once.
Top 10 movies with a rental rate of 2.99 or 4.99, rated R, and title containing the letter L:
SELECT *
FROM film
WHERE rental_rate IN (2.99, 4.99)
AND rating = 'R'
AND title LIKE '%L%'
LIMIT 10;
More Pagination
Display 10 customers after skipping the first 20, sorted alphabetically by last name:
SELECT *
FROM customer
ORDER BY last_name ASC
LIMIT 10 OFFSET 20;
Top 5 movies with the highest replacement cost, skipping the most expensive one:
SELECT *
FROM film
ORDER BY replacement_cost DESC
LIMIT 5 OFFSET 1;
Skipping the first row after sorting by replacement cost descending means you skip the single most expensive film and start from the second one.
Filtering by Date Range
Rentals that happened between May 1 and June 1 of 2005:
SELECT *
FROM rental
WHERE rental_date BETWEEN '2005-05-01' AND '2005-06-01';
Filtering by Duration
Movies where the rental duration is more than 7 days:
SELECT *
FROM film
WHERE rental_duration > 7;
What I Picked Up from This Set
This batch of problems made me a lot more comfortable with a few things. LIKE and the percent wildcard for pattern matching, IS NULL for catching missing values, IN as a cleaner alternative to stacking OR conditions, and OFFSET for skipping rows when you need pagination. These are not fancy features but they come up constantly in real queries and knowing them well makes a big difference.
Top comments (0)