-- Kolokwium SQL — Genealogia (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. Czy można być własną babcią?. Znaleźć osoby, które według zapisanych powiązań są własnymi babciami: dana osoba jest matką któregoś ze swoich rodziców. To kontrola spójności danych; pusty wynik jest poprawny. Kolumny: id, imie, nazwisko.' SELECT o.id, o.imie, o.nazwisko FROM osoba o WHERE EXISTS ( SELECT 1 FROM osoba r WHERE r.id IN (o.id_matki, o.id_ojca) AND r.id_matki = o.id ) ORDER BY o.id; \echo 'Zadanie 2. Liczba wnuków. Dla każdej osoby podać jej dane i liczbę różnych wnuków, czyli osób oddalonych o dokładnie dwa kroki rodzic → dziecko. Uwzględnić osoby bez wnuków. Kolumny: id, imie, nazwisko, wnuki.' SELECT o.id, o.imie, o.nazwisko, COUNT(DISTINCT w.id) AS wnuki FROM osoba o LEFT JOIN osoba d ON o.id IN (d.id_matki, d.id_ojca) LEFT JOIN osoba w ON d.id IN (w.id_matki, w.id_ojca) GROUP BY o.id ORDER BY o.id; \echo 'Zadanie 3. Małżonkowie pod różnymi adresami. Znaleźć nieuporządkowane pary osób, dla których istniał dzień, gdy były małżeństwem i miały odnotowane dwa różne adresy (po jednym dla każdej osoby). Porównać pełny adres; dwa brakujące numery mieszkania uznać za takie same. Każdą parę wypisać raz, z danymi obu osób. Kolumny: id1, imie1, nazwisko1, id2, imie2, nazwisko2.' SELECT DISTINCT p.id AS id1, p.imie AS imie1, p.nazwisko AS nazwisko1, q.id AS id2, q.imie AS imie2, q.nazwisko AS nazwisko2 FROM malzenstwo m JOIN osoba p ON p.id = LEAST(m.id_zony, m.id_meza) JOIN osoba q ON q.id = GREATEST(m.id_zony, m.id_meza) JOIN adres a ON a.id_osoby = p.id JOIN adres b ON b.id_osoby = q.id WHERE GREATEST(m.data_poczatku, a.data_poczatku, b.data_poczatku) < LEAST(COALESCE(m.data_konca, 'infinity'::date), COALESCE(a.data_konca, 'infinity'::date), COALESCE(b.data_konca, 'infinity'::date)) AND ROW(a.ulica, a.nr_domu, a.nr_mieszkania, a.kod_pocztowy, a.miejscowosc) IS DISTINCT FROM ROW(b.ulica, b.nr_domu, b.nr_mieszkania, b.kod_pocztowy, b.miejscowosc) ORDER BY id1, id2; \echo 'Zadanie 4. Małżeństwo rodziców przed narodzinami. Ile osób o obojgu znanych rodzicach urodziło się poza ich małżeństwem i przez dziewięć poprzednich miesięcy rodzice także nie byli ze sobą małżeństwem? Sprawdzić okres od dnia urodzenia minus dziewięć miesięcy kalendarzowych aż do dnia urodzenia, włącznie z obydwoma dniami. Kolumny: liczba_osob.' SELECT COUNT(*) AS liczba_osob FROM osoba o WHERE o.id_matki IS NOT NULL AND o.id_ojca IS NOT NULL AND NOT EXISTS ( SELECT 1 FROM malzenstwo m WHERE (m.id_zony, m.id_meza) IN ((o.id_matki, o.id_ojca), (o.id_ojca, o.id_matki)) AND m.data_poczatku <= o.data_urodzenia AND (m.data_konca IS NULL OR m.data_konca > (o.data_urodzenia - INTERVAL '9 months')::date) ); \echo 'Zadanie 5. Nakładające się okresy ważności dowodów. Znaleźć osoby, dla których dwa różne dowody były ważne w tym samym dniu. Styk dwóch okresów na granicy nie jest nakładaniem się. Kolumny: id, imie, nazwisko.' SELECT o.id, o.imie, o.nazwisko FROM osoba o WHERE EXISTS ( SELECT 1 FROM dowod a JOIN dowod b ON a.id_osoby = b.id_osoby AND a.nr < b.nr WHERE a.id_osoby = o.id AND daterange(a.data_poczatku, a.data_konca, '[)') && daterange(b.data_poczatku, b.data_konca, '[)') ) ORDER BY o.id; \echo 'Zadanie 6. Termin wymiany dowodu. Na potrzeby ćwiczenia po zmianie adresu lub zawarciu małżeństwa trzeba uzyskać nowy dowód między dniem zdarzenia a 30. dniem po nim (włącznie). Znaleźć osoby, które choć raz nie dotrzymały terminu już zakończonego do dnia 1 października 2026 włącznie. Za zmianę adresu uznać początek kolejnego wpisu różniącego się pełnym adresem od poprzedniego; pierwszego wpisu nie liczyć. Kolumny: id, imie, nazwisko.' WITH kolejnosc AS ( SELECT a.*, LAG(data_poczatku) OVER (PARTITION BY id_osoby ORDER BY data_poczatku) AS poprzedni_od FROM adres a ), zdarzenia(id, dzien) AS ( SELECT k.id_osoby, k.data_poczatku FROM kolejnosc k JOIN adres a ON a.id_osoby = k.id_osoby AND a.data_poczatku = k.poprzedni_od WHERE ROW(k.ulica, k.nr_domu, k.nr_mieszkania, k.kod_pocztowy, k.miejscowosc) IS DISTINCT FROM ROW(a.ulica, a.nr_domu, a.nr_mieszkania, a.kod_pocztowy, a.miejscowosc) UNION SELECT id_zony, data_poczatku FROM malzenstwo UNION SELECT id_meza, data_poczatku FROM malzenstwo ) SELECT o.id, o.imie, o.nazwisko FROM osoba o WHERE EXISTS ( SELECT 1 FROM zdarzenia z WHERE z.id = o.id AND z.dzien + 30 <= DATE '2026-10-01' AND NOT EXISTS ( SELECT 1 FROM dowod d WHERE d.id_osoby = z.id AND d.data_poczatku BETWEEN z.dzien AND z.dzien + 30 ) ) ORDER BY o.id; \echo 'Zadanie 7. Małżeństwa według miejscowości. Dla każdej miejscowości występującej w adresach podać liczbę małżeństw, których oboje małżonkowie mieli w niej adres w dniu ślubu. Nie pomijać miejscowości z wynikiem zero. Małżeństwo liczyć w danej miejscowości tylko raz. Kolumny: miejscowosc, liczba_slubow.' WITH miejscowosci AS ( SELECT DISTINCT miejscowosc FROM adres ), sluby AS ( SELECT DISTINCT a.miejscowosc, m.id_zony, m.id_meza, m.data_poczatku FROM malzenstwo m JOIN adres a ON a.id_osoby = m.id_zony AND daterange(a.data_poczatku, a.data_konca, '[)') @> m.data_poczatku JOIN adres b ON b.id_osoby = m.id_meza AND b.miejscowosc = a.miejscowosc AND daterange(b.data_poczatku, b.data_konca, '[)') @> m.data_poczatku ) SELECT x.miejscowosc, COUNT(s.id_zony) AS liczba_slubow FROM miejscowosci x LEFT JOIN sluby s ON s.miejscowosc = x.miejscowosc GROUP BY x.miejscowosc ORDER BY x.miejscowosc; \echo 'Zadanie 8. Każde dziecko z inną matką. Znaleźć ojców, którzy każde dziecko mieli z inną matką. Ojciec musi mieć przynajmniej jedno dziecko, a matka każdego jego dziecka musi być znana. Kolumny: id, imie, nazwisko.' SELECT o.id, o.imie, o.nazwisko FROM osoba o JOIN osoba d ON d.id_ojca = o.id GROUP BY o.id HAVING COUNT(*) = COUNT(DISTINCT d.id_matki) ORDER BY o.id; \echo 'Zadanie 9. Dwie role rodzicielskie. Znaleźć osoby figurujące w ewidencji zarówno jako matka, jak i jako ojciec. Badamy wyłącznie role odnotowane w rekordach dzieci; w bazie nie ma historii płci osoby. Kolumny: id, imie, nazwisko.' SELECT o.id, o.imie, o.nazwisko FROM osoba o WHERE EXISTS (SELECT 1 FROM osoba d WHERE d.id_matki = o.id) AND EXISTS (SELECT 1 FROM osoba d WHERE d.id_ojca = o.id) ORDER BY o.id; \echo 'Zadanie 10. Najwięcej małżeństw wśród osób bez dzieci. Wśród osób bez zapisanych dzieci znaleźć te, które zawarły najwięcej małżeństw. Podać dane osób i liczbę małżeństw. Uwzględnić wszystkie remisy, także dla maksimum równego zero. Kolumny: id, imie, nazwisko, liczba.' WITH wyniki AS ( SELECT o.id, o.imie, o.nazwisko, COUNT(m.data_poczatku) AS liczba FROM osoba o LEFT JOIN malzenstwo m ON o.id IN (m.id_zony, m.id_meza) WHERE NOT EXISTS (SELECT 1 FROM osoba d WHERE o.id IN (d.id_matki, d.id_ojca)) GROUP BY o.id ) SELECT id, imie, nazwisko, liczba FROM wyniki WHERE liczba = (SELECT MAX(liczba) FROM wyniki) ORDER BY id; \echo 'Zadanie 11. Dla chętnych (rekurencja, nieobowiązkowe). Przodkowie osoby nr 1111. Znaleźć wszystkich znanych przodków osoby o id = 1111. Podać ich dane i najmniejszą liczbę kroków dziecko → rodzic. Nie wypisywać osoby początkowej. Każdego przodka wypisać raz; zabezpieczyć zapytanie przed cyklem w błędnych danych. Kolumny: id, imie, nazwisko, odleglosc.' WITH RECURSIVE droga(id, kroki, sciezka) AS ( SELECT id, 0, ARRAY[id] FROM osoba WHERE id = 1111 UNION ALL SELECT r.id, d.kroki + 1, array_append(d.sciezka, r.id) FROM droga d JOIN osoba o ON o.id = d.id JOIN osoba r ON r.id IN (o.id_matki, o.id_ojca) WHERE NOT r.id = ANY(d.sciezka) ) SELECT o.id, o.imie, o.nazwisko, MIN(d.kroki) AS odleglosc FROM droga d JOIN osoba o ON o.id = d.id WHERE d.kroki > 0 GROUP BY o.id ORDER BY odleglosc, o.id; \echo 'Zadanie 12. Dla chętnych (rekurencja, nieobowiązkowe). Najliczniejsze grono potomków. Dla każdej osoby policzyć wszystkich różnych potomków, niezależnie od pokolenia. Nie zaliczać osoby do jej własnych potomków. Wypisać dane wszystkich osób z największym wynikiem oraz ten wynik. Zapytanie ma zakończyć się nawet przy błędnych cyklach. Kolumny: id, imie, nazwisko, liczba.' WITH RECURSIVE potomkowie(przodek, potomek) AS ( SELECT r.id, d.id FROM osoba r JOIN osoba d ON r.id IN (d.id_matki, d.id_ojca) UNION SELECT p.przodek, d.id FROM potomkowie p JOIN osoba d ON p.potomek IN (d.id_matki, d.id_ojca) ), liczby AS ( SELECT o.id, o.imie, o.nazwisko, COUNT(p.potomek) AS liczba FROM osoba o LEFT JOIN potomkowie p ON p.przodek = o.id AND p.potomek <> o.id GROUP BY o.id ) SELECT * FROM liczby WHERE liczba = (SELECT MAX(liczba) FROM liczby) ORDER BY id;