Kolokwium SQL — Bitwy i wodzowie

Bazy danych · PostgreSQL · Autor pierwotnego kolokwium: Krzysztof Stencel.

Wodzowie dowodzą armiami, a w każdej zapisanej bitwie jedna armia zwycięża drugą. Państwo przypisano armii, nie wodzowi. Nazwy własne w danych zapisano bez polskich znakó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 bitwy.create.sql
\i bitwy.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 bitwy.drop.sql usuwa tabele zestawu wraz z danymi.

Schemat w skrócie

Zadania 1–10 zachowują tematykę pierwotnego kolokwium. Archiwalne rozwiązania opracowali Jan Bednarski (Oracle) i Magdalena Osełka (PostgreSQL).

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. Lata bitew armii krzyżackiej

Wypisać bez powtórzeń lata bitew, w których brała udział armia o nazwie „Panstwo krzyzackie”.

Kolumny: rok.

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.

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.

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.

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.

6. Spokojne lata XVII wieku

Wypisać wszystkie lata XVII wieku (1601–1700), w których nie stoczono żadnej zapisanej bitwy.

Kolumny: rok.

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.

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.

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.

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.

Zadania dodatkowe 11–12: rekurencja

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

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

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