-- Kolokwium SQL — Pracownicy i projekty (PostgreSQL, psql). -- Wzorcowe rozwiązania. -- Zadania 1–10: po 1 punkcie. Zadania 11–12 z rekurencji są NIEOBOWIĄZKOWE, po 1 punkcie dodatkowym. \encoding UTF8 \set ON_ERROR_STOP on \pset pager off \pset null '(NULL)' \echo 'Zadanie 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.' SELECT p.id, p.imie, p.nazwisko, p.pensja, COUNT(u.projekt) AS ile_projektow FROM pracownicy p LEFT JOIN uczestnicyprojektow u ON u.pracownik = p.id GROUP BY p.id ORDER BY p.id; \echo 'Zadanie 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.' WITH liczby AS ( SELECT p.id, COUNT(u.projekt) AS n FROM pracownicy p LEFT JOIN uczestnicyprojektow u ON u.pracownik = p.id GROUP BY p.id ) SELECT p.id, p.imie, p.nazwisko FROM pracownicy p JOIN liczby l USING (id) WHERE l.n = (SELECT MAX(n) FROM liczby) ORDER BY p.id; \echo 'Zadanie 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.' SELECT p.id, p.imie, p.nazwisko FROM pracownicy p WHERE NOT EXISTS (SELECT FROM listaobecnosci l WHERE l.pracownik = p.id) ORDER BY p.id; \echo 'Zadanie 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.' SELECT j.id, j.nazwa, p.nazwisko AS kierownik, COUNT(u.pracownik) AS uczestnicy FROM projekty j LEFT JOIN pracownicy p ON p.id = j.szef LEFT JOIN uczestnicyprojektow u ON u.projekt = j.id GROUP BY j.id, p.nazwisko ORDER BY j.id; \echo 'Zadanie 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.' SELECT p.id, p.nazwisko, s.nazwisko AS szef, p.pensja - s.pensja AS roznica FROM pracownicy p JOIN pracownicy s ON s.id = p.szef WHERE p.pensja > s.pensja ORDER BY p.id; \echo 'Zadanie 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.' SELECT j.id, j.nazwa FROM projekty j WHERE j.szef IS NOT NULL AND NOT EXISTS ( SELECT FROM uczestnicyprojektow u WHERE u.projekt = j.id AND u.pracownik = j.szef ) ORDER BY j.id; \echo 'Zadanie 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.' SELECT p.id, p.nazwisko FROM pracownicy p WHERE EXISTS ( SELECT FROM uczestnicyprojektow a JOIN projekty x ON x.id = a.projekt JOIN uczestnicyprojektow b ON b.pracownik = a.pracownik AND b.projekt > a.projekt JOIN projekty y ON y.id = b.projekt WHERE a.pracownik = p.id AND x.rozpoczecie <= y.zakonczenie AND y.rozpoczecie <= x.zakonczenie ) ORDER BY p.id; \echo 'Zadanie 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.' WITH wpisy AS ( SELECT pracownik, wejscie, MAX(wyjscie) OVER (PARTITION BY pracownik ORDER BY wejscie, wyjscie ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING) AS poprzedni_koniec FROM listaobecnosci WHERE wyjscie IS NOT NULL ) SELECT p.id, p.imie, p.nazwisko FROM pracownicy p WHERE EXISTS (SELECT FROM wpisy w WHERE w.pracownik = p.id AND w.wejscie < w.poprzedni_koniec) ORDER BY p.id; \echo 'Zadanie 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.' WITH zakresy AS ( SELECT pracownik, range_agg(tsrange(wejscie, wyjscie, '[)')) AS czas FROM listaobecnosci WHERE wyjscie IS NOT NULL GROUP BY pracownik ), godziny AS ( SELECT z.pracownik, SUM(EXTRACT(EPOCH FROM (upper(r) - lower(r)))) / 3600 AS ile FROM zakresy z CROSS JOIN LATERAL unnest(z.czas) AS czesc(r) GROUP BY z.pracownik ) SELECT p.id, p.imie, p.nazwisko, COALESCE(g.ile, 0) AS godziny FROM pracownicy p LEFT JOIN godziny g ON g.pracownik = p.id ORDER BY p.id; \echo 'Zadanie 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.' WITH okno AS ( SELECT tsrange(TIMESTAMP '2000-01-01', TIMESTAMP '2002-01-01', '[)') AS okres ), obecnosci AS ( SELECT range_agg(tsrange(wejscie, wyjscie, '[)')) AS zajete FROM listaobecnosci WHERE wyjscie IS NOT NULL ), przerwy AS ( SELECT lower(r) AS poczatek, upper(r) AS koniec FROM okno CROSS JOIN obecnosci CROSS JOIN LATERAL unnest(tsmultirange(okres) - COALESCE(zajete, '{}'::tsmultirange)) AS czesc(r) ) SELECT poczatek, koniec FROM przerwy WHERE koniec - poczatek = (SELECT MAX(koniec - poczatek) FROM przerwy) ORDER BY poczatek; \echo 'Zadanie 11. Dla chętnych (rekurencja, nieobowiązkowe). 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.' WITH RECURSIVE podleglosc(szef, podwladny) AS ( SELECT szef, id FROM pracownicy WHERE szef IS NOT NULL UNION SELECT h.szef, p.id FROM podleglosc h JOIN pracownicy p ON p.szef = h.podwladny ) SELECT p.id, p.imie, p.nazwisko FROM pracownicy p JOIN podleglosc h ON h.szef = p.id AND h.podwladny <> p.id GROUP BY p.id HAVING COUNT(*) >= 2 ORDER BY p.id; \echo 'Zadanie 12. Dla chętnych (rekurencja, nieobowiązkowe). 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.' WITH RECURSIVE podleglosc(szef, podwladny) AS ( SELECT szef, id FROM pracownicy WHERE szef IS NOT NULL UNION SELECT h.szef, p.id FROM podleglosc h JOIN pracownicy p ON p.szef = h.podwladny ) SELECT p.id, p.imie, p.nazwisko, COUNT(h.podwladny) AS podwladni FROM pracownicy p LEFT JOIN podleglosc h ON h.szef = p.id AND h.podwladny <> p.id GROUP BY p.id ORDER BY p.id;