Lokeshwaran S

Posted on Mar 29

Filter Assignments - CA32

#beginners #database #sql #tutorial

My Thinking and Approach

Introduction

In this bonus task, I explored advanced filtering techniques in SQL using the dvdrental database. The focus was on using conditions like NULL checks, pattern matching, ranges, and logical operators.

This helped me understand how to extract specific and meaningful data from a database.

Problem Statement

Retrieve data using conditions
Use operators like AND, OR, BETWEEN, IN
Apply pattern matching using LIKE
Work with LIMIT and OFFSET

My Initial Thought

At first, I thought:

Filtering is just using WHERE clause
Conditions are simple

But I realized:

SQL provides many ways to filter data
Pattern matching is very powerful
Combining conditions gives precise results

Key Observation

NULL values require special handling
LIKE is used for pattern matching
BETWEEN is useful for ranges
LIMIT and OFFSET control output size

Solutions

1. Movies with NULL special features

SELECT *
FROM film
WHERE special_features IS NULL;

2. Movies with rental duration more than 7

SELECT *
FROM film
WHERE rental_duration > 7;

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

SELECT *
FROM film
WHERE rental_rate = 4.99 AND replacement_cost > 20;

4. Movies with rental rate 0.99 or rating PG-13

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

5. First 5 movies sorted by title

SELECT *
FROM film
ORDER BY title ASC
LIMIT 5;

6. Skip 10 rows and fetch next 3 by replacement cost

SELECT *
FROM film
ORDER BY replacement_cost DESC
OFFSET 10
LIMIT 3;

7. Movies with rating G, PG, or PG-13

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

8. Movies with rental rate between 2 and 4

SELECT *
FROM film
WHERE rental_rate BETWEEN 2 AND 4;

9. Movies starting with The

SELECT *
FROM film
WHERE title LIKE 'The%';

10. First 10 movies with multiple conditions

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

11. Movies containing % symbol

SELECT *
FROM film
WHERE title LIKE '%\%%' ESCAPE '\';

12. Movies containing underscore

SELECT *
FROM film
WHERE title LIKE '%\_%' ESCAPE '\';

13. Titles starting with A or B and ending with s

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

14. Movies containing Man, Men, or Woman

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

15. Movies containing digits

SELECT *
FROM film
WHERE title ~ '[0-9]';

16. Movies containing backslash

SELECT *
FROM film
WHERE title LIKE '%\\%';

17. Movies containing Love or Hate

SELECT *
FROM film
WHERE title LIKE '%Love%' OR title LIKE '%Hate%';

18. First 5 movies ending with er, or, ar

SELECT *
FROM film
WHERE title LIKE '%er' OR title LIKE '%or' OR title LIKE '%ar'
LIMIT 5;

Final Understanding

SQL filtering can be very precise using conditions
Pattern matching helps in text-based searches
Combining operators gives better control over results

Conclusion

This task helped me understand advanced SQL filtering techniques. It improved my ability to work with real-world data and extract meaningful information using different conditions.

DEV Community