-- Marcus Webb — Sakila Rentals

-- Question 1
SELECT film.title, film.rental_rate, film.rating FROM sakila.film WHERE film.rental_rate > 3.00;

-- Question 2
SELECT customer.first_name, customer.last_name, customer.email FROM sakila.customer ORDER BY customer.last_name ASC, customer.first_name ASC;

-- Question 3
SELECT title, length, replacement_cost FROM film WHERE length > 120 AND replacement_cost < 00 ORDER BY length ASC;

-- Question 4
SELECT staff.first_name, staff.last_name, staff.email FROM sakila.staff WHERE staff.active = 1 ORDER BY staff.last_name ASC;

-- Question 5
SELECT category.name, COUNT(film_category.film_id) AS total_films FROM sakila.category JOIN sakila.film_category ON category.category_id = film_category.category_id GROUP BY category.category_id, category.name ORDER BY total_films DESC;

-- Question 6
SELECT customer.first_name, customer.last_name, SUM(payment.amount) AS total_paid FROM sakila.customer JOIN sakila.payment ON customer.customer_id = payment.customer_id GROUP BY customer.customer_id, customer.first_name, customer.last_name ORDER BY total_paid DESC LIMIT 10;

-- Question 7
-- (not attempted)

-- Question 8
SELECT category.name, ROUND(AVG(film.rental_rate), 2) AS avg_rental_rate, SUM(payment.amount) AS total_revenue FROM sakila.category JOIN sakila.film_category ON category.category_id = film_category.category_id JOIN sakila.film ON film_category.film_id = film.film_id JOIN sakila.inventory ON film.film_id = inventory.film_id JOIN sakila.rental ON inventory.inventory_id = rental.inventory_id JOIN sakila.payment ON rental.rental_id = payment.rental_id GROUP BY category.category_id, category.name HAVING SUM(payment.amount) > 5000 ORDER BY total_revenue DESC;
