Filter Assignments

Qn 1: Get all movies (films) that have a rental rate greater than $3.
here we just need to filter out rows where rental_rate is more than 3. nothing else is needed since the question is very direct, so a simple where condition works.

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.
this is similar to the previous one but now there are two conditions. so we use and because both conditions should be true for a row to be selected.

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.
here it is not mandatory for both conditions to be true, even one is enough. so we use or to check for either rating or rental rate.

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

Qn 4: Show the first 10 movies sorted by rental rate (highest first).
Using order by clause I learnt last class and using desc to show the highest value first.

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.
we first sort in ascending order since that is mentioned. then we skip the first 5 rows using offset and take the next 3 using limit.

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

Qn 6: Skip the first 5 movies and fetch the next 3 sorted by rental rate in ascending order.
same logic as the previous qn, just applying offset and limit after sorting.

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

Qn 7: Get all movies with a rental duration between 3 and 7 days.
instead of writing two conditions like greater than and less than, we can directly use between which makes it cleaner and easier to read.

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'.
this is pattern matching. a% means starts with a and %e means ends with e. combining both gives us titles that satisfy both conditions.

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

Qn 9: Find all customers who do not have an email address listed.
if something is not listed in sql, it usually means the value is null. so we check for null using is null.

SELECT * FROM customer WHERE email IS NULL;

sql

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.
this was fun to solve. mixing the smaller queries i already tried to come up with this.

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;

Qn 11: Display 10 customers after skipping the first 20, sorted alphabetically by last name.
as seen earlier we use the offset clause to skip the first 20 values.

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

Qn 12: Get the top 5 movies with the highest replacement cost, skipping the most expensive one.
highest cost means we need the list in reverse or descending order. for skipping most expensive use offset.

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

Qn 13: Find all rentals that occurred between '2005-05-01' and '2005-06-01'.
again using between makes it easy to filter dates within a range without writing multiple conditions.

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".
we want to match anywhere in the string, so we use % on both sides with like "man"

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).
get the rows where value is null for special_features because not listed means null, so we check using is null.

SELECT * FROM film WHERE special_features IS NULL;

Qn 16: Find all movies where the rental duration is more than 7 days.
Filter where rental duration is greater than 7.

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".
Combine multiple filters and search for a letter inside title.

SELECT * FROM film WHERE rental_rate IN (2.99, 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".
we need two starting conditions, so we use or. each condition uses like to match start and end.

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".
Check if title contains any of the given words using OR.

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

Qn 20: Find all movies where the special features are not listed (i.e., special_features is NULL).
same as earlier, just checking for null again.

SELECT * FROM film WHERE special_features IS NULL;

Qn 21: Find all movies where the rental duration is more than 7 days.
we just give a condition where

SELECT * FROM film WHERE rental_duration > 7;

Qn 22: Find all movies that have a rental rate of $4.99 and a replacement cost of more than $20.
Apply AND condition for both rental rate and replacement cost.

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

Qn 23: Find all movies that have a rental rate of $0.99 or a rating of 'PG-13'.
Use OR since either rental rate or rating condition works.

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

Qn 24: Retrieve the first 5 rows of movies sorted alphabetically by title.
Sort alphabetically and take first 5 rows.

SELECT * FROM film ORDER BY title ASC LIMIT 5;

Qn 25: Skip the first 10 rows and fetch the next 3 movies with the highest replacement cost.
Sort by replacement cost descending, skip 10, take next 3.

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

Qn 26: Find all movies where the rating is either 'G', 'PG', or 'PG-13'.

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

Qn 27: Find all movies with a rental rate between $2 and $4.

SELECT * FROM film WHERE rental_rate BETWEEN 2 AND 4;

Qn 28: Find all movies with titles that start with 'The'.
Use LIKE to match titles starting with “The”.

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

Qn 29: 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".
Combine filters and search for specific word “Love” in title.

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

Qn 30: Find all movies where the title contains the % symbol.
Escape special character % and search in title.

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

Qn 31: Find all movies where the title contains an underscore (_).

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

Qn 32: Find all 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');

Qn 33: Find all movies where the title contains "Man", "Men", or "Woman".
Use OR with LIKE to match multiple words.

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

Qn 34: Find all movies with titles that contain digits (e.g., "007", "2", "300").
Use regex to check if title contains digits.

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

Qn 35: Find all movies with titles containing a backslash ().

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

Qn 36: Find all movies where the title does contain the words "Love" or "Hate".
Use OR to check if title contains either word.

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

Qn 37: Find the first 5 movies with titles that end with "er", "or", or "ar".
Match titles ending with specific suffixes and limit results.

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