-- Kolokwium SQL — Formuła 1 (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. Długie wyścigi jesienią. Proszę wypisać wyścigi rozegrane od 1 września do 31 października 2026 roku włącznie, których zaplanowana liczba okrążeń wynosi co najmniej 60. Kolumny: id_wyscigu, nazwa, data_wyscigu, liczba_okrazen. Sortowanie: data_wyscigu, id_wyscigu — rosnąco.' SELECT id_wyscigu, nazwa, data_wyscigu, liczba_okrazen FROM wyscig WHERE data_wyscigu >= DATE '2026-09-01' AND data_wyscigu < DATE '2026-11-01' AND liczba_okrazen >= 60 ORDER BY data_wyscigu, id_wyscigu; \echo 'Zadanie 2. Podium wyścigu 7. Proszę wypisać kierowców, którzy zajęli miejsca od 1 do 3 w wyścigu o identyfikatorze 7, wraz z zespołem reprezentowanym w tym wyścigu. Każdego kierowcę należy wypisać raz. Chodzi o pozycję na mecie, a nie pozycję startową. Kolumny: id_kierowcy, imie, nazwisko, id_zespolu, nazwa_zespolu, pozycja_meta. Sortowanie: pozycja_meta, id_kierowcy — rosnąco.' SELECT k.id_kierowcy, k.imie, k.nazwisko, z.id_zespolu, z.nazwa AS nazwa_zespolu, s.pozycja_meta FROM start AS s JOIN kierowca AS k ON k.id_kierowcy = s.id_kierowcy JOIN zespol AS z ON z.id_zespolu = s.id_zespolu WHERE s.id_wyscigu = 7 AND s.pozycja_meta BETWEEN 1 AND 3 ORDER BY s.pozycja_meta, k.id_kierowcy; \echo 'Zadanie 3. Partnerzy z jednego zespołu. Proszę wypisać wszystkie pary różnych kierowców, którzy w wyścigu o identyfikatorze 7 wystartowali w tym samym zespole. Należy uwzględnić również kierowców, którzy wyścigu nie ukończyli albo zostali zdyskwalifikowani. Każdą parę proszę wypisać tylko raz: pierwszy kierowca ma mieć mniejszy identyfikator. Identyczne imiona i nazwiska nie oznaczają tej samej osoby. Kolumny: id_zespolu, nazwa_zespolu, id_pierwszego, imie_pierwszego, nazwisko_pierwszego, id_drugiego, imie_drugiego, nazwisko_drugiego. Sortowanie: id_zespolu, id_pierwszego, id_drugiego — rosnąco.' SELECT z.id_zespolu, z.nazwa AS nazwa_zespolu, k1.id_kierowcy AS id_pierwszego, k1.imie AS imie_pierwszego, k1.nazwisko AS nazwisko_pierwszego, k2.id_kierowcy AS id_drugiego, k2.imie AS imie_drugiego, k2.nazwisko AS nazwisko_drugiego FROM start AS s1 JOIN start AS s2 ON s2.id_wyscigu = s1.id_wyscigu AND s2.id_zespolu = s1.id_zespolu AND s1.id_kierowcy < s2.id_kierowcy JOIN kierowca AS k1 ON k1.id_kierowcy = s1.id_kierowcy JOIN kierowca AS k2 ON k2.id_kierowcy = s2.id_kierowcy JOIN zespol AS z ON z.id_zespolu = s1.id_zespolu WHERE s1.id_wyscigu = 7 ORDER BY z.id_zespolu, k1.id_kierowcy, k2.id_kierowcy; \echo 'Zadanie 4. Ile wyścigów odbyło się na torach. Dla każdego toru proszę podać liczbę wyścigów rozegranych na nim w 2026 roku. Należy uwzględnić również tory bez wyścigów w tym roku, podając dla nich 0. Wyścigi z innych lat nie mogą zwiększać licznika ani powodować pominięcia toru. Kolumny: id_toru, nazwa, liczba_wyscigow. Sortowanie: id_toru — rosnąco.' SELECT t.id_toru, t.nazwa, COUNT(w.id_wyscigu) AS liczba_wyscigow FROM tor AS t LEFT JOIN wyscig AS w ON w.id_toru = t.id_toru AND w.data_wyscigu >= DATE '2026-01-01' AND w.data_wyscigu < DATE '2027-01-01' GROUP BY t.id_toru, t.nazwa ORDER BY t.id_toru; \echo 'Zadanie 5. Regularnie wysoko na mecie. Proszę wypisać kierowców, którzy ukończyli co najmniej cztery wyścigi w 2026 roku, a średnia ich pozycji na mecie w ukończonych wyścigach tego roku wynosi co najwyżej 6. Starty ze statusem DNF lub DSQ proszę pominąć przy obliczaniu liczby ukończonych wyścigów i średniej. Każdy ukończony wyścig ma taką samą wagę. Warunek dotyczący średniej należy sprawdzić przed jej ewentualnym zaokrągleniem. Kolumny: id_kierowcy, imie, nazwisko, liczba_ukonczonych, srednia_pozycja. Sortowanie: id_kierowcy — rosnąco.' SELECT k.id_kierowcy, k.imie, k.nazwisko, COUNT(s.pozycja_meta) AS liczba_ukonczonych, ROUND(AVG(s.pozycja_meta), 2) AS srednia_pozycja FROM kierowca AS k JOIN start AS s ON s.id_kierowcy = k.id_kierowcy JOIN wyscig AS w ON w.id_wyscigu = s.id_wyscigu WHERE w.data_wyscigu >= DATE '2026-01-01' AND w.data_wyscigu < DATE '2027-01-01' AND s.status = 'UKONCZYL' GROUP BY k.id_kierowcy, k.imie, k.nazwisko HAVING COUNT(s.pozycja_meta) >= 4 AND AVG(s.pozycja_meta) <= 6 ORDER BY k.id_kierowcy; \echo 'Zadanie 6. Bez ukończonego wyścigu. Proszę wypisać kierowców, którzy nie ukończyli ani jednego wyścigu w 2026 roku. Należy uwzględnić zarówno kierowców mających w tym roku wyłącznie starty ze statusem DNF lub DSQ, jak i kierowców bez startów w 2026 roku. Ukończenie wyścigu w innym roku nie wyklucza kierowcy z wyniku. Kolumny: id_kierowcy, imie, nazwisko. Sortowanie: id_kierowcy — rosnąco.' SELECT k.id_kierowcy, k.imie, k.nazwisko FROM kierowca AS k WHERE NOT EXISTS ( SELECT 1 FROM start AS s JOIN wyscig AS w ON w.id_wyscigu = s.id_wyscigu WHERE s.id_kierowcy = k.id_kierowcy AND s.status = 'UKONCZYL' AND w.data_wyscigu >= DATE '2026-01-01' AND w.data_wyscigu < DATE '2027-01-01' ) ORDER BY k.id_kierowcy; \echo 'Zadanie 7. Wynik lepszy od własnej średniej. Proszę wypisać starty z 2026 roku, w których pozycja kierowcy na mecie była mniejsza niż średnia jego pozycji na mecie we wszystkich ukończonych przez niego wyścigach 2026 roku. Do średniej należy wliczyć także badany start. Starty DNF i DSQ pomijamy w średniej i w wyniku. Mniejszy numer pozycji oznacza lepszy wynik. Kolumny: id_wyscigu, id_kierowcy, pozycja_meta. Sortowanie: id_wyscigu, id_kierowcy — rosnąco.' SELECT s.id_wyscigu, s.id_kierowcy, s.pozycja_meta FROM start AS s JOIN wyscig AS w ON w.id_wyscigu = s.id_wyscigu WHERE w.data_wyscigu >= DATE '2026-01-01' AND w.data_wyscigu < DATE '2027-01-01' AND s.pozycja_meta < ( SELECT AVG(s2.pozycja_meta) FROM start AS s2 JOIN wyscig AS w2 ON w2.id_wyscigu = s2.id_wyscigu WHERE s2.id_kierowcy = s.id_kierowcy AND w2.data_wyscigu >= DATE '2026-01-01' AND w2.data_wyscigu < DATE '2027-01-01' ) ORDER BY s.id_wyscigu, s.id_kierowcy; \echo 'Zadanie 8. Bilans zespołów. Dla każdego zespołu proszę podać liczbę startów jego kierowców w 2026 roku, liczbę różnych kierowców, którzy reprezentowali go w tych startach, oraz sumę zdobytych w nich punktów. Jeden wiersz tabeli start oznacza jeden start jednego kierowcy; nie liczymy tutaj liczby różnych wyścigów zespołu. Należy uwzględnić wszystkie statusy startu oraz wszystkie zespoły. Zespół bez startów w 2026 roku powinien otrzymać trzy zera. Zmiana zespołu przez kierowcę między wyścigami jest dopuszczalna. Kolumny: id_zespolu, nazwa, liczba_startow, liczba_kierowcow, suma_punktow. Sortowanie: id_zespolu — rosnąco.' WITH starty_2026 AS ( SELECT s.id_zespolu, s.id_kierowcy, s.punkty FROM start AS s JOIN wyscig AS w ON w.id_wyscigu = s.id_wyscigu WHERE w.data_wyscigu >= DATE '2026-01-01' AND w.data_wyscigu < DATE '2027-01-01' ) SELECT z.id_zespolu, z.nazwa, COUNT(s.id_kierowcy) AS liczba_startow, COUNT(DISTINCT s.id_kierowcy) AS liczba_kierowcow, COALESCE(SUM(s.punkty), 0) AS suma_punktow FROM zespol AS z LEFT JOIN starty_2026 AS s ON s.id_zespolu = z.id_zespolu GROUP BY z.id_zespolu, z.nazwa ORDER BY z.id_zespolu; \echo 'Zadanie 9. Najwięcej punktów na każdym torze. Dla każdego toru, na którym istnieje co najmniej jeden start w wyścigu z 2026 roku, proszę wypisać kierowców mających największą sumę punktów ze wszystkich swoich startów na tym torze w 2026 roku. Należy uwzględnić wszystkich remisujących. Porównujemy wyłącznie kierowców, którzy na danym torze wystartowali w tym roku. Jeżeli największa suma wynosi 0, również należy wypisać wszystkich kierowców osiągających to maksimum. Tory bez takich startów należy pominąć. Kolumny: id_toru, nazwa_toru, id_kierowcy, imie, nazwisko, suma_punktow. Sortowanie: id_toru, id_kierowcy — rosnąco.' WITH punkty_na_torze AS ( SELECT w.id_toru, s.id_kierowcy, SUM(s.punkty) AS suma_punktow FROM start AS s JOIN wyscig AS w ON w.id_wyscigu = s.id_wyscigu WHERE w.data_wyscigu >= DATE '2026-01-01' AND w.data_wyscigu < DATE '2027-01-01' GROUP BY w.id_toru, s.id_kierowcy ) SELECT t.id_toru, t.nazwa AS nazwa_toru, k.id_kierowcy, k.imie, k.nazwisko, p.suma_punktow FROM punkty_na_torze AS p JOIN tor AS t ON t.id_toru = p.id_toru JOIN kierowca AS k ON k.id_kierowcy = p.id_kierowcy WHERE NOT EXISTS ( SELECT 1 FROM punkty_na_torze AS p2 WHERE p2.id_toru = p.id_toru AND p2.suma_punktow > p.suma_punktow ) ORDER BY t.id_toru, k.id_kierowcy; \echo 'Zadanie 10. Punkty w każdym wyścigu na torze. Proszę wypisać kierowców, którzy zdobyli co najmniej 1 punkt w każdym wyścigu rozegranym w 2026 roku na torze o identyfikatorze 1. Punkty zdobyte w innych wyścigach nie zastępują brakującego punktowanego startu. Jeżeli na torze 1 nie ma żadnego wyścigu z 2026 roku, warunek spełniają wszyscy kierowcy; dotyczy to także sytuacji, gdy tor 1 nie istnieje. Kolumny: id_kierowcy, imie, nazwisko. Sortowanie: id_kierowcy — rosnąco.' SELECT k.id_kierowcy, k.imie, k.nazwisko FROM kierowca AS k WHERE NOT EXISTS ( SELECT 1 FROM wyscig AS w WHERE w.id_toru = 1 AND w.data_wyscigu >= DATE '2026-01-01' AND w.data_wyscigu < DATE '2027-01-01' AND NOT EXISTS ( SELECT 1 FROM start AS s WHERE s.id_wyscigu = w.id_wyscigu AND s.id_kierowcy = k.id_kierowcy AND s.punkty >= 1 ) ) ORDER BY k.id_kierowcy; \echo 'Zadanie 11. Dla chętnych (rekurencja, nieobowiązkowe). Poprzednicy zespołu. Korzystając z WITH RECURSIVE, proszę wypisać wszystkich bezpośrednich i pośrednich poprzedników zespołu o identyfikatorze 8. Nie należy wypisywać samego zespołu 8. Dla każdego poprzednika proszę podać odległość od zespołu 8 w liczbie krawędzi: bezpośredni poprzednik ma poziom 1. Kolumna id_poprzednika wskazuje poprzednika danego zespołu. Hierarchia nie zawiera cykli. Gdy zespół 8 nie ma poprzednika lub nie istnieje, wynik jest pusty. Kolumny: id_zespolu, nazwa, poziom. Sortowanie: poziom, id_zespolu — rosnąco.' WITH RECURSIVE poprzednicy AS ( SELECT p.id_zespolu, p.nazwa, p.id_poprzednika, 1 AS poziom FROM zespol AS z JOIN zespol AS p ON p.id_zespolu = z.id_poprzednika WHERE z.id_zespolu = 8 UNION ALL SELECT p.id_zespolu, p.nazwa, p.id_poprzednika, r.poziom + 1 FROM poprzednicy AS r JOIN zespol AS p ON p.id_zespolu = r.id_poprzednika ) SELECT id_zespolu, nazwa, poziom FROM poprzednicy ORDER BY poziom, id_zespolu; \echo 'Zadanie 12. Dla chętnych (rekurencja, nieobowiązkowe). Dorobek całej rodziny zespołów. Korzystając z WITH RECURSIVE, dla każdego zespołu proszę podać liczbę wszystkich jego bezpośrednich i pośrednich następców oraz sumę punktów zdobytych w 2026 roku przez ten zespół i wszystkich tych następców. Samego zespołu nie należy wliczać do liczby następców, ale należy uwzględnić jego punkty. Zespół N jest bezpośrednim następcą zespołu P, gdy N.id_poprzednika = P.id_zespolu. Należy uwzględnić wszystkie zespoły, również bez następców i bez startów. Brak następców oznacza licznik 0, a brak startów w całej rodzinie — sumę 0. W obrębie wyniku jednego zespołu każdy start należy policzyć dokładnie raz. Ten sam start może jednak zasilać wyniki różnych zespołów będących jego przodkami. Hierarchia jest lasem i nie zawiera cykli. Kolumny: id_zespolu, nazwa, liczba_nastepcow, suma_punktow_rodziny. Sortowanie: id_zespolu — rosnąco.' WITH RECURSIVE rodzina (id_przodka, id_czlonka) AS ( SELECT id_zespolu, id_zespolu FROM zespol UNION ALL SELECT r.id_przodka, z.id_zespolu FROM rodzina AS r JOIN zespol AS z ON z.id_poprzednika = r.id_czlonka ), punkty_zespolow AS ( SELECT s.id_zespolu, SUM(s.punkty) AS suma_punktow FROM start AS s JOIN wyscig AS w ON w.id_wyscigu = s.id_wyscigu WHERE w.data_wyscigu >= DATE '2026-01-01' AND w.data_wyscigu < DATE '2027-01-01' GROUP BY s.id_zespolu ) SELECT z.id_zespolu, z.nazwa, COUNT(*) - 1 AS liczba_nastepcow, COALESCE(SUM(p.suma_punktow), 0) AS suma_punktow_rodziny FROM zespol AS z JOIN rodzina AS r ON r.id_przodka = z.id_zespolu LEFT JOIN punkty_zespolow AS p ON p.id_zespolu = r.id_czlonka GROUP BY z.id_zespolu, z.nazwa ORDER BY z.id_zespolu;