sql practice — filtering movies

1. find all movies where special features is null

select * from film where special_features is null;

2. find movies with rental duration more than 7 days

select * from film where rental_duration > 7;

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

select * from film where rental_rate = 4.99 and replacement_cost > 20;

4. movies with rental rate 0.99 or rating pg-13

select * from film where rental_rate = 0.99 or rating = 'pg-13';

5. first 5 rows sorted alphabetically by title

select * from film order by title asc limit 5;

6. skip first 10 rows and fetch next 3 highest replacement cost

select * from film order by replacement_cost desc offset 10 limit 3;

7. movies with rating g, pg, pg-13

select * from film where rating in ('g','pg','pg-13');

8. movies with rental rate between 2 and 4

select * from film where rental_rate between 2 and 4;

9. movies with titles starting with "the"

select * from film where title like 'the%';

10. first 10 movies rate 2.99 or 4.99, rating r, title contains love

select * from film where (rental_rate in (2.99,4.99)) and rating='r' and title ilike '%love%' limit 10;

11. movies where title contains % symbol

select * from film where title like '%\%%' escape '\';

12. movies where title contains underscore

select * from film where title like '%\_%' escape '\';

13. titles start with a or b and end with s

select * from film where (title like 'a%s' or title like 'b%s');

14. title contains man, men, or woman

select * from film where title ilike '%man%' or title ilike '%men%' or title ilike '%woman%';

15. titles containing digits

select * from film where title ~ '[0-9]';

16. titles containing backslash

select * from film where title like '%\\%';

17. titles contain love or hate

select * from film where title ilike '%love%' or title ilike '%hate%';

18. first 5 titles ending with er, or, ar

select * from film where title ilike '%er' or title ilike '%or' or title ilike '%ar' limit 5;