Filter in DB

1.SELECT is used to choose what data to display.
2.WHERE is used to filter records based on conditions.
3.AND and OR are used to combine multiple conditions.
4.ORDER BY is used to sort the data either ascending or descending.
5.LIMIT is used to restrict how many rows are shown.
6.OFFSET is used to skip a certain number of rows.
7.BETWEEN is used to filter values within a range.
8.IN is used to match multiple possible values.
9.LIKE and SIMILAR TO are used for pattern matching in text.
10.IS NULL is used to find missing or empty values.

1. Get movies with rental rate greater than 3

SELECT title, rental_rate FROM film WHERE rental_rate > 3;

2. Rental rate > 3 and replacement cost < 20

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

3. Rated PG or rental rate 0.99

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

4. Top 10 movies by rental rate

SELECT title, rental_rate FROM film ORDER BY rental_rate DESC LIMIT 10;

5. Skip 5 and get next 3

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

6. Skip 5 and get next 3

SELECT title, rental_rate FROM film ORDER BY rental_rate ASC OFFSET 5 FETCH NEXT 3 ROWS ONLY;

7. Rental duration between 3 and 7

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

8. Title starts with A and ends with e

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

9. Customers without email

SELECT first_name, last_name FROM customer WHERE email IS NULL;

10. Movies from 2006 with conditions

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;

11. Skip 20 customers, show next 10

SELECT first_name, last_name FROM customer ORDER BY last_name LIMIT 10 OFFSET 20;

12. Highest replacement cost (skip first)

SELECT title, replacement_cost FROM film ORDER BY replacement_cost DESC OFFSET 1 FETCH NEXT 5 ROWS ONLY;

13. Rentals between dates

SELECT rental_id, rental_date, customer_id FROM rental WHERE rental_date BETWEEN '2005-05-01' AND '2005-06-01';

14. Actors with "man" in last name

SELECT first_name, last_name FROM actor WHERE last_name LIKE '%man%';

15. Movies with no special features

SELECT title FROM film WHERE special_features IS NULL;

16. Rental duration more than 7

SELECT title, rental_duration FROM film WHERE rental_duration > 7;

17. Movies with multiple conditions

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

18. Title starts with A or B and ends with s

SELECT title FROM film WHERE title SIMILAR TO '(A|B)%s';

19. Title contains Man, Men, or Woman

SELECT title FROM film WHERE title SIMILAR TO '%(Man|Men|Woman)%';