Filter Assignments

1.Find all movies where the special features are not listed (i.e., special_features is NULL).

SELECT title FROM film WHERE special_features IS NULL;

output:
title
Academy Dinosaur
Ace Goldfinger
Adaptation Holes
Affair Prejudice
African Egg

2) Find all movies where the rental duration is more than 7 days.

SELECT title, rental_duration FROM film WHERE rental_duration > 7;

output:
title | rental_duration
---------------------+-----------------
Alamo Videotape | 8
Brotherhood Blanket | 9
Chicago North | 10
Dragon Squad | 8

3) Find all movies that have a rental rate of $4.99 and a replacement cost of more than $20.

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

output:
title | rental_rate | replacement_cost
--------------------+-------------+------------------
Ace Goldfinger | 4.99 | 22.99
Airport Pollock | 4.99 | 24.99
Bright Encounters | 4.99 | 21.99

4) Find all movies that have a rental rate of $0.99 or a rating of 'PG-13'.

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

Output:
title | rental_rate | rating
-------------------+-------------+--------
Academy Dinosaur | 0.99 | PG
Alien Center | 2.99 | PG-13
Angels Life | 0.99 | PG-13

5) Retrieve the first 5 rows of movies sorted alphabetically by title.

SELECT title FROM film ORDER BY title ASC LIMIT 5;

output:
title
Academy Dinosaur
Ace Goldfinger
Adaptation Holes
Affair Prejudice
African Egg

6) Skip the first 10 rows and fetch the next 3 movies with the highest replacement cost.

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

output:
title | replacement_cost
-------------------+------------------
Anthem Luke | 24.99
Apollo Teen | 24.99
Arabia Dogma | 24.99

7) Find all movies where the rating is either 'G', 'PG', or 'PG-13'.

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

Output:
title | rating
-------------------+--------
Academy Dinosaur | PG
Ace Goldfinger | G
Alien Center | PG-13

8) Find all movies with a rental rate between $2 and $4.

SELECT title, rental_rate FROM film WHERE rental_rate BETWEEN 2 AND 4;

Output:
title | rental_rate
-------------------+-------------
Adaptation Holes | 2.99
Alien Center | 2.99
Apollo Teen | 3.99

9) Find all movies with titles that start with 'The'.

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

Output:
title
The Matrix
The Pianist
The Others
The Truman Show

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".

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

Output:
title | rental_rate | rating
-----------------+-------------+--------
Crazy Love | 2.99 | R
Dangerous Love | 4.99 | R
Endless Love | 2.99 | R

11) Find all movies where the title contains the % symbol.

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

Output:
title
100% Love
50% Chance

12) Find all movies where the title contains an underscore (_).

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

Output:
title
Mission_Impossible
Fast_Furious

13) Find all movies where the title starts with "A" or "B" and ends with "s".

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

Output:
title
Angels Life
Backwards Towns
Brothers Dreams

14) Find all movies where the title contains "Man", "Men", or "Woman".

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

Output:
title
Spider Man
X Men United
Wonder Woman

15) Find all movies with titles that contain digits (e.g., "007", "2", "300").

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

Output:
title
007 Bond
300 Spartans
2 Fast 2 Furious

16) Find all movies with titles containing a backslash ().

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

Output:
title
Escape \ Reality
Path \ Finder

17) Find all movies where the title does contain the words "Love" or "Hate".

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

Output:
title
Crazy Love
Endless Love
Hate Story
Love Actually

18) Find the first 5 movies with titles that end with "er", "or", or "ar".

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

Output:
title
Joker
Creator
Avatar
Doctor
Warrior