Introductory SQL: filtering, joins, and aggregation over the Sakila rental database.
Answer each of the 8 questions below with a single SQL query using the Sakila database. Every table reference must be schema-qualified (e.g., sakila.film) and every column reference must be table-qualified (e.g., film.title) — do not use aliases or bare column names. Submit a single .sql file named Lastname_Firstname_SQL_Assignment.sql with your queries clearly numbered using comments (e.g., -- Question 1). Queries that do not run or produce incorrect results will receive partial or no credit depending on the rubric.
Sakila Video is a growing DVD rental company operating across multiple store locations. Management needs data-driven insights to understand which films are most profitable, which customers are most valuable, and how inventory is performing across stores. As a junior data analyst, you have been tasked with writing SQL queries against the company's operational database to answer key business questions.
The Sakila database models a video rental business. The film table contains movie details (title, rating, rental rate, length). Films are linked to actor via film_actor, and to category via film_category. Inventory tracks physical copies per store. Customers rent inventory items (rental table) and payments are recorded in payment. Staff and store tables represent the physical locations.
1. Retrieve the title, rental_rate, and rating of all films that have a rental rate greater than $3.00. Order the results by rental_rate in descending order. (8 pts)
SELECT film.title, film.rental_rate, film.rating FROM sakila.film WHERE film.rental_rate > 3.00 ORDER BY film.rental_rate DESC;2. Find the first name, last name, and email of all customers whose last name starts with the letter 'S'. Order the results alphabetically by last name, then by first name. (8 pts)
SELECT customer.first_name, customer.last_name, customer.email FROM sakila.customer WHERE customer.last_name LIKE 'S%' ORDER BY customer.last_name ASC, customer.first_name ASC;3. List the title, length, and replacement_cost of all films that are longer than 120 minutes and have a replacement cost less than $20.00. Order the results by length in ascending order. (8 pts)
SELECT film.title, film.length, film.replacement_cost FROM sakila.film WHERE film.length > 120 AND film.replacement_cost < 20.00 ORDER BY film.length ASC;4. Retrieve the first name, last name, and email of all active staff members (where active = 1). Order the results by last name alphabetically. (8 pts)
SELECT staff.first_name, staff.last_name, staff.email FROM sakila.staff WHERE staff.active = 1 ORDER BY staff.last_name ASC;5. For each film category, find the category name and the total number of films in that category. Order the results by the total number of films in descending order. (12 pts)
Hint: Join the category and film_category tables, then group by category to count the films in each one.
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;6. List each customer's full name (first and last) along with the total amount they have paid across all their payments. Only include customers who have made at least one payment. Order the results by total amount paid in descending order, showing the top 10 customers. (14 pts)
Hint: Join customer and payment tables, then use an aggregate function on the payment amount grouped by customer.
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;7. Find the actor's first name, last name, and the total number of films they have appeared in. Only include actors who have appeared in more than 30 films. Order the results by the number of films in descending order. (14 pts)
Hint: After grouping by actor and counting their films, use HAVING to filter out actors below the threshold.
SELECT actor.first_name, actor.last_name, COUNT(film_actor.film_id) AS total_films FROM sakila.actor JOIN sakila.film_actor ON actor.actor_id = film_actor.actor_id GROUP BY actor.actor_id, actor.first_name, actor.last_name HAVING COUNT(film_actor.film_id) > 30 ORDER BY total_films DESC;8. For each film category, calculate the average rental rate of films in that category and the total rental revenue generated (sum of all payment amounts) from films in that category. Only include categories where the total rental revenue exceeds $5,000. Display the category name, average rental rate (rounded to 2 decimal places), and total revenue, ordered by total revenue descending. (28 pts)
Hint: You will need to join several tables to connect categories all the way through to payments. Think about the path: category → film_category → film → inventory → rental → payment.
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;