Filter Assignments

Qn 1 Get all movies (films) that have a rental rate greater than $3.
I identified that I need all columns and a simple condition on rental_rate, so I used SELECT * with a WHERE filter.

SELECT * FROM film WHERE rental_rate > 3;

Qn 2 Get all movies that have a rental rate greater than $3 and a replacement cost less than $20.
There are two conditions, so I combined them using AND in the WHERE clause.

SELECT * FROM film WHERE rental_rate > 3 AND replacement_cost < 20;

Qn 3 Get all movies that are either rated as 'PG' or have a rental rate of $0.99.
Since either condition can be true, I used OR between rating and rental_rate conditions.

SELECT * FROM film WHERE rating = 'PG' OR rental_rate = 0.99;

Qn 4 Show the first 10 movies sorted by rental rate (highest first).
I sorted using ORDER BY rental_rate DESC and limited results using LIMIT 10.

SELECT * FROM film ORDER BY rental_rate DESC LIMIT 10;

Qn 5 Skip the first 5 movies and fetch the next 3 sorted by rental rate in ascending order.
I used ORDER BY for sorting and OFFSET with LIMIT to skip and fetch rows.

 SELECT * FROM film ORDER BY rental_rate ASC LIMIT 3 OFFSET 5;

Qn 6 Skip the first 5 movies and fetch the next 3 sorted by rental rate in ascending order.
This is identical to the previous question, so I used the same LIMIT and OFFSET logic.

SELECT * FROM film ORDER BY rental_rate ASC LIMIT 3 OFFSET 5;

Qn 7 Get all movies with a rental duration between 3 and 7 days.
Since it’s a range condition, I used BETWEEN for cleaner syntax.

SELECT * FROM film WHERE rental_duration BETWEEN 3 AND 7;

Qn 8 Get all movies where the title starts with 'A' and ends with 'e'.
I used LIKE with pattern matching for start and end conditions combined with AND.

SELECT * FROM film WHERE title LIKE 'A%e';

Qn 9 Find all customers who do not have an email address listed.
Missing values are represented by NULL, so I used IS NULL to filter.

SELECT * FROM customer WHERE email IS NULL;

Qn 10 Find all movies released in 2006 with a rental rate of $2.99 or $3.99, and their title starts with 'S'. Display the top 5 results.
I combined multiple conditions using AND and OR, then limited results using LIMIT.

SELECT title, rental_rate, release_year FROM film WHERE release_year = 2006 AND (rental_rate = 2.99 OR rental_rate = 3.99) AND title LIKE 'S%' LIMIT 5;

Qn 11 Display 10 customers after skipping the first 20, sorted alphabetically by last name.
I sorted using ORDER BY and used OFFSET to skip rows before applying LIMIT.

SELECT * FROM customer ORDER BY last_name ASC LIMIT 10 OFFSET 20;

Qn 12 Get the top 5 movies with the highest replacement cost, skipping the most expensive one.
I sorted in descending order and skipped the first row using OFFSET.

SELECT * FROM film ORDER BY replacement_cost DESC LIMIT 5 OFFSET 1;

Qn 13 Find all rentals that occurred between '2005-05-01' and '2005-06-01'.
Since it’s a date range, I used BETWEEN on the rental_date column.

SELECT * FROM rental WHERE rental_date BETWEEN '2005-05-01' AND '2005-06-01';

Qn 14 Get all actors whose last names contain the letters "man".
I used LIKE with wildcards on both sides to find substring matches.

SELECT * FROM actor WHERE last_name LIKE '%man%';

Qn 15 Find all movies where the special features are not listed (i.e., special_features is NULL).
I checked for NULL values using IS NULL since equality won’t work with NULL.

SELECT * FROM film WHERE special_features IS NULL;

Qn 16 Find all movies where the rental duration is more than 7 days.
This is a simple comparison condition using greater than operator.

 SELECT * FROM film WHERE rental_duration > 7;

Qn 17 Find the first 10 movies with a rental rate of $2.99 or $4.99, a rating of 'R', and a title containing the word "L".
I combined multiple filters and used LIKE for substring matching, then limited results.

SELECT * FROM film WHERE (rental_rate = 2.99 OR rental_rate = 4.99) AND rating = 'R' AND title LIKE '%L%' LIMIT 10;

Qn 18 Find all movies where the title starts with "A" or "B" and ends with "s".
I used LIKE patterns for both conditions and combined them using OR.

 SELECT * FROM film WHERE (title LIKE 'A%s' OR title LIKE 'B%s');

Qn 19 Find all movies where the title contains "Man", "Men", or "Woman".
I used multiple LIKE conditions combined with OR to match any of the words.

SELECT * FROM film WHERE title LIKE '%Man%' OR title LIKE '%Men%' OR title LIKE '%Woman%';

Bonus Q/A

Qn 1 Find all movies where the special features are not listed (i.e., special_features is NULL).
I checked for missing values using IS NULL.

SELECT * FROM film WHERE special_features IS NULL;

Qn 2 Find all movies where the rental duration is more than 7 days.
I used a simple greater-than condition on rental_duration.

 SELECT * FROM film WHERE rental_duration > 7;

Qn 3 Find all movies that have a rental rate of $4.99 and a replacement cost of more than $20.
I combined both conditions using AND.

 SELECT * FROM film WHERE rental_rate = 4.99 AND replacement_cost > 20;

Qn 4 Find all movies that have a rental rate of $0.99 or a rating of 'PG-13'.
Since either condition can satisfy the query, I used OR.

SELECT * FROM film WHERE rental_rate = 0.99 OR rating = 'PG-13';

Qn 5 Retrieve the first 5 rows of movies sorted alphabetically by title.
I sorted using ORDER BY title and limited the output using LIMIT.

SELECT * FROM film ORDER BY title ASC LIMIT 5;

Qn 6 Skip the first 10 rows and fetch the next 3 movies with the highest replacement cost.
I sorted descending and used OFFSET to skip rows before LIMIT.

SELECT * FROM film ORDER BY replacement_cost DESC LIMIT 3 OFFSET 10;

Qn 7 Find all movies where the rating is either 'G', 'PG', or 'PG-13'.
I used OR conditions to match multiple possible values.

SELECT * FROM film WHERE rating IN ('G','PG','PG-13');

Qn 8 Find all movies with a rental rate between $2 and $4.
I used BETWEEN to simplify the range condition.

SELECT * FROM film WHERE rental_rate BETWEEN 2 AND 4;

Qn 9 Find all movies with titles that start with 'The'.
I used LIKE with a prefix pattern.

SELECT * FROM film WHERE title LIKE 'The%';

Qn 10 Find the first 10 movies with a rental rate of $2.99 or $4.99, a rating of 'R', and a title containing the word "Love".
I combined all conditions and used LIKE for substring matching, then applied LIMIT.

SELECT * FROM film WHERE (rental_rate = 2.99 OR rental_rate = 4.99) AND rating = 'R' AND title LIKE '%Love%' LIMIT 10;

Qn 11 Find all movies where the title contains the % symbol.
Since % is a wildcard, I escaped it to treat it as a literal character.

SELECT * FROM film WHERE title LIKE '%%%';

Qn 12 Find all movies where the title contains an underscore (_).
I escaped underscore since it’s a wildcard for single character.

SELECT * FROM film WHERE title LIKE '%_%';

Qn 13 Find all movies where the title starts with "A" or "B" and ends with "s".
I used LIKE patterns and combined both cases with OR.

SELECT * FROM film WHERE title LIKE 'A%s' OR title LIKE 'B%s';

Qn 14 Find all movies where the title contains "Man", "Men", or "Woman".
I used multiple LIKE conditions joined with OR.

SELECT * FROM film WHERE title LIKE '%Man%' OR title LIKE '%Men%' OR title LIKE '%Woman%';

Qn 15 Find all movies with titles that contain digits (e.g., "007", "2", "300").
I used pattern matching with numeric ranges.

SELECT * FROM film WHERE title ~ '[0-9]';

Qn 16 Find all movies with titles containing a backslash ().
I escaped the backslash character in the LIKE pattern.

SELECT * FROM film WHERE title LIKE '%\\%';

Qn 17 Find all movies where the title does contain the words "Love" or "Hate".
I used LIKE with OR to match either word.

SELECT * FROM film WHERE title LIKE '%Love%' OR title LIKE '%Hate%';

Qn 18 Find the first 5 movies with titles that end with "er", "or", or "ar".
I used LIKE with multiple suffix patterns and limited the results.

SELECT * FROM film WHERE title LIKE '%er' OR title LIKE '%or' OR title LIKE '%ar' LIMIT 5;