CA 32 - Filter Assignments

I started by retrieving all movies that have a rental rate greater than $3.

SELECT *
FROM film
WHERE rental_rate > 3;

Then I added multiple conditions. I got movies with rental rate greater than $3 and replacement cost less than $20.

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

Next, I used OR condition retrieved movies that are either rated 'PG' or have a rental rate of $0.99.

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

I also worked with limit and sorting. I showed the first 10 movies sorted by highest rental rate.

SELECT *
FROM film
ORDER BY rental_rate DESC
LIMIT 10;

Then I used offset to skip rows. I skipped the first 5 movies and fetched the next 3 in ascending order.

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

I retrieved movies with rental duration between 3 and 7 days.

SELECT *
FROM film
WHERE rental_duration BETWEEN 3 AND 7;

I retrieved movies with rental duration more than 7 days.

SELECT *
FROM film
WHERE rental_duration > 7;

I used pattern matching to find titles starting with 'A' and ending with 'e'.

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

I used pattern matching with or.

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

I searched titles containing specific words.

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

I searched actor names containing "man".

SELECT *
FROM actor
WHERE last_name LIKE '%man%';

I checked for NULL values by finding customers without an email.

SELECT *
FROM customer
WHERE email IS NULL;

I found movies where special features are NULL.

SELECT *
FROM film
WHERE special_features IS NULL;

I combined multiple conditions again. I found movies released in 2006 with rental rate 2.99 or 3.99 and title starting 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;

I combined multiple filters again with limit.

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

I retrieved rentals between two dates.

SELECT *
FROM rental
WHERE rental_date BETWEEN '2005-05-01' AND '2005-06-01';

I displayed 10 customers after skipping the first 20, sorted by last name.

SELECT *
FROM customer
ORDER BY last_name
LIMIT 10 OFFSET 20;

I got top movies by replacement cost while skipping the most expensive one.

SELECT *
FROM film
ORDER BY replacement_cost DESC
LIMIT 5 OFFSET 1;