-- Kolokwium SQL — Zawody sportowe (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. Liczba zawodów każdego sportowca. Dla każdego sportowca wypisać liczbę różnych zawodów, w których bierze lub brał udział, czyli zawodów rozpoczętych nie później niż 31 października 2026 r. Udział w kilku dyscyplinach tych samych zawodów liczyć tylko raz. Uwzględnić sportowców bez takich udziałów, podając liczbę 0. Kolumny: id, imie, nazwisko, liczba_zawodow. Sortowanie: id.' SELECT s.id, s.imie, s.nazwisko, COUNT(DISTINCT z.id) AS liczba_zawodow FROM Sportowcy AS s LEFT JOIN UczestnicyZawodow AS u ON u.zawodnik = s.id LEFT JOIN Zawody AS z ON z.id = u.zawody AND z.poczatek <= DATE '2026-10-31' GROUP BY s.id, s.imie, s.nazwisko ORDER BY s.id; \echo 'Zadanie 2. Najwięcej zawodów jednocześnie — stan na dzień odniesienia. Wypisać sportowców, którzy 31 października 2026 r. uczestniczą w największej liczbie różnych zawodów. Zawody trwają tego dnia, gdy ich początek nie jest późniejszy, a koniec nie jest wcześniejszy od tej daty. Uwzględnić wszystkie remisy. Gdy żaden sportowiec nie uczestniczy tego dnia w żadnych zawodach, wypisać wszystkich sportowców z liczbą 0. Kolumny: id, imie, nazwisko, liczba_zawodow. Sortowanie: id.' WITH liczby AS ( SELECT s.id, s.imie, s.nazwisko, COUNT(DISTINCT z.id) AS liczba_zawodow FROM Sportowcy AS s LEFT JOIN UczestnicyZawodow AS u ON u.zawodnik = s.id LEFT JOIN Zawody AS z ON z.id = u.zawody AND DATE '2026-10-31' BETWEEN z.poczatek AND z.koniec GROUP BY s.id, s.imie, s.nazwisko ) SELECT id, imie, nazwisko, liczba_zawodow FROM liczby WHERE liczba_zawodow = (SELECT MAX(liczba_zawodow) FROM liczby) ORDER BY id; \echo 'Zadanie 3. Sportowcy bez pierwszego miejsca. Wypisać sportowców, którzy nie zajęli pierwszego miejsca w żadnej dyscyplinie żadnych zawodów zakończonych przed 31 października 2026 r. Uwzględnić również osoby bez udziałów w zakończonych zawodach. Brak klasyfikacji, zapisany jako NULL w kolumnie miejsce, nie jest pierwszym miejscem. Kolumny: id, imie, nazwisko. Sortowanie: id.' SELECT s.id, s.imie, s.nazwisko FROM Sportowcy AS s WHERE NOT EXISTS ( SELECT 1 FROM UczestnicyZawodow AS u JOIN Zawody AS z ON z.id = u.zawody WHERE u.zawodnik = s.id AND u.miejsce = 1 AND z.koniec < DATE '2026-10-31' ) ORDER BY s.id; \echo 'Zadanie 4. Udział w nakładających się zawodach. Wypisać sportowców, którzy najpóźniej 31 października 2026 r. uczestniczyli jednocześnie w co najmniej dwóch różnych zawodach. Wystarczy jeden wspólny dzień. Udział w kilku dyscyplinach tych samych zawodów nie spełnia warunku. Każdego sportowca wypisać tylko raz. Kolumny: id, imie, nazwisko. Sortowanie: id.' SELECT s.id, s.imie, s.nazwisko FROM Sportowcy AS s WHERE EXISTS ( SELECT 1 FROM UczestnicyZawodow AS u1 JOIN Zawody AS z1 ON z1.id = u1.zawody JOIN UczestnicyZawodow AS u2 ON u2.zawodnik = u1.zawodnik AND u1.zawody < u2.zawody JOIN Zawody AS z2 ON z2.id = u2.zawody WHERE u1.zawodnik = s.id AND GREATEST(z1.poczatek, z2.poczatek) <= LEAST(z1.koniec, z2.koniec, DATE '2026-10-31') ) ORDER BY s.id; \echo 'Zadanie 5. Łączna liczba dni udziału w zawodach. Dla każdego sportowca policzyć liczbę różnych dni, w których uczestniczył w zawodach, do 31 października 2026 r. włącznie. Wspólny dzień kilku zawodów lub kilku dyscyplin liczyć tylko raz. Z zawodów trwających w dniu odniesienia uwzględnić jedynie dni do tej daty; przyszłe zawody pominąć. Uwzględnić osoby bez takich dni, podając liczbę 0. Kolumny: id, imie, nazwisko, liczba_dni. Sortowanie: id.' -- Rozwijamy przedziały na dni. DISTINCT usuwa zarówno nakładanie się -- zawodów, jak i wielokrotny udział w różnych dyscyplinach jednych zawodów. WITH dni AS ( SELECT DISTINCT u.zawodnik, z.poczatek + g.przesuniecie AS dzien FROM UczestnicyZawodow AS u JOIN Zawody AS z ON z.id = u.zawody CROSS JOIN LATERAL generate_series( 0, LEAST(z.koniec, DATE '2026-10-31') - z.poczatek ) AS g(przesuniecie) WHERE z.poczatek <= DATE '2026-10-31' ) SELECT s.id, s.imie, s.nazwisko, COUNT(d.dzien) AS liczba_dni FROM Sportowcy AS s LEFT JOIN dni AS d ON d.zawodnik = s.id GROUP BY s.id, s.imie, s.nazwisko ORDER BY s.id; \echo 'Zadanie 6. Trenerzy i bezpośredni podopieczni. Wypisać pary: trener i jego bezpośredni podopieczny. Dla obu osób podać identyfikator, imię i nazwisko. Każdą parę wypisać w osobnym wierszu; osoby bez bezpośrednich podopiecznych nie tworzą żadnej pary. Kolumny: id_trenera, trener_imie, trener_nazwisko, id_podopiecznego, podopieczny_imie, podopieczny_nazwisko. Sortowanie: id_trenera, id_podopiecznego.' SELECT t.id AS id_trenera, t.imie AS trener_imie, t.nazwisko AS trener_nazwisko, p.id AS id_podopiecznego, p.imie AS podopieczny_imie, p.nazwisko AS podopieczny_nazwisko FROM Sportowcy AS t JOIN Sportowcy AS p ON p.trener = t.id ORDER BY t.id, p.id; \echo 'Zadanie 7. Niepełna klasyfikacja. Spośród zawodów zakończonych przed 31 października 2026 r. wypisać te, w których przyznano co najmniej jedno miejsce, a jednocześnie co najmniej jeden start pozostał bez klasyfikacji. Podać id, nazwę i liczbę niesklasyfikowanych startów (nie osób), rosnąco według id. Kolumny: id, nazwa, bez_klasyfikacji.' SELECT z.id, z.nazwa, COUNT(*) - COUNT(u.miejsce) AS bez_klasyfikacji FROM zawody z JOIN uczestnicyzawodow u ON u.zawody = z.id WHERE z.koniec < DATE '2026-10-31' GROUP BY z.id, z.nazwa HAVING COUNT(u.miejsce) BETWEEN 1 AND COUNT(*) - 1 ORDER BY z.id; \echo 'Zadanie 8. Najliczniejsze dyscypliny. W każdych zawodach rozpoczętych do 31 października 2026 r. włącznie znaleźć dyscypliny o największej liczbie startujących osób. Wypisać id zawodów, dyscyplinę i liczbę osób; zachować remisy, a zawody bez startów pominąć. Uporządkować wynik według id zawodów i nazwy dyscypliny. Kolumny: zawody, dyscyplina, liczba_osob.' WITH liczby AS ( SELECT u.zawody, u.dyscyplina, COUNT(*) AS liczba_osob FROM uczestnicyzawodow u JOIN zawody z ON z.id = u.zawody WHERE z.poczatek <= DATE '2026-10-31' GROUP BY u.zawody, u.dyscyplina ) SELECT l.zawody, l.dyscyplina, l.liczba_osob FROM liczby l WHERE l.liczba_osob = (SELECT MAX(i.liczba_osob) FROM liczby i WHERE i.zawody = l.zawody) ORDER BY l.zawody, l.dyscyplina; \echo 'Zadanie 9. Najwszechstronniejszy zespół. Wskazać trenerów, których bezpośredni podopieczni wygrali w największej liczbie różnych dyscyplin w zawodach zakończonych przed 31 października 2026 r. Liczyć dyscyplinę tylko raz na trenera. Uwzględnić wszystkich remisujących trenerów; gdy nikt nie odniósł zwycięstwa, wypisać każdą osobę mającą podopiecznego, z liczbą 0. Podać id trenera i liczbę dyscyplin, rosnąco według id. Kolumny: id_trenera, ile.' WITH dyscypliny AS ( SELECT p.trener AS id_trenera, COUNT(DISTINCT u.dyscyplina) FILTER (WHERE z.id IS NOT NULL) AS ile FROM sportowcy p LEFT JOIN uczestnicyzawodow u ON u.zawodnik = p.id AND u.miejsce = 1 LEFT JOIN zawody z ON z.id = u.zawody AND z.koniec < DATE '2026-10-31' WHERE p.trener IS NOT NULL GROUP BY p.trener ) SELECT id_trenera, ile FROM dyscypliny WHERE ile = (SELECT MAX(ile) FROM dyscypliny) ORDER BY id_trenera; \echo 'Zadanie 10. Najdłuższe wolne okresy w kalendarzu. W kalendarzu od 1 stycznia 2025 r. do 31 grudnia 2026 r. włącznie znaleźć najdłuższe ciągłe okresy, w których nie odbywają się żadne zawody zapisane w bazie. Uwzględnić wszystkie zawody, także przyszłe i takie, do których nikt nie jest zapisany. Dla każdego najdłuższego okresu podać pierwszy i ostatni dzień oraz liczbę dni. Przy remisie wypisać wszystkie takie okresy. Gdy zawodów w tym oknie nie ma, wynikiem jest całe okno; gdy zajęty jest każdy dzień, wynik jest pusty. Kolumny: poczatek_okresu, koniec_okresu, liczba_dni. Sortowanie: poczatek_okresu.' -- Dla kolejnych dni tej samej wolnej serii różnica -- dzień - numer_wiersza jest stała (metoda grupowania kolejnych dni). WITH kalendarz AS ( SELECT DATE '2025-01-01' + g.n AS dzien FROM generate_series( 0, DATE '2026-12-31' - DATE '2025-01-01' ) AS g(n) ), wolne AS ( SELECT k.dzien FROM kalendarz AS k WHERE NOT EXISTS ( SELECT 1 FROM Zawody AS z WHERE k.dzien BETWEEN z.poczatek AND z.koniec ) ), grupy AS ( SELECT dzien, dzien - (ROW_NUMBER() OVER (ORDER BY dzien))::integer AS grupa FROM wolne ), okresy AS ( SELECT MIN(dzien) AS poczatek_okresu, MAX(dzien) AS koniec_okresu, COUNT(*) AS liczba_dni FROM grupy GROUP BY grupa ) SELECT poczatek_okresu, koniec_okresu, liczba_dni FROM okresy WHERE liczba_dni = (SELECT MAX(liczba_dni) FROM okresy) ORDER BY poczatek_okresu; \echo 'Zadanie 11. Dla chętnych (rekurencja, nieobowiązkowe). Bezpośredni i pośredni podopieczni. Dla każdej osoby w tabeli Sportowcy wypisać łączną liczbę różnych jej bezpośrednich i pośrednich podopiecznych. Podopieczny podopiecznego jest podopiecznym pośrednim, podobnie na kolejnych poziomach hierarchii. Nie liczyć danej osoby jako jej własnego podopiecznego. Uwzględnić osoby bez podopiecznych, podając liczbę 0. Kolumny: id, imie, nazwisko, liczba_podopiecznych. Sortowanie: id.' -- Hierarchia w danych jest acykliczna. UNION zachowuje jedną kopię -- każdej pary (trener, podopieczny) na wszystkich poziomach. WITH RECURSIVE podopieczni(trener, podopieczny) AS ( SELECT trener, id FROM Sportowcy WHERE trener IS NOT NULL UNION SELECT p.trener, s.id FROM podopieczni AS p JOIN Sportowcy AS s ON s.trener = p.podopieczny ) SELECT s.id, s.imie, s.nazwisko, COUNT(p.podopieczny) AS liczba_podopiecznych FROM Sportowcy AS s LEFT JOIN podopieczni AS p ON p.trener = s.id AND p.podopieczny <> s.id GROUP BY s.id, s.imie, s.nazwisko ORDER BY s.id; \echo 'Zadanie 12. Dla chętnych (rekurencja, nieobowiązkowe). Najdłuższe okresy aktywności zespołu trenera. Dla każdego trenera znaleźć wszystkie najdłuższe ciągłe okresy w kalendarzu od 1 stycznia 2025 r. do 31 grudnia 2026 r. włącznie, w których każdego dnia uczestniczy w zawodach co najmniej trzech różnych jego bezpośrednich lub pośrednich podopiecznych. Zestaw osób może zmieniać się z dnia na dzień. Jedną osobę danego dnia liczyć tylko raz, niezależnie od liczby zawodów i dyscyplin. Własnego udziału trenera nie uwzględniać. Uwzględnić także przyszłe zawody zapisane w kalendarzu. Trenerów bez takiego okresu pominąć; remisy rozpatrywać osobno dla każdego trenera i wypisać wszystkie jego najdłuższe okresy. Kolumny: id_trenera, imie, nazwisko, poczatek_okresu, koniec_okresu, liczba_dni. Sortowanie: id_trenera, poczatek_okresu.' -- Łączymy rekurencję, liczenie różnych osób w danym dniu i grupowanie -- kolejnych dni. Maksymalną długość wyznaczamy osobno dla każdego trenera. WITH RECURSIVE podopieczni(trener, podopieczny) AS ( SELECT trener, id FROM Sportowcy WHERE trener IS NOT NULL UNION SELECT p.trener, s.id FROM podopieczni AS p JOIN Sportowcy AS s ON s.trener = p.podopieczny ), kalendarz AS ( SELECT DATE '2025-01-01' + g.n AS dzien FROM generate_series( 0, DATE '2026-12-31' - DATE '2025-01-01' ) AS g(n) ), aktywne_dni AS ( SELECT p.trener, k.dzien FROM podopieczni AS p JOIN UczestnicyZawodow AS u ON u.zawodnik = p.podopieczny JOIN Zawody AS z ON z.id = u.zawody JOIN kalendarz AS k ON k.dzien BETWEEN z.poczatek AND z.koniec WHERE p.podopieczny <> p.trener GROUP BY p.trener, k.dzien HAVING COUNT(DISTINCT p.podopieczny) >= 3 ), grupy AS ( SELECT trener, dzien, dzien - (ROW_NUMBER() OVER ( PARTITION BY trener ORDER BY dzien ))::integer AS grupa FROM aktywne_dni ), okresy AS ( SELECT trener, MIN(dzien) AS poczatek_okresu, MAX(dzien) AS koniec_okresu, COUNT(*) AS liczba_dni FROM grupy GROUP BY trener, grupa ) SELECT s.id AS id_trenera, s.imie, s.nazwisko, o.poczatek_okresu, o.koniec_okresu, o.liczba_dni FROM okresy AS o JOIN Sportowcy AS s ON s.id = o.trener WHERE o.liczba_dni = ( SELECT MAX(o2.liczba_dni) FROM okresy AS o2 WHERE o2.trener = o.trener ) ORDER BY s.id, o.poczatek_okresu;