filter assigment

1. Movies where special features are not listed

Sometimes data is missing. In SQL, missing values are stored as NULL.

SELECT * FROM film WHERE special_features IS NULL;

Explanation
We cannot use = with NULL because NULL means "unknown" So we use IS NULL to check if the column has no value.

2. Movies with rental duration more than 7 days

SELECT *  FROM film WHERE rental_duration > 7;

Explanation

We use > to filter movies whose rental period is greater than 7 days.

3. Movies with rental rate 4.99 and replacement cost > 20

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

Explanation

AND means both conditions must be true We are filtering premium movies that are costly to replace.

4. Movies with rental rate 0.99 or rating PG-13

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

Explanation

OR means either condition can be true This helps find cheap movies or specific rated movies.

5. First 5 movies sorted alphabetically

SELECT * FROM film ORDER BY title ASC LIMIT 5;

Explanation

ORDER BY sorts the titles alphabetically the LIMIT 5 ensures only the first 5 records are shown.

6. Skip first 10 and fetch next 3 highest replacement cost

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

Explanation

We first sort movies by highest replacement cost OFFSET 10 skips the first 10 rows LIMIT 3 gives the next 3 rows.

7. Movies with rating G, PG, or PG-13

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

Explanation

IN is used when checking multiple values is cleaner than writing multiple OR conditions.

8. Movies with rental rate between 2 and 4

SELECT * FROM film WHERE rental_rate BETWEEN 2 AND 4;

Explanation

BETWEEN includes both 2 and 4 it simplifies range conditions.

9. Movies with titles starting with "The"

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

Explanation

LIKE is used for pattern matching % means any characters after "The".

10. First 10 movies with multiple conditions

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

Explanation

We combine conditions:

rental rate is either 2.99 or 4.99 ,rating must be R and title must contain "Love"

11. Titles containing % symbol

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

Explanation

% is normally a wildcard it search the literally, we escape it using .

12. Titles containing underscore (_)

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

Explanation

_ means one character in SQL it escape it to treat it as a normal character.

13. Titles starting with A or B and ending with s

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

Explanation
A%s ---> starts with A, ends with s
B%s ---> starts with B, ends with s

14. Titles containing Man, Men, or Woman

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

Explanation
We search for specific words inside the title using %.

15. Titles containing digits

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

16. Titles containing backslash ()

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

Explanation

Backslash is a special escape character So we use double escaping (\\) to match a single .

1. Titles containing "Love" or "Hate"

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

Explanation
We check if either word exists anywhere in the title.

18. First 5 movies ending with "er", "or", or "ar"

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

Explanation

%er, %or, %ar means titles ending with those suffixes the LIMIT 5 restricts output.