-- Kolokwium SQL — Piłkarze i drużyny (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. Zawodnicy Legii Warszawa. Wypisać nazwiska zawodników drużyny Legia Warszawa, po jednym wierszu na zawodnika. Kolumny: nazwisko.' SELECT p.nazwisko FROM pilkarz p JOIN sklad s ON s.idpilkarza = p.id JOIN druzyna d ON d.skrot = s.skrotdruz WHERE d.nazwa = 'Legia Warszawa' ORDER BY p.nazwisko, p.id; \echo 'Zadanie 2. Piłkarze i ich drużyny. Wypisać imiona i nazwiska wszystkich piłkarzy, a przy nich skróty i nazwy ich drużyn. Jeśli piłkarz należy do kilku składów, podać osobny wiersz dla każdego z nich. Uwzględnić osoby bez drużyny. Kolumny: imie, nazwisko, skrot, nazwa.' SELECT p.imie, p.nazwisko, d.skrot, d.nazwa FROM pilkarz p LEFT JOIN sklad s ON s.idpilkarza = p.id LEFT JOIN druzyna d ON d.skrot = s.skrotdruz ORDER BY p.id, d.skrot; \echo 'Zadanie 3. Bez klubu lub bez zawodników. Wypisać jedną kolumnę nieprzydzieleni: nazwy drużyn bez zawodników oraz imiona piłkarzy bez drużyny. Każdą taką drużynę i osobę uwzględnić raz, nawet jeśli mają identyczną nazwę lub imię. Kolumny: nieprzydzieleni.' SELECT d.nazwa AS nieprzydzieleni FROM druzyna d WHERE NOT EXISTS (SELECT FROM sklad s WHERE s.skrotdruz = d.skrot) UNION ALL SELECT p.imie FROM pilkarz p WHERE NOT EXISTS (SELECT FROM sklad s WHERE s.idpilkarza = p.id) ORDER BY 1; \echo 'Zadanie 4. Rozpiętość wycen w drużynie. Wypisać skrót i nazwę każdej drużyny, w której różnica między najwyższą i najniższą znaną ceną jest ściśle mniejsza niż 1000. Pominąć drużyny bez żadnej znanej ceny. Kolumny: skrot, nazwa.' SELECT d.skrot, d.nazwa FROM druzyna d JOIN sklad s ON s.skrotdruz = d.skrot GROUP BY d.skrot, d.nazwa HAVING MAX(s.cena) - MIN(s.cena) < 1000 ORDER BY d.skrot; \echo 'Zadanie 5. Najniższy numer koszulki. Wyznaczyć najmniejszy numer koszulki w całej bazie. Dla każdej drużyny, w której ktoś gra z tym numerem, wypisać skrót drużyny i wszystkich zawodników z jej składu (id, imię i nazwisko). Kolumny: skrotdruz, id, imie, nazwisko.' SELECT s.skrotdruz, p.id, p.imie, p.nazwisko FROM sklad s JOIN pilkarz p ON p.id = s.idpilkarza WHERE s.skrotdruz IN ( SELECT skrotdruz FROM sklad WHERE numer = (SELECT MIN(numer) FROM sklad) ) ORDER BY s.skrotdruz, p.id; \echo 'Zadanie 6. Najliczniej obsadzona pozycja. Dla każdej drużyny i każdej znanej pozycji policzyć ilu jej zawodników gra na tej pozycji. Wypisać wszystkie pary (skrót drużyny, pozycja) o największej dodatniej liczebności oraz tę liczebność. Kolumny: skrotdruz, pozycja, ilu.' WITH grupy AS ( SELECT s.skrotdruz, p.pozycja, COUNT(*) AS ilu FROM sklad s JOIN pilkarz p ON p.id = s.idpilkarza WHERE p.pozycja IS NOT NULL GROUP BY s.skrotdruz, p.pozycja ) SELECT * FROM grupy WHERE ilu = (SELECT MAX(ilu) FROM grupy) ORDER BY skrotdruz, pozycja; \echo 'Zadanie 7. Najdrożsi piłkarze. Wypisać id, imię i nazwisko każdego piłkarza, który w co najmniej jednym składzie osiąga najwyższą znaną cenę w całej bazie. Uwzględnić remisy, każdego piłkarza wypisać raz. Kolumny: id, imie, nazwisko.' SELECT p.id, p.imie, p.nazwisko FROM pilkarz p WHERE EXISTS (SELECT FROM sklad s WHERE s.idpilkarza = p.id AND s.cena = (SELECT MAX(cena) FROM sklad)) ORDER BY p.id; \echo 'Zadanie 8. Ranking cen w drużynie. Dla każdej znanej wyceny wypisać imię i nazwisko piłkarza, skrót drużyny, cenę i miejsce w rankingu cen w tej drużynie. Najwyższa cena ma miejsce 1, równe ceny mają to samo miejsce, a kolejne miejsca nie mają luk. Posortować według drużyny, miejsca i id zawodnika. Kolumny: imie, nazwisko, skrotdruz, cena, miejsce.' SELECT p.imie, p.nazwisko, s.skrotdruz, s.cena, DENSE_RANK() OVER (PARTITION BY s.skrotdruz ORDER BY s.cena DESC) AS miejsce FROM sklad s JOIN pilkarz p ON p.id = s.idpilkarza WHERE s.cena IS NOT NULL ORDER BY s.skrotdruz, miejsce, p.id; \echo 'Zadanie 9. Następny numer koszulki. Wypisać każdego piłkarza (id, imię, nazwisko), drużynę, jego numer i najmniejszy większy numer w tej samej drużynie. Ostatni numer nie ma następnika. Piłkarza bez drużyny uwzględnić z wartościami NULL; piłkarza kilku drużyn — osobno dla każdej. Kolumny: id, imie, nazwisko, skrotdruz, numer, nastepny.' SELECT p.id, p.imie, p.nazwisko, s.skrotdruz, s.numer, LEAD(s.numer) OVER (PARTITION BY s.skrotdruz ORDER BY s.numer) AS nastepny FROM pilkarz p LEFT JOIN sklad s ON s.idpilkarza = p.id ORDER BY p.id, s.skrotdruz; \echo 'Zadanie 10. Wszystkie pozycje w składzie. Wypisać skróty i nazwy drużyn, które mają w składzie co najmniej jednego piłkarza na każdej znanej pozycji występującej w tabeli pilkarz. Wartości NULL nie traktować jako pozycji. Kolumny: skrot, nazwa.' SELECT d.skrot, d.nazwa FROM druzyna d WHERE NOT EXISTS ( SELECT p.pozycja FROM pilkarz p WHERE p.pozycja IS NOT NULL EXCEPT SELECT p.pozycja FROM sklad s JOIN pilkarz p ON p.id = s.idpilkarza WHERE s.skrotdruz = d.skrot ) ORDER BY d.skrot; \echo 'Zadanie 11. Dla chętnych (rekurencja, nieobowiązkowe). Kluby połączone wspólnym zawodnikiem. Dwa różne kluby są połączone, jeśli mają w bazie wspólnego piłkarza. Używając rekurencji, wypisać skróty i nazwy wszystkich klubów osiągalnych z LW przez dowolną liczbę takich połączeń (w tym LW). Każdy klub wypisać raz. Kolumny: skrot, nazwa.' WITH RECURSIVE kluby(skrot) AS ( SELECT skrot FROM druzyna WHERE skrot = 'LW' UNION SELECT b.skrotdruz FROM kluby k JOIN sklad a ON a.skrotdruz = k.skrot JOIN sklad b ON b.idpilkarza = a.idpilkarza AND b.skrotdruz <> a.skrotdruz ) SELECT d.skrot, d.nazwa FROM kluby k JOIN druzyna d USING (skrot) ORDER BY d.skrot; \echo 'Zadanie 12. Dla chętnych (rekurencja, nieobowiązkowe). Odległość między klubami. Przy tej samej definicji połączenia wyznaczyć rekurencyjnie minimalną liczbę przejść z LW do każdego klubu. Wypisać skrót klubu i odległość: 0 dla LW i NULL dla klubów nieosiągalnych. Nie wolno krążyć w cyklu. Kolumny: skrot, odleglosc.' WITH RECURSIVE pary(a, b) AS ( SELECT DISTINCT s.skrotdruz, t.skrotdruz FROM sklad s JOIN sklad t ON t.idpilkarza = s.idpilkarza AND t.skrotdruz <> s.skrotdruz ), drogi(klub, odwiedzone, kroki) AS ( SELECT skrot, ARRAY[skrot::text], 0 FROM druzyna WHERE skrot = 'LW' UNION ALL SELECT p.b, array_append(d.odwiedzone, p.b::text), d.kroki + 1 FROM drogi d JOIN pary p ON p.a = d.klub WHERE NOT (p.b = ANY(d.odwiedzone)) ) SELECT k.skrot, MIN(d.kroki) AS odleglosc FROM druzyna k LEFT JOIN drogi d ON d.klub = k.skrot GROUP BY k.skrot ORDER BY k.skrot;