While practicing SQL, I worked on a movie rental database and tried solving different types of queries using conditions, sorting,limits and pattern matching.
1.Movies with rental rate > $3
SELECT * FROM film WHERE rental_rate > 3;
2.Rental rate > $3 AND replacement cost < $20
SELECT *
FROM film
WHERE rental_rate > 3 AND replacement_cost < 20;
3.Movies rated 'PG' OR rental rate = $0.99
SELECT * FROM film WHERE rating = 'PG' OR rental_rate = 0.99;
4.Top 10 movies by highest rental rate
SELECT * FROM film ORDER BY rental_rate DESC LIMIT 10;
5.Skip first 5, fetch next 3 (ascending rental rate)
SELECT * FROM film ORDER BY rental_rate ASC LIMIT 3 OFFSET 5;
6.First 5 movies sorted by title
SELECT * FROM film ORDER BY title ASC LIMIT 5;
7.Skip first 10, fetch next 3 (highest replacement cost)
SELECT * FROM film ORDER BY replacement_cost DESC LIMIT 3 OFFSET 10;
8.Top 5 movies with highest replacement cost (skip most expensive)
SELECT * FROM film ORDER BY replacement_cost DESC LIMIT 5 OFFSET 1;
9.Rental duration between 3 and 7 days
SELECT * FROM film WHERE rental_duration BETWEEN 3 AND 7;
10.Rentals between two dates
SELECT * FROM rental WHERE rental_date BETWEEN '2005-05-01' AND '2005-06-01';
11.Title starts with 'A' and ends with 'e'
SELECT * FROM film WHERE title LIKE 'A%e';
12.Title starts with 'A' or 'B' and ends with 's'
SELECT * FROM film WHERE (title LIKE 'A%s' OR title LIKE 'B%s');
13.Title contains "Man", "Men", or "Woman"
SELECT * FROM film WHERE title LIKE '%Man%' OR title LIKE '%Men%' OR title LIKE '%Woman%';
14.Title starts with 'The'
SELECT * FROM film WHERE title LIKE 'The%';
15.Title contains "Love" or "Hate"
SELECT * FROM film WHERE title LIKE '%Love%' OR title LIKE '%Hate%';
16.Titles ending with 'er', 'or', or 'ar' (top 5)
SELECT * FROM film WHERE title LIKE '%er' OR title LIKE '%or' OR title LIKE '%ar' LIMIT 5;
17.Movies released in 2006, rental rate 2.99 or 3.99, 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;
18.Movies with rental rate 4.99 and replacement cost > 20
SELECT * FROM film WHERE rental_rate = 4.99 AND replacement_cost > 20;
19.Movies with rental rate 0.99 OR rating 'PG-13'
SELECT * FROM film WHERE rental_rate = 0.99 OR rating = 'PG-13';
20.Movies with rating in ('G', 'PG', 'PG-13')
SELECT * FROM film WHERE rating IN ('G', 'PG', 'PG-13');
21.Movies with rental rate between $2 and $4
SELECT * FROM film WHERE rental_rate BETWEEN 2 AND 4;
22.Movies where special_features is NULL
SELECT * FROM film WHERE special_features IS NULL;
23.Customers without email
SELECT * FROM customer WHERE email IS NULL;
24.Movies with rental duration > 7 days
SELECT * FROM film WHERE rental_duration > 7;
25.Movies with rental rate 2.99 or 4.99, rating 'R', title contains "Love"
SELECT * FROM film WHERE rental_rate IN (2.99, 4.99) AND rating ='R' AND title LIKE '%Love%' LIMIT 10;
26.Titles containing % symbol
SELECT * FROM film WHERE title LIKE '%\%%' ESCAPE '\';
27.Titles containing underscore _
SELECT * FROM film WHERE title LIKE '%\_%' ESCAPE '\';
28.Titles containing digits
SELECT * FROM film WHERE title REGEXP '[0-9]';
29.Titles containing backslash ()
SELECT * FROM film WHERE title LIKE '%\\\\%';
30.10 customers after skipping 20 (sorted by last name)
SELECT * FROM customer ORDER BY last_name ASC LIMIT 10 OFFSET 20;
What I Learned
- How to filter data using
WHERE,AND,OR - Sorting with
ORDER BY - Pagination(Page navigation) using
LIMITandOFFSET - Pattern matching with
LIKEandREGEXP - Handling missing data using
NULL
Top comments (0)