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.
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.
kluby(nazwa, id, ranking) — klucz: id.bokserzy(id, nazwisko, waga, wzrost, klub) — klub wskazuje kluby.id; NULL oznacza osobę niezrzeszoną.kat_wagowe(id, min, max, nazwa) — przedział wag w funtach, obie granice włącznie.walki(bokser1, bokser2, zwyciezca, kat_wag, data) — bokser1 i bokser2 wskazują zawodników, kat_wag kategorię w dniu walki; kluczem jest trójka (bokser1, bokser2, data).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.
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.
Wypisać kategorie wagowe, do których nie należy obecnie żaden bokser.
Kolumny: id, nazwa.
Sortowanie: id.
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.
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.
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.
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.
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.
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.
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.
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.
Oba zadania są nieobowiązkowe. Każde jest warte 1 punkt dodatkowy. Proszę użyć WITH RECURSIVE.
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.
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.