Kolokwium SQL — Bokserzy

Bazy danych · PostgreSQL · Autor pierwotnego kolokwium: Jacek Sroka.

Zestaw opisuje kluby, bokserów, aktualne kategorie wagowe oraz wyniki walk.

Zadania podstawowe 1–10 są warte po 1 punkcie. Zadania 11–12 z rekurencji są dla chętnych — po 1 punkcie dodatkowym.

Pliki do pobrania

Uruchomienie

W jednym katalogu proszę zapisać skrypty lub rozpakować paczkę studencką. Po uruchomieniu w tym katalogu programu psql i połączeniu z własną bazą proszę sprawdzić schemat i wczytać dane:

SELECT current_user, current_schema();
\i boks.create.sql
\i boks.insert.sql

W nazwie rozwiazania.imie.nazwisko.sql proszę wstawić swoje imię i nazwisko, np. rozwiazania.Jan.Kowalski.sql. Wewnątrz pliku proszę zastąpić kolejne SELECT n AS zadanie; własnymi zapytaniami.

\i rozwiazania.Jan.Kowalski.sql

Po zakończeniu ćwiczenia \i boks.drop.sql usuwa tabele zestawu wraz z danymi.

Schemat w skrócie

Waga jest podana w całych funtach, a wzrost w centymetrach. Aktualną kategorię wyznacza waga zawodnika. W danych przedziały pokrywają zakres 1–499 i nie nakładają się.

Wynik walki: 0 = remis, 1 = wygrywa bokser1, 2 = wygrywa bokser2. Historyczna kategoria walki może być inna niż aktualna kategoria zawodnika.

Baza przechowuje tylko aktualną przynależność do klubu. Zwycięstwa zaliczamy obecnemu klubowi boksera. Tożsamość określa id, nie nazwisko.

Każde zadanie należy rozwiązać jednym niezależnym zapytaniem. Powinno ono działać także dla innych danych zgodnych ze schematem i założeniami zadania. Jeśli nie wskazano porządku wyników, kolejność wierszy jest dowolna.

Zadania podstawowe 1–10

1. Niepokonani w aktualnej kategorii

Wypisać nazwiska bokserów, którzy nie przegrali żadnej walki w swojej aktualnej kategorii wagowej. Aktualną kategorię wyznacza obecna waga boksera. Uwzględnić również bokserów bez walk oraz tych, którzy przegrywali wyłącznie w innych kategoriach.

Kolumny: nazwisko.

Sortowanie: nazwisko, id boksera.

2. Puste kategorie

Wypisać kategorie wagowe, do których nie należy obecnie żaden bokser.

Kolumny: id, nazwa.

Sortowanie: id.

3. Nieobsadzone wagi

Wypisać wszystkie całkowite wagi w funtach z zakresu od 1 do 499 włącznie, których nie ma obecnie żaden bokser. Chodzi o wartości wagi, a nie nazwy kategorii.

Kolumny: waga.

Sortowanie: waga.

4. Raport klubów i kategorii

Dla każdego klubu wypisać liczbę jego bokserów w każdej z trzech aktualnych kategorii: piórkowej, ciężkiej i superciężkiej. Każdy klub ma zajmować jeden wiersz, a każda z tych kategorii osobną kolumnę. Uwzględnić także kluby bez bokserów i podać zera w nieobsadzonych kategoriach. Bokserów niezrzeszonych pominąć.

Kolumny: id_klubu, nazwa_klubu, piorkowa, ciezka, superciezka.

Sortowanie: id_klubu.

5. Wyżsi od średniej

Wypisać nazwisko i wzrost boksera, nazwę jego aktualnej kategorii wagowej oraz średni wzrost bokserów w tej kategorii, jeżeli jest on wyższy od tej średniej. Średnią liczyć ze wszystkich obecnych członków kategorii, także z badanego boksera i osób niezrzeszonych.

Kolumny: nazwisko, wzrost, kategoria, sredni_wzrost.

Sortowanie: nazwisko, id boksera.

6. Najwięcej zwycięstw klubu

Wypisać maksymalną łączną liczbę zwycięstw jednego klubu. Do dorobku klubu zaliczyć wszystkie zapisane zwycięstwa bokserów, którzy obecnie do niego należą. Chodzi o sumę zwycięstw całego klubu. Nie trzeba podawać, który to klub; bokserów niezrzeszonych pominąć. Jeśli żaden klub nie ma zwycięstwa, podać 0.

Kolumny: maks_zwyciestw.

Sortowanie: nie dotyczy — jeden wiersz.

7. Najlepszy wynik zawodnika w klubie

Dla każdego klubu wypisać maksymalną liczbę zwycięstw jednego jego obecnego boksera. Uwzględnić wszystkie kluby. Dla klubu bez bokserów lub bez zwycięstw podać 0.

Kolumny: id_klubu, maks_zwyciestw.

Sortowanie: id_klubu.

8. Także bokserzy niezrzeszeni

Rozwiązać zadanie 7, dodając jeden wiersz z maksymalną liczbą zwycięstw jednego boksera niezrzeszonego. W tym wierszu id_klubu ma być NULL. Wiersz ten należy wypisać wtedy i tylko wtedy, gdy istnieje co najmniej jeden bokser niezrzeszony. Nie wolno użyć UNION ani UNION ALL.

Kolumny: id_klubu, maks_zwyciestw.

Sortowanie: id_klubu, z NULL na końcu.

9. Kluby bez bokserów

Wypisać nazwy klubów, do których nie należy żaden bokser. Klub mający bokserów, którzy jeszcze nie walczyli, nie spełnia tego warunku.

Kolumny: nazwa.

Sortowanie: nazwa.

10. Środkowa wartość wzrostu

Uporządkować różne wartości wzrostu występujące w bazie i znaleźć wartość dokładnie środkową. Wypisać nazwisko i wzrost każdego boksera mającego taki wzrost. Przy szukaniu środka nie liczyć powtórzeń wartości. Jeżeli liczba różnych wzrostów jest parzysta albo nie ma bokserów, wynik ma być pusty; nie obliczać średniej dwóch środkowych wartości.

Kolumny: nazwisko, wzrost.

Sortowanie: nazwisko, id boksera.

Zadania dodatkowe 11–12: rekurencja

Oba zadania są nieobowiązkowe. Każde jest warte 1 punkt dodatkowy. Proszę użyć WITH RECURSIVE.

11. Łańcuch zwycięstw

Przyjmijmy strzałkę A → B, gdy A choć raz pokonał B. Poczynając od boksera nr 6, wypisać id i nazwisko każdego innego boksera osiągalnego po jednej lub wielu takich strzałkach. Daty nie mają znaczenia; remisy nie tworzą strzałki. Każdą osobę wypisać raz, w kolejności id. Uwzględnić możliwość cyklu.

Kolumny: id, nazwisko.

12. Zwycięstwa w porządku chronologicznym

Jak zmieni się wynik zadania 11, jeśli każda kolejna walka w łańcuchu musi odbyć się później niż poprzednia? Dla każdej osiągalnej osoby innej niż bokser nr 6 wypisać id, nazwisko i najwcześniejszą datę ostatniej walki spośród łańcuchów kończących się na tej osobie. Dopuszczamy powrót do tego samego boksera w późniejszym dniu. Posortować po dacie, a przy remisie po id.

Kolumny: id, nazwisko, najwczesniej.