Let me explain it with some "DVD Rental DB" qns
QN: 1
Retrieve film titles and their rental rates.
ANS: SELECT title AS "Movie Title", rental_rate AS "Rate" FROM film;
it is to select title and rental_rate from table film and we are renaming them as Movie Title and Rate using alias.
QN: 2
List customer names and their email addresses.
ANS: SELECT first_name AS "First Name", last_name AS "Last Name", email FROM customer;
since, we need first name, last name and email from customer table and we rename first_name and last_name.
QN: 3
Sort films by rental rate descending and title.
ANS: SELECT title, rental_rate FROM film ORDER BY rental_rate DESC, title ASC;
since, it is to sort rental_rate in descending if same means then title will be sorted alphabetically.
QN: 4
Actor names sorted by last name then first name.
ANS: SELECT first_name, last_name FROM actor ORDER BY last_name, first_name;
sorting is done first by last_name and then by first_name.
QN: 5
Unique replacement costs.
ANS: SELECT DISTINCT replacement_cost FROM film;
it is to get only unique values so we use DISTINCT.
QN: 6
Film title and length.
ANS: SELECT title, length AS "Duration (min)" FROM film;
select title and length and rename length as Duration.
QN: 7
Customer with active status.
ANS: SELECT first_name, last_name, active AS "Is Active" FROM customer;
we take name and active column and rename active.
QN: 8
Film categories sorted.
ANS: SELECT name FROM category ORDER BY name;
we select category names and sort alphabetically.
QN: 9
Films by length descending
ANS: SELECT title, length FROM film ORDER BY length DESC;
sorting is from highest length to lowest.
QN: 10
Actors by first name descending.
ANS: SELECT first_name, last_name FROM actor ORDER BY first_name DESC;
sorting is based on first_name in reverse.
QN: 11
Unique ratings.
ANS: SELECT DISTINCT rating FROM film;only unique ratings are needed.
QN: 12
Unique rental durations.
ANS: SELECT DISTINCT rental_duration FROM film;
we need only distinct rental_duration.
QN: 13
First customer_id based on active.
ANS: SELECT DISTINCT ON (active) customer_id, active FROM customer ORDER BY active, customer_id;
for each active value we take first customer_id.
QN: 14
Earliest rental date per customer.
ANS: SELECT customer_id, MIN(rental_date) FROM rental GROUP BY customer_id ORDER BY customer_id;
MIN is used to find earliest date for each customer.
QN: 15
10 shortest films.
ANS: SELECT title, length FROM film ORDER BY length ASC LIMIT 10;
sorting is from smallest length and limiting to 10.
QN: 16
Top 5 customers with highest id.
ANS: SELECT first_name, last_name, customer_id FROM customer ORDER BY customer_id DESC LIMIT 5;
highest customer_id will come first.
QN: 17
Unique store ids.
ANS: SELECT DISTINCT store_id FROM inventory;
DISTINCT removes duplicates.
QN: 18
Unique replacement cost sorted.
ANS: SELECT DISTINCT replacement_cost FROM film ORDER BY replacement_cost ASC;
values are sorted from lowest to highest.
QN: 19
First rental date per store.
ANS: SELECT i.store_id, MIN(r.rental_date) FROM rental r JOIN inventory i ON r.inventory_id = i.inventory_id GROUP BY i.store_id ORDER BY i.store_id;
we join tables and get earliest rental date per store.
QN: 20
Unique ratings sorted.
ANS: SELECT DISTINCT rating FROM film ORDER BY rating;
ratings are sorted alphabetically.
QN: 21
Films by rating and length.
ANS: SELECT title, rating, length FROM film ORDER BY rating ASC, length DESC;
first rating is sorted then length is sorted.
QN: 22
Actors sorted last and first name.
ANS: SELECT first_name, last_name FROM actor ORDER BY last_name ASC, first_name DESC;
last_name is ascending and first_name is descending.
QN: 23
Films by replacement and rate.
ANS: SELECT title, replacement_cost, rental_rate FROM film ORDER BY replacement_cost ASC, rental_rate DESC;
first replacement_cost is sortedthen rental_rate.
QN: 24
Customers sorted.
ANS: SELECT first_name, last_name FROM customer ORDER BY last_name ASC, first_name DESC;
last_name ascending and first_name descending.
QN: 25
Rentals sorted.
ANS: SELECT * FROM rental ORDER BY customer_id ASC, rental_date DESC;
sorted by customer_id and latest rental first.
QN: 26
Films by duration and title.
ANS: SELECT title, rental_duration FROM film ORDER BY rental_duration ASC, title DESC;
duration is sorted first then title in reverse order.
Top comments (0)