Qn 1: Get all movies (films) that have a rental rate greater than $3.
Just filter rows where rental rate is greater than 3.
SELECT * FROM film WHERE rental_rate > 3;
Qn 2: Get all movies that have a rental rate greater than $3 and a replacement cost less than $20.
Apply two conditions
SELECT * FROM film WHERE rental_rate > 3 AND replacement_cost < 20;
Qn 3: Get all movies that are either rated as 'PG' or have a rental rate of $0.99.
Same as previous qn apply two condition and check for both.
SELECT * FROM film WHERE rating = 'PG' OR rental_rate = 0.99;
Qn 4: Show the first 10 movies sorted by rental rate (highest first).
Using order by clause I learnt last class and using desc to show the highest value first.
SELECT * FROM film ORDER BY rental_rate DESC LIMIT 10;
Qn 5: Skip the first 5 movies and fetch the next 3 sorted by rental rate in ascending order.
offset 5 is used to not show the first few valuees.
SELECT * FROM film ORDER BY rental_rate ASC OFFSET 5 LIMIT 3;
Qn 6: Skip the first 5 movies and fetch the next 3 sorted by rental rate in ascending order.
Similar to last qn.
SELECT * FROM film ORDER BY rental_rate ASC OFFSET 5 LIMIT 3;
Qn 7: Get all movies with a rental duration between 3 and 7 days.
using between clause to get the desired output.
SELECT * FROM film WHERE rental_duration BETWEEN 3 AND 7;
Qn 8: Get all movies where the title starts with 'A' and ends with 'e'.
pattern matching thing works like this: 'A%' means starts with A. '%e' means ends with e. Combine them both.
SELECT * FROM film WHERE title LIKE 'A%e';
Qn 9: Find all customers who do not have an email address listed.
emall not listed means it will be null.
SELECT * FROM customer WHERE email IS NULL;
Qn 10: 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.
this was fun to solve. mixing the smaller queries i already tried to come up with this.
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;
Qn 11: Display 10 customers after skipping the first 20, sorted alphabetically by last name.
as seen earlier we use the offset clause to skip the first 20 values.
SELECT * FROM customer ORDER BY last_name ASC OFFSET 20 LIMIT 10;
Qn 12: Get the top 5 movies with the highest replacement cost, skipping the most expensive one.
highest cost means we need the list in reverse or descending order. for skipping most expensive use offset.
SELECT * FROM film ORDER BY replacement_cost DESC OFFSET 1 LIMIT 5;
Qn 13: Find all rentals that occurred between '2005-05-01' and '2005-06-01'.
simple between clause does the job.
SELECT * FROM rental WHERE rental_date BETWEEN '2005-05-01' AND '2005-06-01';
Qn 14: Get all actors whose last names contain the letters "man".
pattern matching with "man"
SELECT * FROM actor WHERE last_name LIKE '%man%';
Qn 15: Find all movies where the special features are not listed (i.e., special_features is NULL).
get the rows where value is null for special_features
SELECT * FROM film WHERE special_features IS NULL;
Qn 16: Find all movies where the rental duration is more than 7 days.
Filter where rental duration is greater than 7.
SELECT * FROM film WHERE rental_duration > 7;
Qn 17: 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".
Combine multiple filters and search for a letter inside title.
SELECT * FROM film WHERE rental_rate IN (2.99, 4.99) AND rating = 'R' AND title LIKE '%L%' LIMIT 10;
Qn 18: Find all movies where the title starts with "A" or "B" and ends with "s".
Use OR for starting letters and pattern for ending letter.
SELECT * FROM film WHERE (title LIKE 'A%s' OR title LIKE 'B%s');
Qn 19: Find all movies where the title contains "Man", "Men", or "Woman".
Check if title contains any of the given words using OR.
SELECT * FROM film WHERE title LIKE '%Man%' OR title LIKE '%Men%' OR title LIKE '%Woman%';
Qn 20: Find all movies where the special features are not listed (i.e., special_features is NULL).
SELECT * FROM film WHERE special_features IS NULL;
Qn 21: Find all movies where the rental duration is more than 7 days.
SELECT * FROM film WHERE rental_duration > 7;
Qn 22: Find all movies that have a rental rate of $4.99 and a replacement cost of more than $20.
Apply AND condition for both rental rate and replacement cost.
SELECT * FROM film WHERE rental_rate = 4.99 AND replacement_cost > 20;
Qn 23: Find all movies that have a rental rate of $0.99 or a rating of 'PG-13'.
Use OR since either rental rate or rating condition works.
SELECT * FROM film WHERE rental_rate = 0.99 OR rating = 'PG-13';
Qn 24: Retrieve the first 5 rows of movies sorted alphabetically by title.
Sort alphabetically and take first 5 rows.
SELECT * FROM film ORDER BY title ASC LIMIT 5;
Qn 25: Skip the first 10 rows and fetch the next 3 movies with the highest replacement cost.
Sort by replacement cost descending, skip 10, take next 3.
SELECT * FROM film ORDER BY replacement_cost DESC OFFSET 10 LIMIT 3;
Qn 26: Find all movies where the rating is either 'G', 'PG', or 'PG-13'.
SELECT * FROM film WHERE rating IN ('G', 'PG', 'PG-13');
Qn 27: Find all movies with a rental rate between $2 and $4.
SELECT * FROM film WHERE rental_rate BETWEEN 2 AND 4;
Qn 28: Find all movies with titles that start with 'The'.
Use LIKE to match titles starting with “The”.
SELECT * FROM film WHERE title LIKE 'The%';
Qn 29: 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 "Love".
Combine filters and search for specific word “Love” in title.
SELECT * FROM film WHERE rental_rate IN (2.99, 4.99) AND rating = 'R' AND title LIKE '%Love%' LIMIT 10;
Qn 30: Find all movies where the title contains the % symbol.
Escape special character % and search in title.
SELECT * FROM film WHERE title LIKE '%\%%' ESCAPE '\';
Qn 31: Find all movies where the title contains an underscore (_).
SELECT * FROM film WHERE title LIKE '%\_%' ESCAPE '\';
Qn 32: Find all movies where the title starts with "A" or "B" and ends with "s".
SELECT * FROM film WHERE (title LIKE 'A%s' OR title LIKE 'B%s');
Qn 33: Find all movies where the title contains "Man", "Men", or "Woman".
Use OR with LIKE to match multiple words.
SELECT * FROM film WHERE title LIKE '%Man%' OR title LIKE '%Men%' OR title LIKE '%Woman%';
Qn 34: Find all movies with titles that contain digits (e.g., "007", "2", "300").
Use regex to check if title contains digits.
SELECT * FROM film WHERE title ~ '[0-9]';
Qn 35: Find all movies with titles containing a backslash ().
SELECT * FROM film WHERE title LIKE '%\\%';
Qn 36: Find all movies where the title does contain the words "Love" or "Hate".
Use OR to check if title contains either word.
SELECT * FROM film WHERE title LIKE '%Love%' OR title LIKE '%Hate%';
Qn 37: Find the first 5 movies with titles that end with "er", "or", or "ar".
Match titles ending with specific suffixes and limit results.
SELECT * FROM film WHERE title LIKE '%er' OR title LIKE '%or' OR title LIKE '%ar' LIMIT 5;
Top comments (0)