DB-TASK-002

Let me explain it with some "DVD Rental DataBase" qns

QN: 1
Get all movies with rental rate greater than 3.


SELECT * FROM film WHERE rental_rate > 3

it is to select all columns from film table and condition is rental_rate greater than 3

QN: 2
Movies with rental rate > 3 and replacement cost < 20.

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

two conditions are given so we use AND to combine both.

QN: 3
Movies rated PG or rental rate 0.99.

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

either one condition can be true so we use OR.

QN: 4
First 10 movies sorted by rental rate (highest first).

ANS: SELECT * FROM film ORDER BY rental_rate DESC LIMIT 10;

since, we sort rental_rate in descending and take first 10.

QN: 5
Skip first 5 and get next 3 (ascending).

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

OFFSET skips first 5 rows then LIMIT gives next 3.

QN: 6
Skip first 5 and get next 3 (ascending).

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

same question so same query.

QN: 7
Movies with rental duration between 3 and 7.

ANS: SELECT * FROM film WHERE rental_duration BETWEEN 3 AND 7;

BETWEEN is used for range values.

QN: 8
Title starts with A and ends with e.

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

A% means starts with A and %e means ends with e.

QN: 9
Customers without email.

ANS: SELECT * FROM customer WHERE email IS NULL;

NULL is checked using IS NULL.

QN: 10
Movies in 2006, rental rate 2.99 or 3.99, title starts with S.

ANS:
SELECT title, rental_rate, release_year
FROM film
WHERE release_year = 2006
AND (rental_rate = 2.99 OR rental_rate = 3.99)
AND title LIKE 'S%'
LIMIT 5;

multiple conditions are combined and we limit to top 5.

QN: 11
Skip 20 customers and show next 10 sorted by last name.

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

sorted alphabetically then skip 20 and take next 10.

QN: 12
Top 5 movies with highest replacement cost, skip most expensive one.

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

highest is skipped using OFFSET 1 then next 5 are taken.

QN: 13
Rentals between two dates.

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

BETWEEN is used for date range.

QN: 14
Actors with "man" in last name.

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

%man% means anywhere in the name.

QN: 15
Movies with no special features.

ANS: SELECT * FROM film WHERE special_features IS NULL;

NULL values are checked using IS NULL.

QN: 16
Movies with rental duration more than 7.

ANS: SELECT * FROM film WHERE rental_duration > 7;

condition is greater than 7.

QN: 17
First 10 movies with rental rate 2.99 or 4.99, rating R, title contains L.

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

multiple conditions and title contains L.

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

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

starts with A or B ends with s.

QN: 19
Title contains Man, Men or Woman.

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

checking multiple words using LIKE.