DEV Community

Christina Sharon S
Christina Sharon S

Posted on

Filtering a DB using SQL Queries

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;
Enter fullscreen mode Exit fullscreen mode

2.Rental rate > $3 AND replacement cost < $20

SELECT * 
FROM film
WHERE rental_rate > 3 AND replacement_cost < 20;
Enter fullscreen mode Exit fullscreen mode

3.Movies rated 'PG' OR rental rate = $0.99

SELECT * FROM film WHERE rating = 'PG' OR rental_rate = 0.99;
Enter fullscreen mode Exit fullscreen mode

4.Top 10 movies by highest rental rate

SELECT * FROM film ORDER BY rental_rate DESC LIMIT 10;
Enter fullscreen mode Exit fullscreen mode

5.Skip first 5, fetch next 3 (ascending rental rate)

SELECT * FROM film ORDER BY rental_rate ASC LIMIT 3 OFFSET 5;
Enter fullscreen mode Exit fullscreen mode

6.First 5 movies sorted by title

SELECT * FROM film ORDER BY title ASC LIMIT 5;
Enter fullscreen mode Exit fullscreen mode

7.Skip first 10, fetch next 3 (highest replacement cost)

SELECT * FROM film ORDER BY replacement_cost DESC LIMIT 3 OFFSET 10;
Enter fullscreen mode Exit fullscreen mode

8.Top 5 movies with highest replacement cost (skip most expensive)

SELECT * FROM film ORDER BY replacement_cost DESC LIMIT 5 OFFSET 1;
Enter fullscreen mode Exit fullscreen mode

9.Rental duration between 3 and 7 days

SELECT * FROM film WHERE rental_duration BETWEEN 3 AND 7;
Enter fullscreen mode Exit fullscreen mode

10.Rentals between two dates

SELECT * FROM rental WHERE rental_date BETWEEN '2005-05-01' AND '2005-06-01';
Enter fullscreen mode Exit fullscreen mode

11.Title starts with 'A' and ends with 'e'

SELECT * FROM film WHERE title LIKE 'A%e';
Enter fullscreen mode Exit fullscreen mode

12.Title starts with 'A' or 'B' and ends with 's'

SELECT * FROM film WHERE (title LIKE 'A%s' OR title LIKE 'B%s');
Enter fullscreen mode Exit fullscreen mode

13.Title contains "Man", "Men", or "Woman"

SELECT * FROM film WHERE title LIKE '%Man%' OR title LIKE '%Men%' OR title LIKE '%Woman%';
Enter fullscreen mode Exit fullscreen mode

14.Title starts with 'The'

SELECT * FROM film WHERE title LIKE 'The%';
Enter fullscreen mode Exit fullscreen mode

15.Title contains "Love" or "Hate"

SELECT * FROM film WHERE title LIKE '%Love%' OR title LIKE '%Hate%';
Enter fullscreen mode Exit fullscreen mode

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;
Enter fullscreen mode Exit fullscreen mode

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;
Enter fullscreen mode Exit fullscreen mode

18.Movies with rental rate 4.99 and replacement cost > 20

SELECT * FROM film WHERE rental_rate = 4.99 AND replacement_cost > 20;
Enter fullscreen mode Exit fullscreen mode

19.Movies with rental rate 0.99 OR rating 'PG-13'

SELECT * FROM film WHERE rental_rate = 0.99 OR rating = 'PG-13';
Enter fullscreen mode Exit fullscreen mode

20.Movies with rating in ('G', 'PG', 'PG-13')

SELECT * FROM film WHERE rating IN ('G', 'PG', 'PG-13');
Enter fullscreen mode Exit fullscreen mode

21.Movies with rental rate between $2 and $4

SELECT * FROM film WHERE rental_rate BETWEEN 2 AND 4;
Enter fullscreen mode Exit fullscreen mode

22.Movies where special_features is NULL

SELECT * FROM film WHERE special_features IS NULL;
Enter fullscreen mode Exit fullscreen mode

23.Customers without email

SELECT * FROM customer WHERE email IS NULL;
Enter fullscreen mode Exit fullscreen mode

24.Movies with rental duration > 7 days

SELECT * FROM film WHERE rental_duration > 7;
Enter fullscreen mode Exit fullscreen mode

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;
Enter fullscreen mode Exit fullscreen mode

26.Titles containing % symbol

SELECT * FROM film WHERE title LIKE '%\%%' ESCAPE '\';
Enter fullscreen mode Exit fullscreen mode

27.Titles containing underscore _

SELECT * FROM film WHERE title LIKE '%\_%' ESCAPE '\';
Enter fullscreen mode Exit fullscreen mode

28.Titles containing digits

SELECT * FROM film WHERE title REGEXP '[0-9]';
Enter fullscreen mode Exit fullscreen mode

29.Titles containing backslash ()

SELECT * FROM film WHERE title LIKE '%\\\\%';
Enter fullscreen mode Exit fullscreen mode

30.10 customers after skipping 20 (sorted by last name)

SELECT * FROM customer ORDER BY last_name ASC LIMIT 10 OFFSET 20;
Enter fullscreen mode Exit fullscreen mode

What I Learned

  • How to filter data using WHERE, AND, OR
  • Sorting with ORDER BY
  • Pagination(Page navigation) using LIMIT and OFFSET
  • Pattern matching with LIKE and REGEXP
  • Handling missing data using NULL

Top comments (0)