Bazy danych · PostgreSQL · Autor pierwotnego kolokwium: Krzysztof Stencel.
Wariant PostgreSQL korzysta z nowej Ewidencji ludności. Okresy zapisujemy jako [data_poczatku, data_konca): dzień początku należy do okresu, dzień końca już nie. NULL w dacie końca oznacza okres otwarty. Dane osoby to id, imie i nazwisko.
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 usc.nowa.create.sql \i usc.nowa.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 usc.nowa.drop.sql usuwa tabele zestawu wraz z danymi.
osoba(id, imie, nazwisko, data_urodzenia, id_matki, id_ojca) — klucz: id; rodzice wskazują tę samą tabelę, NULL oznacza rodzica nieznanego.dowod(nr, id_osoby, data_poczatku, data_konca) — klucz: nr; historia dowodów przypisanych osobie.adres(id_osoby, data_poczatku, data_konca, ulica, nr_domu, nr_mieszkania, kod_pocztowy, miejscowosc) — klucz: (id_osoby, data_poczatku).malzenstwo(id_zony, id_meza, data_poczatku, data_konca) — klucz: (id_zony, id_meza, data_poczatku); obie osoby wskazują tabelę osoba.Zadania 1–10 zaadaptowano z wersji dla Oracle. Archiwalne rozwiązanie opracował Witalis Domitrz. Niektóre zadania kontrolują nietypowe przypadki: pusty wynik na danych ćwiczeniowych nie musi oznaczać błędu.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
Oba zadania są nieobowiązkowe. Każde jest warte 1 punkt dodatkowy. Proszę użyć WITH RECURSIVE.
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.
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.
Pierwotna strona i oryginalne pliki (ZIP). Numeracja, dialekt SQL i zestaw danych w archiwum mogą różnić się od obecnej wersji.