~~NOTOC~~
====== MySQL: Przykłady zapytań na bazie Sakila ======

{{:notatki:pasted:20250513-160056.png?900}}\\
Schemat bazy danych.

Baza sakila jest to przykładowa baza jeżeli wybierzemy to będzie instalowana razem ze środowiskiem MySQL community.

Zapytania można wykonać i zaobserwować wyniki za pomocą aplikacji: https://wiki.ostrowski.net.pl/php_mysql/sakila.php

===== Lista wszystkich filmów z długością i oceną =====

<code sql>
SELECT title, length, rating
FROM film;
</code>
===== Top 10 najdłuższych filmów =====

<code sql>
SELECT title, length
FROM film
ORDER BY length DESC
LIMIT 10;
</code>
===== Liczba filmów w każdej kategorii =====

<code sql>
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;
</code>
===== Średnia długość filmu w każdej kategorii =====

<code sql>
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;
</code>
===== Aktorzy, którzy wystąpili w największej liczbie filmów =====

<code sql>
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;
</code>
===== Filmy dostępne w więcej niż 1 języku (jeśli dotyczy) =====

<code sql>
SELECT f.title, l.name AS language
FROM film f
JOIN language l ON f.language_id = l.language_id;
</code>
===== Lista filmów i nazwisk aktorów w nich grających =====

<code sql>
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;
</code>
===== Klienci, którzy zapłacili najwięcej (Top 10) =====

<code sql>
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;
</code>
===== Ilość wypożyczeń każdego filmu =====

<code sql>
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;
</code>
===== Najczęściej wypożyczany film =====

<code sql>
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;
</code>
===== Klienci z miasta "Dallas" =====

<code sql>
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';
</code>
===== Lista kategorii filmów, których średnia długość przekracza 120 minut =====

<code sql>
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;
</code>
===== Lista wszystkich klientów z liczbą ich wypożyczeń =====

<code sql>
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;
</code>
===== Filmy bez wypożyczeń =====

<code sql>
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;
</code>
===== Liczba klientów w każdym kraju =====

<code sql>
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;
</code>
===== Średni czas trwania wypożyczenia (w dniach) =====

<code sql>
SELECT AVG(DATEDIFF(return_date, rental_date)) AS avg_rental_duration
FROM rental
WHERE return_date IS NOT NULL;
</code>
===== Filmy zawierające słowo "Action" w opisie =====

<code sql>
SELECT title, description
FROM film
WHERE description LIKE '%Action%';
</code>
===== Filmy z aktorem "PENELOPE GUINESS" =====

<code sql>
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';
</code>
===== Lista klientów, którzy wypożyczyli film w 2006 roku =====

<code sql>
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;
</code>
===== Średnia liczba wypożyczeń per klient =====

<code sql>
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;
</code>