-- Kolokwium SQL — Bitwy i wodzowie (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. Lata bitew armii krzyżackiej. Wypisać bez powtórzeń lata bitew, w których brała udział armia o nazwie „Panstwo krzyzackie”. Kolumny: rok.' SELECT DISTINCT rok FROM bitwy WHERE 'Panstwo krzyzackie' IN (zwyciezca, pokonany) ORDER BY rok; \echo 'Zadanie 2. Zwycięstwo mniej krwawego wodza. Wypisać różne pary (miejsce, rok) dla bitew, w których mniej krwawy wódz pokonał bardziej krwawego. Kolumny: miejsce, rok.' SELECT DISTINCT b.miejsce, b.rok FROM bitwy b JOIN armia z ON z.nazwa = b.zwyciezca JOIN armia p ON p.nazwa = b.pokonany JOIN wodzowie wz ON wz.imie = z.dowodca JOIN wodzowie wp ON wp.imie = p.dowodca WHERE wz.krwawosc < wp.krwawosc ORDER BY b.rok, b.miejsce; \echo 'Zadanie 3. Wodzowie walczący w 1410 roku. Wypisać bez powtórzeń imiona wodzów, którzy walczyli w roku 1410, niezależnie od wyniku bitwy. Kolumny: dowodca.' SELECT DISTINCT a.dowodca FROM bitwy b JOIN armia a ON a.nazwa IN (b.zwyciezca, b.pokonany) WHERE b.rok = 1410 ORDER BY a.dowodca; \echo 'Zadanie 4. Liczba bitew każdego wodza. Dla każdego wodza podać imię, krwawość i liczbę stoczonych bitew (także zero). Tę samą bitwę policzyć jednemu wodzowi tylko raz, nawet gdy dowodził obiema armiami. Kolumny: imie, krwawosc, liczba_bitew.' SELECT w.imie, w.krwawosc, (SELECT COUNT(*) FROM bitwy b WHERE EXISTS (SELECT 1 FROM armia a WHERE a.dowodca = w.imie AND a.nazwa IN (b.zwyciezca, b.pokonany))) AS liczba_bitew FROM wodzowie w ORDER BY w.imie; \echo 'Zadanie 5. Wodzowie bez bitew. Wypisać imiona wodzów, którzy nigdy nie dowodzili armią w żadnej zapisanej bitwie. Uwzględnić zarówno wodzów bez armii, jak i tych, których armie nigdy nie walczyły. Kolumny: imie.' SELECT w.imie FROM wodzowie w WHERE NOT EXISTS ( SELECT 1 FROM armia a JOIN bitwy b ON a.nazwa IN (b.zwyciezca, b.pokonany) WHERE a.dowodca = w.imie ) ORDER BY w.imie; \echo 'Zadanie 6. Spokojne lata XVII wieku. Wypisać wszystkie lata XVII wieku (1601–1700), w których nie stoczono żadnej zapisanej bitwy. Kolumny: rok.' SELECT lata.rok FROM generate_series(1601, 1700) AS lata(rok) WHERE NOT EXISTS (SELECT 1 FROM bitwy b WHERE b.rok = lata.rok) ORDER BY lata.rok; \echo 'Zadanie 7. Najwięcej wygranych bitew. Wśród wodzów z co najmniej jednym zwycięstwem znaleźć tych, którzy wygrali najwięcej bitew. Podać ich imiona; uwzględnić remisy. Kolumny: dowodca.' WITH wygrane AS ( SELECT a.dowodca, COUNT(*) AS ile FROM bitwy b JOIN armia a ON a.nazwa = b.zwyciezca GROUP BY a.dowodca ) SELECT dowodca FROM wygrane WHERE ile = (SELECT MAX(ile) FROM wygrane) ORDER BY dowodca; \echo 'Zadanie 8. Najaktywniejsi wodzowie państw. Dla każdego państwa wystawiającego armię w XVII wieku (1601–1700) wypisać państwo, imię, krwawość i liczbę bitew wodza, który stoczył dla niego najwięcej bitew w tym wieku. Uwzględnić remisy; tę samą bitwę policzyć tylko raz danemu wodzowi w danym państwie. Brać pod uwagę tylko państwa i wodzów uczestniczących w tych bitwach. Kolumny: panstwo, dowodca, krwawosc, liczba_bitew.' WITH udzial AS ( SELECT DISTINCT a.panstwo, a.dowodca, b.zwyciezca, b.pokonany, b.rok FROM bitwy b JOIN armia a ON a.nazwa IN (b.zwyciezca, b.pokonany) WHERE b.rok BETWEEN 1601 AND 1700 ), wyniki AS ( SELECT panstwo, dowodca, COUNT(*) AS liczba_bitew, RANK() OVER (PARTITION BY panstwo ORDER BY COUNT(*) DESC) AS pozycja FROM udzial GROUP BY panstwo, dowodca ) SELECT r.panstwo, r.dowodca, w.krwawosc, r.liczba_bitew FROM wyniki r JOIN wodzowie w ON w.imie = r.dowodca WHERE r.pozycja = 1 ORDER BY r.panstwo, r.dowodca; \echo 'Zadanie 9. Wodzowie państw Miltiadesa. Wypisać bez powtórzeń imiona innych niż Miltiades wodzów, którzy dowodzili armią tego samego państwa co przynajmniej jedna z armii Miltiadesa. Narodowości osób nie przechowujemy. Kolumny: dowodca.' SELECT DISTINCT a.dowodca FROM armia a WHERE a.dowodca <> 'Miltiades' AND EXISTS (SELECT 1 FROM armia m WHERE m.dowodca = 'Miltiades' AND m.panstwo = a.panstwo) ORDER BY a.dowodca; \echo 'Zadanie 10. Mediana krwawości pod Grunwaldem. Obliczyć medianę krwawości wodzów, którzy kiedykolwiek walczyli pod Grunwaldem. Każdą osobę policzyć raz; przy parzystej liczbie osób wziąć średnią dwóch wartości środkowych. Kolumny: mediana.' SELECT percentile_cont(0.5) WITHIN GROUP (ORDER BY w.krwawosc) AS mediana FROM wodzowie w WHERE EXISTS ( SELECT 1 FROM bitwy b JOIN armia a ON a.nazwa IN (b.zwyciezca, b.pokonany) WHERE b.miejsce = 'Grunwald' AND a.dowodca = w.imie ); \echo 'Zadanie 11. Dla chętnych (rekurencja, nieobowiązkowe). Graf zwycięstw armii. Utworzyć skierowany graf zwycięstw armii: krawędź A → B oznacza, że A pokonała B przynajmniej raz. Począwszy od „Turcja”, wypisać nazwy wszystkich innych armii osiągalnych po co najmniej jednej krawędzi. Lata bitew pominąć. Każdą armię wypisać raz; rozwiązanie ma kończyć się również przy cyklach. Kolumny: nazwa.' WITH RECURSIVE zasieg(nazwa) AS ( SELECT nazwa FROM armia WHERE nazwa = 'Turcja' UNION SELECT b.pokonany FROM zasieg z JOIN bitwy b ON b.zwyciezca = z.nazwa ) SELECT nazwa FROM zasieg WHERE nazwa <> 'Turcja' ORDER BY nazwa; \echo 'Zadanie 12. Dla chętnych (rekurencja, nieobowiązkowe). Chronologiczny łańcuch pogromców. Zbudować chronologiczne łańcuchy pogromców: A przegrała z B, a w ściśle późniejszym roku B przegrała z C itd. Dla każdego niepustego łańcucha o maksymalnej długości wypisać tablicę nazw kolejnych armii (od pierwszej pokonanej do ostatniej zwycięskiej), tablicę lat i liczbę bitew. Uwzględnić wszystkie remisy. Armia może powrócić do łańcucha. Kolumny: armie, lata, liczba_bitew.' WITH RECURSIVE lancuch(ostatnia, rok, armie, lata) AS ( SELECT b.zwyciezca, b.rok, ARRAY[b.pokonany::text, b.zwyciezca::text], ARRAY[b.rok] FROM bitwy b UNION ALL SELECT b.zwyciezca, b.rok, array_append(l.armie, b.zwyciezca::text), array_append(l.lata, b.rok) FROM lancuch l JOIN bitwy b ON b.pokonany = l.ostatnia AND b.rok > l.rok ) SELECT armie, lata, cardinality(lata) AS liczba_bitew FROM lancuch WHERE cardinality(lata) = (SELECT MAX(cardinality(lata)) FROM lancuch) ORDER BY armie, lata;