Kolokwium SQL — Genealogia

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.

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

Schemat w skrócie

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.

Zadania podstawowe 1–10

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

Zadania dodatkowe 11–12: rekurencja

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

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

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

Wersja archiwalna

Pierwotna strona i oryginalne pliki (ZIP). Numeracja, dialekt SQL i zestaw danych w archiwum mogą różnić się od obecnej wersji.

Sprawdź poprawność XHTML 1.0 Strict


Powrót do spisu kolokwiów z SQL