Kolokwium z baz danych: Zawody sportowe

Autor pierwotnych zadań 1–8: Tomasz Waleń.

Wersja: PostgreSQL; rozszerzenie i aktualizacja 31 października 2026 r.

Struktura bazy danych: sport.create.sql, sport.drop.sql.

Przykładowe dane: sport.insert.sql — 100 sportowców, 50 zawodów i 500 startów w dyscyplinach.

Pełny opis zestawu: Zawody sportowe — tabele, zależności i znaczenie danych.

Szablon rozwiązań: sport.rozwiazania.imie.nazwisko.sql.

Przykładowe rozwiązania: sport.solution.sql — skrypt psql wypisujący numery i treść zadań oraz wyniki zapytań.

Baza danych

Baza zawiera fikcyjne dane o sportowcach, ich trenerach, zawodach oraz startach w poszczególnych dyscyplinach.

Sportowcy(id, imie, nazwisko, trener)
Zawody(id, nazwa, poczatek, koniec)
UczestnicyZawodow(zawody, zawodnik, dyscyplina, miejsce)

Sportowcy.trener wskazuje Sportowcy.id. Kolumny UczestnicyZawodow.zawody i UczestnicyZawodow.zawodnik wskazują odpowiednio Zawody.id i Sportowcy.id. Kluczem udziałów jest trójka (zawody, zawodnik, dyscyplina).

Przyjęte zasady

Uruchomienie w psql

Po pobraniu skryptów i przejściu do ich katalogu należy wykonać:

\encoding UTF8
\set ON_ERROR_STOP on
\pset pager off
\pset null '(NULL)'
\i sport.create.sql
\i sport.insert.sql

Wszystkie zapytania należy wykonywać w tym samym schemacie. Skrypt danych wymaga pustych tabel. Szablon zawiera po jednym miejscu na rozwiązanie każdego zadania.

Zadania

Należy napisać następujące zapytania SQL. Dla każdego zadania podano kolumny i wymagane sortowanie. Zadania 9 i 10 są trudniejsze. Zachowano sens i kolejność ośmiu pierwotnych zadań; okno historyczne z zadania 8 dostosowano do dat nowego zestawu.

  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.
    Porządek rosnący: 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.
    Porządek rosnący: 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.
    Porządek rosnący: 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.
    Porządek rosnący: 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.
    Porządek rosnący: 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.
    Porządek rosnący: id_trenera, id_podopiecznego.

  7. 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.
    Porządek rosnący: id.

  8. 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.
    Porządek rosnący: poczatek_okresu.

  9. Trenerzy zwycięzców we wszystkich dyscyplinach (trudniejsze). Wypisać trenerów, których bezpośredni lub pośredni podopieczni zdobyli łącznie co najmniej jedno pierwsze miejsce w każdej dyscyplinie występującej w zawodach zakończonych przed 31 października 2026 r. Dyscypliny ustalić na podstawie wszystkich udziałów w takich zawodach, niezależnie od wystawienia klasyfikacji. Zwycięzcy w różnych dyscyplinach mogą być różnymi osobami. Własnych wyników trenera nie uwzględniać. Rozpatrywać tylko osoby mające co najmniej jednego podopiecznego. Gdy zbiór wymaganych dyscyplin jest pusty, wszystkie takie osoby spełniają warunek.

    Kolumny: id_trenera, imie, nazwisko.
    Porządek rosnący: id_trenera.

  10. Najdłuższe okresy aktywności zespołu trenera (trudniejsze). 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.
    Porządek rosnący: id_trenera, poczatek_okresu.


Kolokwia (SQL)

Valid XHTML 1.0 Strict