CA 32 - Filter Assignments

Get all movies (films) that have a rental rate greater than $3.

SELECT title, rental_rate FROM film WHERE rental_rate > 3;

Here, we need to fetch the title and rental_rate from film table and it must fetch the data which is having the rental_rate > 3 so we use the WHERE rental_rate > 30 in the condition.

Get all movies that have a rental rate greater than $3 and a replacement cost less than $20.

SELECT title, rental_rate, Replacement_cost FROM film WHERE rental_rate >3 and replacement_cost < 20;

It is same as the previous promblem in extra we fetch a replacement_cost and using the condition replacement_cost < 20.

Get all movies that are either rated as 'PG' or have a rental rate of $0.99.

SELECT rating, rental_rate FROM film WHERE rating = 'PG' or rental_rate = 0.99;

Here, we need to fetch the rating and rental_rate from film table and with the condition rating = 'PG' or rental_rate = 0.99.

Show the first 10 movies sorted by rental rate (highest first).

SELECT rental_rate FROM film ORDER BY rental_rate DESC LIMIT 10;

Here, we need to fetch rental_rate from film table and with the condition of top 10 highest value so we are using decsending order and LIMIT 10 to the top 10 highest value.

Skip the first 5 movies and fetch the next 3 sorted by rental rate in ascending order.

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

Here, we need to fetch the rental_rate data from film table and to skip the first five and to give the next 3 data sorted in ascending order.

Get all movies with a rental duration between 3 and 7 days.

SELECT title, rental_duration FROM film WHERE rental_duration BETWEEN 3 and 7;

Here, we are fetching the data from title and rental_duration FROM film and that rental_duration must be between 3 and 7.

Get all movies where the title starts with 'A' and ends with 'e'

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

Here, we need to get the title of movie start with 'A' and ends with 'e' from the film table.

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%';

Here, we need to get the title of the movie which contain "MAN","MEN"and "WOMEN" so we used the LIKE'%Man%', LIKE'%Men%' and LIKE'%Woman%'

---

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

SELECT * FROM film WHERE special_features IS NULL;

Here we use IS NULL to find movies with no special feature.

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

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