Bazując na bazie testowej Sakila (tutaj zip), wykonaj następujące ćwiczenia. Ułatwieniem może być widok schematu relacji tej bazy. Data ostatniej aktualizacji list: 07.10.2024.

Lista 1: zapytania na jednej tabeli

1. Podaj tytuły, opisy, czas wypożyczenia, koszt wypożyczenia za dzień i całkowity koszt wypożyczenia (jako iloczyn czasu wypożyczenia i jego kosztu) dla każdego filmu. Przyjmij, że pole rental_rate zawiera koszt wypożyczenia za dzień (według dokumentacji bazy jest to koszt wypożyczenia na tyle dni, ile zapisano w polu rental_duration).
   • Wersja zgodna z dokumentacją. Podaj tytuły, opisy, czas wypożyczenia oraz koszt wypożyczenia za dzień (jako iloraz kosztu wypożyczenia na przewidzianą liczbę dni) dla każdego filmu.
2. Podaj alfabetyczną listę nazwisk aktorów, każde nazwisko jeden raz.
   
   Oczekiwana odpowiedź

   121 wierszy, pierwsze 3 rekordy to AKROYD, ALLEN oraz ASTAIRE.
3. Wypisz wszystkie rodzaje ratingów filmów (każdy rating jednokrotnie).
   
   Oczekiwana odpowiedź

   Jest ich 5: G, PG, NC-13, PG-17, R.
4. Wypisz tytuły, opisy, ratingi i długość filmów, które trwają 3 godziny lub dłużej.
   1. Zmodyfikuj powyższe zapytanie tak, aby czas był wyświetlany w godzinach (np. 75 minut = 1,25 godz.).
   2. Zmodyfikuj powyższe zapytanie tak, aby czas był wyświetlany w godzinach z dokładnością do drugiego miejsca po przecinku (np. 75 minut = 1,25 godz.).
   3. Zmodyfikuj powyższe zapytanie tak, aby kolumna z długością filmu w godzinach miała nazwę (alias) length_hours.
5. Wypisz wszystkie informacje z tabeli customer o tych klientach, którzy są nieaktywni albo ich nazwisko kończy się na M.
   
   O co chodzi z albo?

   Spójnik OR oznacza lub, zaś albo to spójnik XOR. Różnica polega na tym, że jeśli dwa zdania, p i q, łączymy tymi spójnikami, to OR zwróci 1, gdy p i q są prawdziwe, a XOR zwróci w tej sytuacji 0.
6. Wypisz wszystkie kategorie, których identyfikator jest większy niż 4, a których nazwa zaczyna się na C, S lub T. Użyj klauzuli WHERE.
   • Wykonaj powyższe ćwiczenie, używając operatora IN do określenia zbioru liter C, S, T.
     Uwaga. Trzeba użyć pewnej funkcji na tekstach.
7. Wypisz wszystkie filmy z ratingiem G, PG-13 lub NC-17. Użyj operatora IN.
8. Wypisz listę otwartych wypożyczeń (czyli wypożyczono film, ale jeszcze go nie zwrócono).
9. Wypisz wypożyczenia, które miały miejsce między 25 a 26 maja 2005 r. Skorzystaj z operatora BETWEEN. Zwróć uwagę, że w polu rental_date przechowywana jest zarówno data, jak i godzina (typ danych to DATETIME).
10. Wypisz listę dziesięciu ostatnio wypożyczonych filmów.

Lista 2: funkcje agregujące

Poniższe zadania używają funkcji agregujących.

1. Ile jest aktorów, których pierwsze imię to 'Scarlett'?
   
   Oczekiwana odpowiedź

   2
2. Które nazwiska aktorów występują więcej niż raz? Podaj je, sortując od najczęstszych do najrzadszych.
   
   Oczekiwana odpowiedź

   Jest 55 takich nazwisk, pierwsze 5: KILMER - 5, TEMPLE - 4, NOLTE - 4, AKROYD - 3, WILLIS - 3.
   Ostatnie 2 mogą być inne - jest więcej niż 2 aktorów, którzy grali w dokładnie 3 filmach, więc kolejność może być losowa (posortuj, aby nie była).
3. Podaj ID 5 pierwszych aktorów, którzy wystąpili w największej liczbie filmów.
   
   Oczekiwana odpowiedź

   107, 102, 198, 181, 23.
4. Policz, ile jest klientów przypisanych do swoich "ulubionych sklepów". Podaj ID sklepu oraz liczbę takich klientów.
   
   Oczekiwana odpowiedź

   Sklep 1 - 326 klientów, sklep 2 - 273 klientów.
5. Rozbuduj powyższe zapytanie, by pokazać, ilu jest klientów aktywnych, a ilu nie w każdym ze sklepów.
   
   Oczekiwana odpowiedź

   Sklep 1 - 8 klientów nieaktywnych, 318 klientów aktywnych; sklep 2 - 7 klientów nieaktywnych, 266 klientów aktywnych.
6. Podaj 10 klientów, którzy wypożyczyli najwięcej filmów. Podaj ich identyfikator oraz liczbę wypożyczonych filmów.
   
   Oczekiwana odpowiedź

   Klient 148 - 46 wypożyczeń
   Klient 526 - 45 wypożyczeń
   Klienci 236, 144 - 42 wypożyczenia
   Klient 75 - 41 wypożyczeń
   Klienci 469, 197 - 40 wypożyczeń
   Klienci 178, 137, 468 - 39 wypożyczeń
   
7. Podaj średni czas trwania wszystkich filmów w bazie.
   
   Oczekiwana odpowiedź

   115,2720 minut.
   • Podaj ten czas w godzinach (odpowiednio zmodyfikuj zapytanie!).
     
     Oczekiwana odpowiedź

     1,9212 godziny.
   • Podaj ten czas w minutach z zaokrągleniem w górę (odpowiednio zmodyfikuj zapytanie!)
     
     Oczekiwana odpowiedź

     116 minut.
8. Podaj średni czas trwania wszystkich filmów w bazie (w minutach, z zaokrągleniem do 1 miejsca po przecinku) w rozbiciu na ratingi.
   
   Oczekiwana odpowiedź

   G - 111,1
   PG - 112,0
   PG-13 - 120,4
   R - 118,7
   NC-17 - 113,2
9. Podaj sumę, średnią, zliczenie, minimum, maksimum oraz odchylenie standardowe dla płatności w rozbiciu na pracownika obsługującego płatność.
   
   Oczekiwana odpowiedź

   Pracownik 1: suma - 33482.50, średnia - 4.157251, zliczenie - 8054, minimum - 0.00, maksimum - 11.99, odchylenie standardowe - 2.364854
   Pracownik 2: suma - 33924.06, średnia - 4.245815, zliczenie - 7990, minimum - 0.00, maksimum - 11.99, odchylenie standardowe - 2.360068
   

Lista 3: zapytania ze złączeniami

Poniższe zadania wymagają złączeń tabel. Konieczne jest, aby wybrać właściwy rodzaj złączenia, pasujący do treści zadania.

1. Podaj identyfikatory i tytuły filmów, identyfikatory sklepów oraz datę wypożyczenia 10 ostatnio wypożyczonych filmów, ale tylko takich, które były wypożyczone przed 31.12.2005.
   
   Oczekiwana odpowiedź

   Identyfikatory filmów: 585, 439, 452, 951, 168, 290, 846, 137, 895, 771
2. Podaj identyfikatory, tytuły i opisy filmów oraz nazwy języka i oryginalnego języka (NULL, jeśli nie ma podanego) dla każdego filmu.
3. Wypisz listę aktorów (nazwisko i imię jako jedno pole) i tytułów filmów, w których grali, każdy film w osobnym wierszu.
4. Rozbuduj powyższe zapytanie, aby każdy aktor znajdował się na liście tylko raz, a w polu z tytułem filmu pojawiły się wszystkie te filmy, w których dany aktor grał, rozdzielone przecinkiem (wersja trochę bardziej zaawansowana: rozdzielone przecinkiem i spacją (", ")).
   Uwaga. Może być potrzebna funkcja GROUP_CONCAT.
5. Dla każdego adresu wypisz: id adresu, 1. i 2. wiersz adresu, dystrykt, kod pocztowy, nazwę miasta, nazwę państwa i numer telefonu.
6. Dla każdej płatności wypisz: id płatności, imię i nazwisko klienta (w jednej kolumnie), imię i nazwisko pracownika (w jednej kolumnie), tytuł wypożyczonego filmu, kwotę do zapłaty i datę zapłaty.
7. Dla każdego sklepu wypisz: id sklepu, imię i nazwisko menedżera (w jednej kolumnie), pierwszy wiersz adresu, kod pocztowy, nazwę miasta i numer telefonu.

Lista 4: zapytania ze złączeniami i funkcjami agregującymi

Poniższe zapytania wymagają złączeń tabel i użycia funkcji agregujących.

1. Podaj średni czas trwania wszystkich filmów w bazie w rozbiciu na kategorie.
2. Dla każdego tytułu filmu podaj, ile razy był on wypożyczony, choćby nie był wypożyczony nigdy.
3. Dla każdego filmu podaj jego tytuł oraz liczbę egzemplarzy tego filmu w posiadaniu wypożyczalni.
4. Wypisz nazwy kategorii i liczbę filmów przypisanych do tej kategorii. Zadbaj o czytelność tabeli wynikowej.
5. Podaj nazwiska i imiona (jako jedno pole) 5 pierwszych aktorów, którzy wystąpili w największej liczbie filmów.
6. Wypisz listę filmów aktualnie wypożyczonych oraz liczbę wypożyczonych egzemplarzy. Pomiń filmy, których aktualnie nikt nie wypożycza.
7. Podaj liczbę klientów w każdym z państw w bazie.
8. Podaj nazwę kategorii oraz liczbę filmów w tej kategorii i ich średnią długość w godzinach dla każdej z kategorii. Zaokrąglij średni czas trwania filmu do jednego miejsca po przecinku.
9. Podaj sumę oraz średnią płatności dla każdego miasta zarejestrowanego w bazie.