Kolokwium z baz danych: Bokserzy

Autor pierwotnych zadań: Jacek Sroka.

Wersja PostgreSQL z przykładowymi danymi i doprecyzowanymi poleceniami.

Materiały

Po pobraniu plików, w sesji psql we własnym schemacie, należy wykonać:

\encoding UTF8
\set ON_ERROR_STOP on
\i boks.create.sql
\i boks.insert.sql

Założenia

Zadania

Zestaw zawiera 15 zadań. W zadaniach 9–12 należy rozwiązać ten sam problem czterema różnymi metodami.

  1. Niepokonani w aktualnej kategorii. Wypisać nazwiska bokserów, którzy nie przegrali żadnej walki w swojej aktualnej kategorii wagowej. Aktualną kategorię wyznacza obecna waga boksera. Uwzględnić również bokserów bez walk oraz tych, którzy przegrywali wyłącznie w innych kategoriach.

    Kolumny: nazwisko. Porządek: nazwisko, id boksera.

  2. Puste kategorie. Wypisać kategorie wagowe, do których nie należy obecnie żaden bokser.

    Kolumny: id, nazwa. Porządek: id.

  3. Nieobsadzone wagi. Wypisać wszystkie całkowite wagi w funtach z zakresu od 1 do 499 włącznie, których nie ma obecnie żaden bokser. Chodzi o wartości wagi, a nie nazwy kategorii.

    Kolumny: waga. Porządek: waga.

  4. Raport klubów i kategorii. Dla każdego klubu wypisać liczbę jego bokserów w każdej z trzech aktualnych kategorii: piórkowej, ciężkiej i superciężkiej. Każdy klub ma zajmować jeden wiersz, a każda z tych kategorii osobną kolumnę. Uwzględnić także kluby bez bokserów i podać zera w nieobsadzonych kategoriach. Bokserów niezrzeszonych pominąć.

    Kolumny: id_klubu, nazwa_klubu, piorkowa, ciezka, superciezka. Porządek: id_klubu.

  5. Wyżsi od średniej. Wypisać nazwisko i wzrost boksera, nazwę jego aktualnej kategorii wagowej oraz średni wzrost bokserów w tej kategorii, jeżeli jest on wyższy od tej średniej. Średnią liczyć ze wszystkich obecnych członków kategorii, także z badanego boksera i osób niezrzeszonych.

    Kolumny: nazwisko, wzrost, kategoria, sredni_wzrost. Porządek: nazwisko, id boksera.

  6. Najwięcej zwycięstw klubu. Wypisać maksymalną łączną liczbę zwycięstw jednego klubu. Do dorobku klubu zaliczyć wszystkie zapisane zwycięstwa bokserów, którzy obecnie do niego należą. Chodzi o sumę zwycięstw całego klubu. Nie trzeba podawać, który to klub; bokserów niezrzeszonych pominąć. Jeśli żaden klub nie ma zwycięstwa, podać 0.

    Kolumny: maks_zwyciestw. Porządek: nie dotyczy — jeden wiersz.

  7. Najlepszy wynik zawodnika w klubie. Dla każdego klubu wypisać maksymalną liczbę zwycięstw jednego jego obecnego boksera. Uwzględnić wszystkie kluby. Dla klubu bez bokserów lub bez zwycięstw podać 0.

    Kolumny: id_klubu, maks_zwyciestw. Porządek: id_klubu.

  8. Także bokserzy niezrzeszeni. Rozwiązać zadanie 7, dodając jeden wiersz z maksymalną liczbą zwycięstw jednego boksera niezrzeszonego. W tym wierszu id_klubu ma być NULL. Wiersz ten należy wypisać wtedy i tylko wtedy, gdy istnieje co najmniej jeden bokser niezrzeszony. Nie wolno użyć UNION ani UNION ALL.

    Kolumny: id_klubu, maks_zwyciestw. Porządek: id_klubu, z NULL na końcu.

  9. Kluby bez bokserów — pierwszy sposób. Wypisać nazwy klubów, do których nie należy żaden bokser. Klub mający bokserów, którzy jeszcze nie walczyli, nie spełnia tego warunku.

    Kolumny: nazwa. Porządek: nazwa.

  10. Kluby bez bokserów — drugi sposób. Rozwiązać zadanie 9 inną metodą niż poprzednio.

    Kolumny: nazwa. Porządek: nazwa.

  11. Kluby bez bokserów — trzeci sposób. Rozwiązać zadanie 9 trzecią metodą, inną niż użyte w zadaniach 9 i 10.

    Kolumny: nazwa. Porządek: nazwa.

  12. Kluby bez bokserów — czwarty sposób. Rozwiązać zadanie 9 czwartą metodą, inną niż użyte w zadaniach 9–11.

    Kolumny: nazwa. Porządek: nazwa.

  13. Pięciu najcięższych. Wypisać pięciu najcięższych bokserów. Przy równej wadze pierwszeństwo ma nazwisko wcześniejsze alfabetycznie, a przy równych nazwiskach mniejszy identyfikator. Jeśli w bazie jest mniej niż pięciu bokserów, wypisać wszystkich.

    Kolumny: id, nazwisko, waga. Porządek: waga malejąco, nazwisko rosnąco, id rosnąco.

  14. Środkowy bokser. Wypisać nazwisko i wzrost boksera, od którego wyższych jest dokładnie tyle samo bokserów, co niższych, i nie ma żadnego innego boksera o takim samym wzroście. Liczyć osoby, a więc uwzględniać powtórzenia wzrostów innych bokserów. Jeżeli nie ma takiej osoby, wynik ma być pusty.

    Kolumny: nazwisko, wzrost. Porządek: nazwisko, id boksera.

  15. Środkowa wartość wzrostu. Uporządkować różne wartości wzrostu występujące w bazie i znaleźć wartość dokładnie środkową. Wypisać nazwisko i wzrost każdego boksera mającego taki wzrost. Przy szukaniu środka nie liczyć powtórzeń wartości. Jeżeli liczba różnych wzrostów jest parzysta albo nie ma bokserów, wynik ma być pusty; nie obliczać średniej dwóch środkowych wartości.

    Kolumny: nazwisko, wzrost. Porządek: nazwisko, id boksera.

Valid XHTML 1.0 Strict


Kolokwia (SQL)