Bazy danych · PostgreSQL · Autor pierwotnego kolokwium: Tomasz Waleń.
Baza opisuje hierarchię służbową w firmie, projekty, ich kierowników i uczestników oraz zarejestrowane wejścia do pracy i wyjścia z niej.
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 firma.create.sql \i firma.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 firma.drop.sql usuwa tabele zestawu wraz z danymi.
pracownicy(id, imie, nazwisko, pensja, szef) — id jest kluczem; szef wskazuje bezpośredniego przełożonego lub ma wartość NULL.listaobecnosci(pracownik, wejscie, wyjscie) — znaczniki czasu bez strefy; NULL w wyjscie oznacza niedomknięty wpis. Wpisy mogą się pokrywać i powtarzać.projekty(id, nazwa, szef, rozpoczecie, zakonczenie) — id jest kluczem; szef wskazuje kierownika projektu, nie musi być uczestnikiem. Obie daty należą do okresu projektu.uczestnicyprojektow(projekt, pracownik) — para kluczy obcych stanowi klucz główny.Rozwiązania wzorcowe zadań o czasie obecności korzystają z typów wieloprzedziałowych PostgreSQL 14 lub nowszego. Obecność traktujemy jako przedział od wejścia włącznie do wyjścia wyłącznie.
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.
Dla każdego pracownika wypisać id, imię, nazwisko, pensję i liczbę projektów, w których jest lub był uczestnikiem. Uwzględnić osoby bez projektów. Udział oznacza wpis w uczestnicyprojektow, a nie samo kierowanie projektem.
Kolumny: id, imie, nazwisko, pensja, ile_projektow.
Wypisać id, imię i nazwisko wszystkich pracowników uczestniczących w największej liczbie projektów. Jeśli żaden nie bierze udziału w żadnym projekcie, wypisać wszystkich pracowników.
Kolumny: id, imie, nazwisko.
Wypisać id, imię i nazwisko osób, które nie mają ani jednego wpisu na liście obecności. Samo zarejestrowane wejście jest wpisem, nawet gdy brakuje godziny wyjścia.
Kolumny: id, imie, nazwisko.
Dla każdego projektu wypisać id, nazwę, nazwisko jego kierownika i liczbę uczestników. Projekt bez kierownika lub bez uczestników również ma się pojawić.
Kolumny: id, nazwa, kierownik, uczestnicy.
Wypisać pracowników zarabiających więcej od swojego bezpośredniego szefa: id pracownika, nazwisko pracownika, nazwisko szefa i różnicę pensji. Pominąć osoby bez szefa.
Kolumny: id, nazwisko, szef, roznica.
Wypisać id i nazwy projektów, które mają kierownika, ale ten kierownik nie figuruje wśród ich uczestników.
Kolumny: id, nazwa.
Wypisać id i nazwiska pracowników, którzy uczestniczą w przynajmniej dwóch projektach czynnych tego samego dnia. Data rozpoczęcia i data zakończenia należą do okresu projektu. Każdego pracownika podać raz.
Kolumny: id, nazwisko.
Wypisać id, imię i nazwisko pracowników, którzy mają co najmniej dwa zakończone wpisy obecności nakładające się przez czas dłuższy od zera. Dwa identyczne wpisy także liczą się jako dwa; samo zetknięcie końców przedziałów nie wystarcza.
Kolumny: id, imie, nazwisko.
Dla każdego pracownika wypisać id, imię, nazwisko i łączną liczbę godzin zakończonej obecności (z częścią ułamkową). Nie zaokrąglać do pełnych godzin; czas pokrywających się wpisów liczyć tylko raz. Osoby bez zakończonych wpisów mają wynik 0.
Kolumny: id, imie, nazwisko, godziny.
Rozpatrzyć całe lata 2000 i 2001: od północy 1.01.2000 włącznie do północy 1.01.2002 wyłącznie. Wypisać początek i koniec najdłuższej przerwy bez żadnej zakończonej obecności. Wpisy bez wyjścia pominąć, przy remisie wypisać wszystkie przerwy. Jeśli nie było żadnej przerwy, wynik jest pusty.
Kolumny: poczatek, koniec.
Oba zadania są nieobowiązkowe. Każde jest warte 1 punkt dodatkowy. Proszę użyć WITH RECURSIVE.
Używając rekurencji, wypisać id, imię i nazwisko pracowników, którzy mają co najmniej dwóch różnych podwładnych, pośrednich lub bezpośrednich. Szef nie jest własnym podwładnym.
Kolumny: id, imie, nazwisko.
Dla każdego pracownika wypisać id, imię, nazwisko i liczbę wszystkich różnych podwładnych, zarówno bezpośrednich, jak i pośrednich. Wyznaczyć zależność rekurencyjnie. Wypisać również pracowników z wynikiem 0 i nie liczyć pracownika jako własnego podwładnego.
Kolumny: id, imie, nazwisko, podwladni.
Pierwotna strona i oryginalne pliki (ZIP). Numeracja, dialekt SQL i zestaw danych w archiwum mogą różnić się od obecnej wersji.