Bazy danych · PostgreSQL · Autor pierwotnego kolokwium: Oskar Skibski.
Składy drużyn, numery koszulek, pozycje i wyceny graczy. Zestawienie nie rozróżnia sezonów: ten sam piłkarz może występować w kilku składach.
Zadania podstawowe 1–10 są warte po 1 punkcie. Zadania 11–12 z rekurencji są dla chętnych — po 1 punkcie dodatkowym.
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 pilkarze.create.sql \i pilkarze.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
Wzorzec uruchamia się poleceniem \i rozwiazanie.sql. Po zakończeniu ćwiczenia \i pilkarze.drop.sql usuwa tabele zestawu wraz z danymi.
pilkarz(id, imie, nazwisko, pozycja) — klucz: id; pozycja może być nieznana.druzyna(skrot, nazwa) — klucz: skrot.sklad(idpilkarza, skrotdruz, numer, cena) — kluczem jest para (idpilkarza, skrotdruz). Numer jest unikatowy w obrębie drużyny; cena może być nieznana.Autorką pierwotnych przykładowych rozwiązań była Martyna Maciejewska. Dane mają charakter ćwiczeniowy; przynależność do kilku składów nie określa kolejności transferów.
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.
Wypisać nazwiska zawodników drużyny Legia Warszawa, po jednym wierszu na zawodnika.
Kolumny: nazwisko.
Wypisać imiona i nazwiska wszystkich piłkarzy, a przy nich skróty i nazwy ich drużyn. Jeśli piłkarz należy do kilku składów, podać osobny wiersz dla każdego z nich. Uwzględnić osoby bez drużyny.
Kolumny: imie, nazwisko, skrot, nazwa.
Wypisać jedną kolumnę nieprzydzieleni: nazwy drużyn bez zawodników oraz imiona piłkarzy bez drużyny. Każdą taką drużynę i osobę uwzględnić raz, nawet jeśli mają identyczną nazwę lub imię.
Kolumny: nieprzydzieleni.
Wypisać skrót i nazwę każdej drużyny, w której różnica między najwyższą i najniższą znaną ceną jest ściśle mniejsza niż 1000. Pominąć drużyny bez żadnej znanej ceny.
Kolumny: skrot, nazwa.
Wyznaczyć najmniejszy numer koszulki w całej bazie. Dla każdej drużyny, w której ktoś gra z tym numerem, wypisać skrót drużyny i wszystkich zawodników z jej składu (id, imię i nazwisko).
Kolumny: skrotdruz, id, imie, nazwisko.
Dla każdej drużyny i każdej znanej pozycji policzyć ilu jej zawodników gra na tej pozycji. Wypisać wszystkie pary (skrót drużyny, pozycja) o największej dodatniej liczebności oraz tę liczebność.
Kolumny: skrotdruz, pozycja, ilu.
Wypisać id, imię i nazwisko każdego piłkarza, który w co najmniej jednym składzie osiąga najwyższą znaną cenę w całej bazie. Uwzględnić remisy, każdego piłkarza wypisać raz.
Kolumny: id, imie, nazwisko.
Dla każdej znanej wyceny wypisać imię i nazwisko piłkarza, skrót drużyny, cenę i miejsce w rankingu cen w tej drużynie. Najwyższa cena ma miejsce 1, równe ceny mają to samo miejsce, a kolejne miejsca nie mają luk. Posortować według drużyny, miejsca i id zawodnika.
Kolumny: imie, nazwisko, skrotdruz, cena, miejsce.
Wypisać każdego piłkarza (id, imię, nazwisko), drużynę, jego numer i najmniejszy większy numer w tej samej drużynie. Ostatni numer nie ma następnika. Piłkarza bez drużyny uwzględnić z wartościami NULL; piłkarza kilku drużyn — osobno dla każdej.
Kolumny: id, imie, nazwisko, skrotdruz, numer, nastepny.
Wypisać skróty i nazwy drużyn, które mają w składzie co najmniej jednego piłkarza na każdej znanej pozycji występującej w tabeli pilkarz. Wartości NULL nie traktować jako pozycji.
Kolumny: skrot, nazwa.
Oba zadania są nieobowiązkowe. Każde jest warte 1 punkt dodatkowy. Proszę użyć WITH RECURSIVE.
Dwa różne kluby są połączone, jeśli mają w bazie wspólnego piłkarza. Używając rekurencji, wypisać skróty i nazwy wszystkich klubów osiągalnych z LW przez dowolną liczbę takich połączeń (w tym LW). Każdy klub wypisać raz.
Kolumny: skrot, nazwa.
Przy tej samej definicji połączenia wyznaczyć rekurencyjnie minimalną liczbę przejść z LW do każdego klubu. Wypisać skrót klubu i odległość: 0 dla LW i NULL dla klubów nieosiągalnych. Nie wolno krążyć w cyklu.
Kolumny: skrot, odleglosc.
Pierwotna strona i oryginalne pliki (ZIP). Numeracja, dialekt SQL i zestaw danych w archiwum mogą różnić się od obecnej wersji.