MySQL: Examples of Sakil-based queries

Database schema.

The sakila database is a sample database if you choose it will be installed together with the MySQL community.

You can execute queries and observe the results using the application: https://wiki.ostrowski.net.pl/php_mysql/sakila.php

List of all films with length and rating

SELECT title, LENGTH, rating
FROM film;

Top 10 longest films

SELECT title, LENGTH
FROM film
ORDER BY LENGTH DESC
LIMIT 10;

Number of films in each category

SELECT c.name AS category, COUNT(*) AS film_count
FROM film_category fc
JOIN category c ON fc.category_id = c.category_id
GROUP BY c.name
ORDER BY film_count DESC;

Average length of film in each category

SELECT c.name AS category, AVG(f.length) AS avg_length
FROM film f
JOIN film_category fc ON f.film_id = fc.film_id
JOIN category c ON fc.category_id = c.category_id
GROUP BY c.name;

Actors who appeared in the most films

SELECT a.first_name, a.last_name, COUNT(*) AS film_count
FROM actor a
JOIN film_actor fa ON a.actor_id = fa.actor_id
GROUP BY a.actor_id
ORDER BY film_count DESC
LIMIT 10;

Films available in more than 1 language (if applicable)

SELECT f.title, l.name AS LANGUAGE
FROM film f
JOIN LANGUAGE l ON f.language_id = l.language_id;

List of films and names of actors in them

SELECT f.title, a.first_name, a.last_name
FROM film f
JOIN film_actor fa ON f.film_id = fa.film_id
JOIN actor a ON fa.actor_id = a.actor_id
ORDER BY f.title;

Customers who paid the most (Top 10)

SELECT c.first_name, c.last_name, SUM(p.amount) AS total_spent
FROM customer c
JOIN payment p ON c.customer_id = p.customer_id
GROUP BY c.customer_id
ORDER BY total_spent DESC
LIMIT 10;

Number of rentals of each film

SELECT f.title, COUNT(r.rental_id) AS rental_count
FROM film f
JOIN inventory i ON f.film_id = i.film_id
JOIN rental r ON i.inventory_id = r.inventory_id
GROUP BY f.film_id
ORDER BY rental_count DESC;

Most rented film

SELECT f.title
FROM film f
JOIN inventory i ON f.film_id = i.film_id
JOIN rental r ON i.inventory_id = r.inventory_id
GROUP BY f.film_id
ORDER BY COUNT(*) DESC
LIMIT 1;

Customers from the city of "Dallas"

SELECT c.first_name, c.last_name, a.address
FROM customer c
JOIN address a ON c.address_id = a.address_id
JOIN city ci ON a.city_id = ci.city_id
WHERE ci.city = 'Dallas';

List of categories of films whose average length exceeds 120 minutes

SELECT c.name AS category, AVG(f.length) AS avg_length
FROM film f
JOIN film_category fc ON f.film_id = fc.film_id
JOIN category c ON fc.category_id = c.category_id
GROUP BY c.name
HAVING AVG(f.length) > 120;

List of all clients with number of rentals

SELECT c.first_name, c.last_name, COUNT(r.rental_id) AS rentals
FROM customer c
JOIN rental r ON c.customer_id = r.customer_id
GROUP BY c.customer_id
ORDER BY rentals DESC;

Films with no rentals

SELECT f.title
FROM film f
LEFT JOIN inventory i ON f.film_id = i.film_id
LEFT JOIN rental r ON i.inventory_id = r.inventory_id
WHERE r.rental_id IS NULL;

Number of customers in each country

SELECT co.country, COUNT(*) AS customer_count
FROM customer cu
JOIN address a ON cu.address_id = a.address_id
JOIN city ci ON a.city_id = ci.city_id
JOIN country co ON ci.country_id = co.country_id
GROUP BY co.country
ORDER BY customer_count DESC;

Average duration of rental (in days)

SELECT AVG(DATEDIFF(return_date, rental_date)) AS avg_rental_duration
FROM rental
WHERE return_date IS NOT NULL;

Films containing the word "Action" in their description

SELECT title, description
FROM film
WHERE description LIKE '%Action%';

Films with the actor "PENELOPE GUINESS"

SELECT f.title
FROM film f
JOIN film_actor fa ON f.film_id = fa.film_id
JOIN actor a ON fa.actor_id = a.actor_id
WHERE a.first_name = 'PENELOPE' AND a.last_name = 'GUINESS';

List of customers who rented the film in 2006

SELECT DISTINCT c.first_name, c.last_name
FROM customer c
JOIN rental r ON c.customer_id = r.customer_id
WHERE YEAR(r.rental_date) = 2006;

Average number of rentals per customer

SELECT AVG(rental_count) AS avg_rentals_per_customer
FROM (
    SELECT customer_id, COUNT(*) AS rental_count
    FROM rental
    GROUP BY customer_id
) AS sub;