Filter Assignments

These are the basic building blocks used in SELECT queries to retrieve and filter data from a table. The FROM clause specifies which table the data is coming from, while the WHERE clause is used to apply conditions (constraints) to filter records before they are returned. Functions like COUNT(column_name) help count the total number of values in a column, and COUNT(DISTINCT column_name) is used to count only unique values. String functions such as LEFT(column_name, n) are useful for extracting characters from the beginning of a column value, often used in conditions to filter records based on starting letters or patterns. Together, these elements form the foundation of writing effective SQL queries for data retrieval and analysis.

1.Get all movies (films) that have a rental rate greater than $3.

SELECT title, rental_rate
FROM film
WHERE rental_rate > 3;

2.Get all movies that have a rental rate greater than $3 and a replacement cost less than $20.

SELECT title, rental_rate, replacement_cost
FROM film
WHERE rental_rate > 3 AND replacement_cost < 20;

3.Get all movies that are either rated as 'PG' or have a rental rate of $0.99.

SELECT title, rating, rental_rate
FROM film
WHERE rating = 'PG' OR rental_rate = 0.99;

4.Show the first 10 movies sorted by rental rate (highest first).

SELECT title, rental_rate
FROM film
ORDER BY rental_rate DESC
LIMIT 10;

5.Skip the first 5 movies and fetch the next 3 sorted by rental rate in ascending order.

SELECT title, rental_rate
FROM film
ORDER BY rental_rate ASC
OFFSET 5 LIMIT 3;

6.Get all movies with a rental duration between 3 and 7 days.

SELECT title, rental_duration
FROM film
WHERE rental_duration BETWEEN 3 AND 7;

7.Get all movies where the title starts with 'A' and ends with 'e'

SELECT title
FROM film
WHERE title LIKE 'A%e';

8.Find all customers who do not have an email address listed.

SELECT first_name, last_name
FROM customer
WHERE email IS NULL;

9.Find all movies released in 2006 with a rental rate of $2.99 or $3.99, and their title starts with 'S'. Display the top 5 results.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;

10.Display 10 customers after skipping the first 20, sorted alphabetically by last name.

SELECT first_name, last_name
FROM customer
ORDER BY last_name
LIMIT 10 OFFSET 20;

11.Get the top 5 movies with the highest replacement cost, skipping the most expensive one.

SELECT title, replacement_cost
FROM film
ORDER BY replacement_cost DESC
OFFSET 1 FETCH NEXT 5 ROWS ONLY;

12.Find all rentals that occurred between '2005-05-01' and '2005-06-01'.

SELECT rental_id, rental_date, customer_id
FROM rental
WHERE rental_date BETWEEN '2005-05-01' AND '2005-06-01';

13.Get all actors whose last names contain the letters "man".

SELECT first_name, last_name
FROM actor
WHERE last_name LIKE '%man%';

14.Find all movies where the special features are not listed (i.e.,
special_features is NULL).

SELECT title
FROM film
WHERE special_features IS NULL;

15.Find all movies where the rental duration is more than 7 days.

SELECT title, rental_duration
FROM film
WHERE rental_duration > 7;

16.Find the first 10 movies with a rental rate of $2.99 or $4.99, a rating of 'R', and a title containing the word "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;

17.Find all movies where the title starts with "A" or "B" and ends with "s".

SIMILAR TO is used in SQL for advanced pattern matching, especially in PostgreSQL. It is more powerful than LIKE because it allows multiple conditions using patterns like (A|B) for matching different options. While LIKE is good for simple searches, SIMILAR TO is useful when you need more flexible and complex text filtering in a single condition.

SELECT title
FROM film
WHERE title SIMILAR TO '(A|B)%s';

18.Find all movies where the title contains "Man", "Men", or "Woman".

SELECT title
FROM film
WHERE title SIMILAR TO '%(Man|Men|Woman)%';