CA 31 - Select Queries from DVD Rental database
MohithSQL SELECT Practice Queries 1. Retrieve film titles and rental rates with...
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;