DB- TASK 2
Bonus Q/A
- Find all movies where the special features are not listed (i.e., special_features is NULL).
cmd:
SELECT title FROM film WHERE special_features IS NULL;
sample op:
title
Academy Dinosaur
Ace Goldfinger
Adaptation Holes
Affair Prejudice
African Egg
2) Find all movies where the rental duration is more than 7 days.
cmd:
SELECT title, rental_duration
FROM film
WHERE rental_duration > 7;
sample op:
title | rental_duration
---------------------+-----------------
Alamo Videotape | 8
Brotherhood Blanket | 9
Chicago North | 10
Dragon Squad | 8
3) Find all movies that have a rental rate of $4.99 and a replacement cost of more than $20.
cmd:
SELECT title, rental_rate, replacement_cost FROM film WHERE rental_rate = 4.99 AND replacement_cost > 20;
sample op:
title | rental_rate | replacement_cost
--------------------+-------------+------------------
Ace Goldfinger | 4.99 | 22.99
Airport Pollock | 4.99 | 24.99
Bright Encounters | 4.99 | 21.99
4) Find all movies that have a rental rate of $0.99 or a rating of 'PG-13'.
cmd:
SELECT title, rental_rate, rating FROM film WHERE rental_rate = 0.99 OR rating = 'PG-13';
sample op:
title | rental_rate | rating
-------------------+-------------+--------
Academy Dinosaur | 0.99 | PG
Alien Center | 2.99 | PG-13
Angels Life | 0.99 | PG-13
5) Retrieve the first 5 rows of movies sorted alphabetically by title.
cmd:
SELECT title FROM film ORDER BY title ASC LIMIT 5;
sample op:
title
Academy Dinosaur
Ace Goldfinger
Adaptation Holes
Affair Prejudice
African Egg
6) Skip the first 10 rows and fetch the next 3 movies with the highest replacement cost.
cmd:
SELECT title, replacement_cost
FROM film
ORDER BY replacement_cost DESC
LIMIT 3 OFFSET 10;
sample op:
title | replacement_cost
-------------------+------------------
Anthem Luke | 24.99
Apollo Teen | 24.99
Arabia Dogma | 24.99
7) Find all movies where the rating is either 'G', 'PG', or 'PG-13'.
cmd:
SELECT title, rating FROM film WHERE rating IN ('G', 'PG', 'PG-13');
sample op:
title | rating
-------------------+--------
Academy Dinosaur | PG
Ace Goldfinger | G
Alien Center | PG-13
8) Find all movies with a rental rate between $2 and $4.
cmd:
SELECT title, rental_rate FROM film WHERE rental_rate BETWEEN 2 AND 4;
sample op:
title | rental_rate
-------------------+-------------
Adaptation Holes | 2.99
Alien Center | 2.99
Apollo Teen | 3.99
9) Find all movies with titles that start with 'The'.
cmd:
SELECT title FROM film WHERE title LIKE 'The%';
sample op:
title
The Matrix
The Pianist
The Others
The Truman Show
10) 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".
cmd:
SELECT title, rental_rate, rating
FROM film
WHERE rental_rate IN (2.99, 4.99)
AND rating = 'R'
AND title LIKE '%Love%'
LIMIT 10;
sample op:
title | rental_rate | rating
-----------------+-------------+--------
Crazy Love | 2.99 | R
Dangerous Love | 4.99 | R
Endless Love | 2.99 | R
11) Find all movies where the title contains the % symbol.
cmd:
SELECT title FROM film WHERE title LIKE '%\%%' ESCAPE '\';
sample op:
title
100% Love
50% Chance
12) Find all movies where the title contains an underscore (_).
cmd:
SELECT title FROM film WHERE title LIKE '%_%' ESCAPE '\';
sample op:
title
Mission_Impossible
Fast_Furious
13) Find all movies where the title starts with "A" or "B" and ends with "s".
cmd:
SELECT title FROM film WHERE (title LIKE 'A%' OR title LIKE 'B%') AND title LIKE '%s';
sample op:
title
Angels Life
Backwards Towns
Brothers Dreams
14) Find all movies where the title contains "Man", "Men", or "Woman".
cmd:
SELECT title FROM film WHERE title LIKE '%Man%' OR title LIKE '%Men%' OR title LIKE '%Woman%';
sample op:
title
Spider Man
X Men United
Wonder Woman
15) Find all movies with titles that contain digits (e.g., "007", "2", "300").
cmd:
SELECT title FROM film WHERE title ~ '[0-9]';
sample op:
title
007 Bond
300 Spartans
2 Fast 2 Furious
16) Find all movies with titles containing a backslash ().
cmd:
SELECT title FROM film WHERE title LIKE '%\%';
sample op:
title
Escape \ Reality
Path \ Finder
17) Find all movies where the title does contain the words "Love" or "Hate".
cmd:
SELECT title FROM film WHERE title LIKE '%Love%' OR title LIKE '%Hate%';
sample op:
title
Crazy Love
Endless Love
Hate Story
Love Actually
18) Find the first 5 movies with titles that end with "er", "or", or "ar".
cmd:
SELECT title
FROM film
WHERE title LIKE '%er'
OR title LIKE '%or'
OR title LIKE '%ar'
LIMIT 5;
sample op:
title
Joker
Creator
Avatar
Doctor
Warrior
Top comments (0)