DB-TASK-002

Get all movies that have rental rate greater than 3
SELECT * FROM film
WHERE rental_rate > 3;

I used this to filter movies which are costly rental movies.

Movies rental rate > 3 and replacement cost < 20
SELECT * FROM film
WHERE rental_rate > 3 AND replacement_cost < 20;

Here I used AND because both condition must be true.

Movies rated PG or rental rate 0.99
SELECT * FROM film
WHERE rating = 'PG' OR rental_rate = 0.99;

I used OR because any one condition is enough.

First 10 movies sorted by rental rate highest first
SELECT title, rental_rate
FROM film
ORDER BY rental_rate DESC
LIMIT 10;

I sorted high price first then limited to 10.

Skip first 5 movies and get next 3 sorted by rental rate ascending
SELECT title, rental_rate
FROM film
ORDER BY rental_rate ASC
OFFSET 5
LIMIT 3;

OFFSET used to skip rows.

(same question again so same query)
SELECT title, rental_rate
FROM film
ORDER BY rental_rate ASC
OFFSET 5
LIMIT 3;
Movies with rental duration between 3 and 7
SELECT title, rental_duration
FROM film
WHERE rental_duration BETWEEN 3 AND 7;

BETWEEN is easier than writing >= and <=.

Movies title starts with A and ends with e
SELECT title
FROM film
WHERE title LIKE 'A%e';

LIKE used for pattern search.

Customers who do not have email
SELECT first_name, last_name
FROM customer
WHERE email IS NULL;

IS NULL used because = NULL will not work.

Movies released in 2006, rental rate 2.99 or 3.99 and title starts with S, top 5
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;

IN used instead of multiple OR.

Display 10 customers after skipping first 20 sorted by last name
SELECT first_name, last_name
FROM customer
ORDER BY last_name
OFFSET 20
LIMIT 10;

Used for pagination type query.

Top 5 movies highest replacement cost skipping most expensive one
SELECT title, replacement_cost
FROM film
ORDER BY replacement_cost DESC
OFFSET 1
LIMIT 5;

Offset 1 skips most expensive movie.

Rentals between two dates
SELECT *
FROM rental
WHERE rental_date BETWEEN '2005-05-01' AND '2005-06-01';

Used BETWEEN for date range.

Actors last name contain "man"
SELECT first_name, last_name
FROM actor
WHERE last_name LIKE '%man%';

%man% means contains man anywhere.

Movies where special features is NULL
SELECT title
FROM film
WHERE special_features IS NULL;
Movies where rental duration more than 7
SELECT title, rental_duration
FROM film
WHERE rental_duration > 7;
First 10 movies rental rate 2.99 or 4.99, rating R and title contains L
SELECT title, rental_rate, rating
FROM film
WHERE rental_rate IN (2.99, 4.99)
AND rating = 'R'
AND title LIKE '%L%'
LIMIT 10;
Movies title starts with A or B and ends with s
SELECT title
FROM film
WHERE (title LIKE 'A%s' OR title LIKE 'B%s');
Movies title contains Man, Men or Woman
SELECT title
FROM film
WHERE title LIKE '%Man%'
OR title LIKE '%Men%'
OR title LIKE '%Woman%';