Kolokwium SQL — Piłkarze i drużyny

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.

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

Schemat w skrócie

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.

Zadania podstawowe 1–10

1. Zawodnicy Legii Warszawa

Wypisać nazwiska zawodników drużyny Legia Warszawa, po jednym wierszu na zawodnika.

Kolumny: nazwisko.

2. Piłkarze i ich drużyny

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.

3. Bez klubu lub bez zawodników

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.

4. Rozpiętość wycen w drużynie

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.

5. Najniższy numer koszulki

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.

6. Najliczniej obsadzona pozycja

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.

7. Najdrożsi piłkarze

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.

8. Ranking cen w drużynie

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.

9. Następny numer koszulki

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.

10. Wszystkie pozycje w składzie

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.

Zadania dodatkowe 11–12: rekurencja

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

11. Kluby połączone wspólnym zawodnikiem

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.

12. Odległość między klubami

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.

Wersja archiwalna

Pierwotna strona i oryginalne pliki (ZIP). Numeracja, dialekt SQL i zestaw danych w archiwum mogą różnić się od obecnej wersji.

Sprawdź poprawność XHTML 1.0 Strict


Powrót do spisu kolokwiów z SQL