Kolokwium SQL — Zawody sportowe

Bazy danych · PostgreSQL · Autor pierwotnego kolokwium: Tomasz Waleń.

Sportowcy mogą trenować innych sportowców oraz startować w kilku dyscyplinach tych samych zawodów.

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 sport.create.sql
\i sport.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 sport.drop.sql usuwa tabele zestawu wraz z danymi.

Schemat w skrócie

Dzień odniesienia to 31 października 2026 r. Zawody zakończone mają datę końca wcześniejszą od tej daty. Udział obejmuje cały okres zawodów.

Jeden wiersz udziałów oznacza start jednej osoby w jednej dyscyplinie. Miejsce 1 oznacza zwycięstwo. Dopuszczamy remisy. NULL to brak klasyfikacji; wszystkie miejsca zawodów trwających i przyszłych są NULL.

W przykładowych danych hierarchia trenerów jest acykliczna. Sportowca identyfikujemy po id, nie po imieniu i nazwisku.

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. 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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

Zadania dodatkowe 11–12: rekurencja

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

11. 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.

12. 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.