CA 31 - Select Queries from DVD Rental database

SQL SELECT Practice Queries

1. Retrieve film titles and rental rates with aliases

select title as "movie title",rental_rates as "rate"from film;

2. List customer names and email with aliases

select first_name as "first name",last_name as "last name",email from customer;

3. Films sorted by rental rate desc, then title asc

select title, rental_rate from film order by title asc, rental_rate dsc;

4. Actor names sorted by last name then first name

select last_name, first_name from actor order by last_name asc, first_name asc;

5. Unique replacement costs

select distinct replacement_cost from film;

6. Film title and duration

select title,length as "duration(min)"from film;

7. Customer active status with alias

select first_name,last_name,active is "is active" from customer;

8. Film categories alphabetically

select name from category order by name;

9. Films sorted by length descending

select title, length from film order by length desc;

10. Actors sorted by first name descending

select first_name, last_name from actor order by first_name desc;

11. Unique ratings

select distinct rating from film;

12. Unique rental duration

select distinct rental_duration from film;

13. Unique customer id based on active status

select distinct customer_id, active from customer order by customer_id;

14. Earliest rental date for each customer

select customer_id, min(rental_date) as rental_date from rental group by customer_id order by customer_id;

15. list 10 shortest films

select title, length from film order by length asc limit 10;

16. Top 5 customers with highest id

select first_name, last_name from customer order by customer_id desc limit 5;

17. Unique store ids

select distinct store_id from inventory;

18. Unique replacement cost sorted

select distinct replacement_cost from film order by replacement_cost;

19. First rental date for each store

select i.store_id,min(r.rental_date) as 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;

20. Unique ratings sorted

select distinct rating from filmorder by rating;

21. Films sorted by rating asc and length desc

select title, rating, length from film order by rating asc, length desc;

22. Actors sorted by last name asc, first name desc

select first_name, last_name from actor order by last_name asc, first_name desc;

23. Films by replacement cost asc and rental rate desc

select title, replacement_cost, rental_rate from film order by replacement_cost asc, rental_rate desc;

24. Customers sorted by last name asc and first name desc

select first_name, last_name from customer order by last_name asc, first_name desc;

25. Rentals sorted by customer and date

select * from rental order by customer_id asc, rental_date desc;

26. Films by rental duration and title

select title, rental_duration from film order by rental_duration asc, title desc;