Saranya R

Posted on Mar 24

Select Queries from DVD Rental database Pt-3

#sql #postgres #tutorial

13) Retrieve the first unique customer ID based on active status. Include the customer_id and active columns, and order by customer_id.

cmd: SELECT DISTINCT customer_id, active FROM customer ORDER BY customer_id;

SAMPLE OP:
customer_id | active
-------------+--------
1 | 1
2 | 1
3 | 1
4 | 1
5 | 1
6 | 1
7 | 1

14) List the earliest rental date for each customer. Include customer_id and rental_date, and order by customer_id.

cmd: SELECT customer_id, MIN(rental_date) AS rental_date FROM rental GROUP BY customer_id ORDER BY customer_id;

SAMPLE OP:
customer_id | rental_date
-------------+---------------------
1 | 2005-05-25 11:30:37
2 | 2005-05-27 00:09:24
3 | 2005-05-27 17:17:09
4 | 2005-06-15 09:31:28
5 | 2005-05-29 07:25:16
6 | 2005-05-25 08:43:32
7 | 2005-05-25 06:04:08

15) List the 10 shortest films by length. Include the title and length.

cmd: SELECT title, length FROM film ORDER BY length ASC LIMIT 10;

16) Get the top 5 customers with the highest customer_id. Include the first and last name.

cmd: SELECT first_name, last_name FROM customer ORDER BY customer_id DESC LIMIT 5;

17) Retrieve all unique values of store_id from the inventory table.

cmd: SELECT DISTINCT store_id FROM inventory;

SAMPLE OP:

store_id

    1
    2

18) Find all unique replacement_cost values in the film table. Sort the results in ascending order.

cmd: SELECT DISTINCT replacement_cost FROM film ORDER BY replacement_cost ASC;

SAMPLE OP:

replacement_cost

19) List the first rental date for each store. Include store_id and rental_date, and sort by store_id.

cmd: 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;

SAMPLE OP:
store_id | min
----------+---------------------
1 | 2005-05-24 22:53:30
2 | 2005-05-24 22:54:33

20) Retrieve a list of film ratings sorted alphabetically and include only unique values.

cmd: SELECT DISTINCT rating FROM film ORDER BY rating ASC;

SAMPLE OP:

rating

G
PG
PG-13
R
NC-17

21) List films by rating in ascending order and length in descending order.

cmd: SELECT title, rating, length FROM film ORDER BY rating ASC, length DESC;

SAMPLE OP:
title | rating | length
-----------------------------+--------+--------
Control Anthem | G | 185
Darn Forrester | G | 185
Muscle Bright | G | 185
Moonwalker Fool | G | 184
Catch Amistad | G | 183
Young Language | G | 183

22) Retrieve actor names sorted by last_name in ascending order and first_name in descending order.

cmd: SELECT first_name, last_name FROM actor ORDER BY last_name ASC, first_name DESC;

23) List films ordered by replacement_cost in ascending order and rental_rate in descending order.

cmd: SELECT title, replacement_cost, rental_rate FROM film ORDER BY replacement_cost ASC, rental_rate DESC;

SAMPLE OP:
title | replacement_cost | rental_rate
-----------------------------+------------------+-------------
Dude Blindness | 9.99 | 4.99
Paths Control | 9.99 | 4.99
Daisy Menagerie | 9.99 | 4.99
Opposite Necklace | 9.99 | 4.99
Heartbreakers Bright | 9.99 | 4.99
Control Anthem | 9.99 | 4.99
Truman Crazy | 9.99 | 4.99

24) Retrieve customer names sorted by last_name ascending and first_name descending.

cmd: SELECT first_name, last_name FROM customer ORDER BY last_name ASC, first_name DESC;

25) List all rentals sorted by customer_id ascending and rental_date descending.

cmd: SELECT first_name, last_name FROM customer ORDER BY last_name ASC, first_name DESC;

26) Retrieve a list of films ordered by rental_duration ascending and title descending.

cmd: SELECT title, rental_duration FROM film ORDER BY rental_duration ASC, title DESC;

DEV Community

Select Queries from DVD Rental database Pt-3

store_id

replacement_cost

rating

Top comments (0)