Kolokwium SQL — Pracownicy i projekty

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.

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

Po zakończeniu ćwiczenia \i firma.drop.sql usuwa tabele zestawu wraz z danymi.

Schemat w skrócie

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.

Zadania podstawowe 1–10

1. Liczba projektów pracownika

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.

2. Najwięcej projektów

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.

3. Brak wpisu na liście obecności

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.

4. Kierownicy i uczestnicy projektu

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.

5. Pensja wyższa od pensji szefa

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.

6. Kierownik spoza zespołu

Wypisać id i nazwy projektów, które mają kierownika, ale ten kierownik nie figuruje wśród ich uczestników.

Kolumny: id, nazwa.

7. Równoległe projekty

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.

8. Nakładające się obecności

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.

9. Łączny czas obecności

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.

10. Najdłuższa przerwa w obecności

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.

Zadania dodatkowe 11–12: rekurencja

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

11. Co najmniej dwóch podwładnych

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.

12. Wielkość całego zespołu

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.