ASSIGNMENT 32

Introduction

In SQL, filtering data is essential to retrieve only the required records from a database. This is done using the WHERE clause along with different operators.

In this task, we explore filtering techniques such as:
•WHERE for conditions
•AND, OR for combining conditions
•IN, BETWEEN for range/value filtering
•LIKE, SIMILAR TO for pattern matching
•IS NULL for missing values
•LIMIT, OFFSET for controlling output

These operations help in extracting meaningful and specific data from large datasets. 

Queries with Code & Explanation

1. Films with rental rate > 3

SELECT title, rental_rate
FROM film
WHERE rental_rate > 3;

Filters movies with rental rate greater than 3.

1. Rental rate > 3 AND replacement cost < 20

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

Uses AND to apply multiple conditions.

1. Rating = ‘PG’ OR rental rate = 0.99

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

Retrieves movies satisfying either condition.

1. Top 10 movies by rental rate

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

Sorts and limits results.

1. Skip 5, fetch next 3 (ascending)

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

Skips first 5 rows and fetches next 3.

1. (Same as 5 – alternate syntax)

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

Uses FETCH instead of LIMIT.

1. Rental duration between 3 and 7

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

Filters range using BETWEEN.

1. Title starts with ‘A’ and ends with ‘e’

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

Pattern matching using LIKE.

1. Customers with no email

SELECT first_name, last_name
FROM customer
WHERE email IS NULL;

Finds missing values.

1. Movies (2006, specific rates, title starts with S)

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;

Combines multiple filters.

1. 10 customers after skipping 20

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

Pagination using LIMIT + OFFSET.

1. Top 5 movies by replacement cost (skip highest)

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

Skips the highest and gets next 5.

1. Rentals between two dates

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

Filters date range.

1. Actors with “man” in last name

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

Searches substring using %.

1. Movies with NULL special features

SELECT title
FROM film
WHERE special_features IS NULL;

Finds missing feature values.

1. Rental duration >7

SELECT title, rental_duration
FROM film
WHERE rental_duration > 7;

Filters based on duration.

1. 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;

Combines IN, AND, and LIKE.

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

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

Uses regex-like pattern matching.

1. Title contains Man, Men, or Woman

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


Advanced pattern matching.

Conclusion

This task demonstrates how SQL filtering helps in:
•Extracting specific data using conditions
•Combining multiple filters effectively
•Searching patterns in text
•Handling missing value
•Controlling output size