-- Kolokwium SQL — Bokserzy (PostgreSQL, psql). -- Wzorcowe rozwiązania. -- Zadania 1–10: po 1 punkcie. Zadania 11–12 z rekurencji są NIEOBOWIĄZKOWE, po 1 punkcie dodatkowym. \encoding UTF8 \set ON_ERROR_STOP on \pset pager off \pset null '(NULL)' \echo 'Zadanie 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.' SELECT b.nazwisko FROM bokserzy AS b JOIN kat_wagowe AS k ON b.waga BETWEEN k.min AND k.max WHERE NOT EXISTS ( SELECT 1 FROM walki AS w WHERE w.kat_wag = k.id AND ((w.bokser1 = b.id AND w.zwyciezca = 2) OR (w.bokser2 = b.id AND w.zwyciezca = 1)) ) ORDER BY b.nazwisko, b.id; \echo 'Zadanie 2. Puste kategorie. Wypisać kategorie wagowe, do których nie należy obecnie żaden bokser. Kolumny: id, nazwa. Sortowanie: id.' SELECT k.id, k.nazwa FROM kat_wagowe AS k WHERE NOT EXISTS (SELECT 1 FROM bokserzy AS b WHERE b.waga BETWEEN k.min AND k.max) ORDER BY k.id; \echo 'Zadanie 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.' SELECT g.waga FROM generate_series(1, 499) AS g(waga) WHERE NOT EXISTS (SELECT 1 FROM bokserzy AS b WHERE b.waga = g.waga) ORDER BY g.waga; \echo 'Zadanie 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.' SELECT c.id AS id_klubu, c.nazwa AS nazwa_klubu, COUNT(b.id) FILTER (WHERE k.nazwa = 'piórkowa') AS piorkowa, COUNT(b.id) FILTER (WHERE k.nazwa = 'ciężka') AS ciezka, COUNT(b.id) FILTER (WHERE k.nazwa = 'superciężka') AS superciezka FROM kluby AS c LEFT JOIN bokserzy AS b ON b.klub = c.id LEFT JOIN kat_wagowe AS k ON b.waga BETWEEN k.min AND k.max GROUP BY c.id, c.nazwa ORDER BY c.id; \echo 'Zadanie 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.' SELECT b.nazwisko, b.wzrost, k.nazwa AS kategoria, (SELECT AVG(b2.wzrost) FROM bokserzy AS b2 WHERE b2.waga BETWEEN k.min AND k.max) AS sredni_wzrost FROM bokserzy AS b JOIN kat_wagowe AS k ON b.waga BETWEEN k.min AND k.max WHERE b.wzrost > (SELECT AVG(b2.wzrost) FROM bokserzy AS b2 WHERE b2.waga BETWEEN k.min AND k.max) ORDER BY b.nazwisko, b.id; \echo 'Zadanie 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.' WITH zwyciestwa_klubow AS ( SELECT c.id, COUNT(w.zwyciezca) AS liczba_zwyciestw FROM kluby AS c LEFT JOIN bokserzy AS b ON b.klub = c.id LEFT JOIN walki AS w ON (w.bokser1 = b.id AND w.zwyciezca = 1) OR (w.bokser2 = b.id AND w.zwyciezca = 2) GROUP BY c.id ) SELECT COALESCE(MAX(liczba_zwyciestw), 0) AS maks_zwyciestw FROM zwyciestwa_klubow; \echo 'Zadanie 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.' WITH zwyciestwa_bokserow AS ( SELECT b.id, b.klub, COUNT(w.zwyciezca) AS liczba_zwyciestw FROM bokserzy AS b LEFT JOIN walki AS w ON (w.bokser1 = b.id AND w.zwyciezca = 1) OR (w.bokser2 = b.id AND w.zwyciezca = 2) GROUP BY b.id, b.klub ) SELECT c.id AS id_klubu, COALESCE(MAX(z.liczba_zwyciestw), 0) AS maks_zwyciestw FROM kluby AS c LEFT JOIN zwyciestwa_bokserow AS z ON z.klub = c.id GROUP BY c.id ORDER BY c.id; \echo 'Zadanie 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.' WITH zwyciestwa_bokserow AS ( SELECT b.id, b.klub, COUNT(w.zwyciezca) AS liczba_zwyciestw FROM bokserzy AS b LEFT JOIN walki AS w ON (w.bokser1 = b.id AND w.zwyciezca = 1) OR (w.bokser2 = b.id AND w.zwyciezca = 2) GROUP BY b.id, b.klub ) SELECT c.id AS id_klubu, COALESCE(MAX(z.liczba_zwyciestw), 0) AS maks_zwyciestw FROM kluby AS c FULL JOIN zwyciestwa_bokserow AS z ON z.klub = c.id GROUP BY c.id ORDER BY c.id NULLS LAST; \echo 'Zadanie 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.' SELECT c.nazwa FROM kluby AS c WHERE NOT EXISTS (SELECT 1 FROM bokserzy AS b WHERE b.klub = c.id) ORDER BY c.nazwa; \echo 'Zadanie 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.' SELECT b.nazwisko, b.wzrost FROM bokserzy AS b WHERE (SELECT COUNT(DISTINCT b2.wzrost) FROM bokserzy AS b2 WHERE b2.wzrost < b.wzrost) = (SELECT COUNT(DISTINCT b2.wzrost) FROM bokserzy AS b2 WHERE b2.wzrost > b.wzrost) ORDER BY b.nazwisko, b.id; \echo 'Zadanie 11. Dla chętnych (rekurencja, nieobowiązkowe). Ł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.' WITH RECURSIVE wygrane(kto, kogo, data) AS ( SELECT CASE zwyciezca WHEN 1 THEN bokser1 ELSE bokser2 END, CASE zwyciezca WHEN 1 THEN bokser2 ELSE bokser1 END, data FROM walki WHERE zwyciezca IN (1, 2) ), osiagalni(id) AS ( SELECT kogo FROM wygrane WHERE kto = 6 UNION SELECT w.kogo FROM osiagalni o JOIN wygrane w ON w.kto = o.id ) SELECT b.id, b.nazwisko FROM osiagalni o JOIN bokserzy b ON b.id = o.id WHERE b.id <> 6 ORDER BY b.id; \echo 'Zadanie 12. Dla chętnych (rekurencja, nieobowiązkowe). 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.' WITH RECURSIVE wygrane(kto, kogo, data) AS ( SELECT CASE zwyciezca WHEN 1 THEN bokser1 ELSE bokser2 END, CASE zwyciezca WHEN 1 THEN bokser2 ELSE bokser1 END, data FROM walki WHERE zwyciezca IN (1, 2) ), dojscia(id, data) AS ( SELECT kogo, data FROM wygrane WHERE kto = 6 UNION SELECT w.kogo, w.data FROM dojscia d JOIN wygrane w ON w.kto = d.id AND w.data > d.data ) SELECT b.id, b.nazwisko, MIN(d.data) AS najwczesniej FROM dojscia d JOIN bokserzy b ON b.id = d.id WHERE b.id <> 6 GROUP BY b.id, b.nazwisko ORDER BY najwczesniej, b.id;