Informatyka dla klas III

Zadania maturalne z baz danych

Na dzisiejszej lekcji rozwiążemy zadania, które pojawiły się w czasie matur w poprzednich latach. Zwykle zadania dotyczące baz danych zawierają pliki tekstowe, które definiują zawartość tabel. Pliki te należy przekopiować do katalogu /tmp. Nie jest to standardowe miejsce i w przypadku ogólnym należy się dowiedzieć od administratora pracowni, gdzie umieszczać pliki dla serwera mysql. Jest to szczególnie istotne w systemach linuksowych, gdzie dostęp do odpowiednich katalogów z poziomu mysql podlega wysokim restrykcjom ze względów bezpieczeństwa.

Matura z informatyki, 2010, poziom podstawowy

Pliki noworodki.txt oraz mamy.txt zawierają dane o dzieciach i ich matkach.

W pliku noworodki.txt każdy wiersz zawiera następujące informacje o jednym dziecku, rozdzielone znakami spacji: identyfikator, płeć (c – córka, s – syn), imię, data urodzenia, waga [g], wzrost [cm] oraz identyfikator matki.

Przykład:

1 c Agnieszka 20-lis-1999 2450 48 33

W pliku mamy.txt każdy wiersz zawiera informacje o jednej kobiecie, rozdzielone znakami spacji: identyfikator matki, imię, wiek.

Przykład:

1 Agata 25

Identyfikator matki z pliku noworodki.txt odpowiada identyfikatorowi w pliku mamy.txt.

Wykorzystując dane zawarte w plikach mamy.txt i noworodki.txtt oraz dostępne narzędzia informatyczne, wykonaj poniższe polecenia.

Podaj imię i wzrost najwyższego chłopca oraz imię i wzrost najwyższej dziewczynki.
Uwaga: Jest tylko jeden taki chłopiec i tylko jedna taka dziewczynka.
W którym dniu urodziło się najwięcej dzieci? Podaj datę i liczbę dzieci.
Uwaga: Jest tylko jeden taki dzień.
Podaj imiona kobiet w wieku poniżej 25 lat, które urodziły dzieci o wadze powyżej 4000 g.
Podaj imiona i daty urodzenia dziewczynek, które odziedziczyły imię po matce.
W pliku noworodki.txt zapisane są informacje o narodzinach bliźniąt. Bliźnięta można rozpoznać po tej samej dacie urodzenia i tym samym identyfikatorze matki. Pamiętaj, że przykładowo Jacek i Agatka oraz Agatka i Jacek to ta sama para. Możesz założyć, że w danych nie ma żadnych trojaczków, czworaczków, itd. Podaj daty, w których urodziły się bliźnięta.

Rozwiązanie

Z opisu wynika, że w bazie danych będą dwie tabele o następującej strukturze kolumn:

noworodki
id_dziecka	plec	imie_dziecka	data_urodzenia	waga	wzrost	id_mamy
INT	CHAR(1)	VARCHAR(20)	DATE	INT	INT	INT

matki
id_matki	imie_matki	wiek
INT	VARCHAR(20)	INT

Tabele są połączone relacją: noworodki.id_mamy → matki.id_matki

Logujemy się na serwer mysql i tworzymy odpowiednie tabele:

Pobieramy pliki noworodki.txt do katalogu /tmp.

Plik wczytujemy do tabeli w bazie danych

Sprawdź, czy dane zostały poprawnie odczytane:

Rekordów ma być 180.

Pobieramy do katalogu /tmp plik mamy.txt i wczytujemy go do tabeli:

Tabela powinna zawierać 174 wiersze.

a) imię i wzrost najwyższego chłopca oraz imię i wzrost najwyższej dziewczynki.

Rozwiązanie uzyskamy za pomocą dwóch zapytań, które wyświetlą z tabeli noworodki zawartości dwóch kolumn: imie_dziecka oraz wzrost. W pierwszym zapytaniu zażądamy, aby pola plec zawierały s (syn), a w drugim c (córka). Wyniki będą posortowane malejąco względem kolumny wzrost. Wystarczy zatem odczytać pierwszy wiersz tabeli wynikowej:

Gdyby chodziło o wyświetlenie tylko tych dzieci, bez pozostałej reszty, to musimy wykorzystać grupowanie i funkcję agregującą MAX. Grupowanie powoduje wykonanie funkcji agregującej na wszystkich rekordach, które w danej kolumnie posiadają taką samą wartość. Zatem jeśli zgrupujemy tablicę noworodki wg kolumny plec, to powstaną dwie grupy: chłopców o polu plec równym s oraz dziewcząt o polu plec równym c. Wydajemy polecenie:

Znając maksymalny wzrost w grupie dziewcząt (c, 61) i chłopców (s,62), możemy wydać polecenia:

oraz:

W zapytaniu można również wykorzystywać wynik zwrócony przez inne zapytanie. Identyczny efekt otrzymamy, każąc bazie danych wyliczyć odpowiednie wzrosty maksymalne w grupie chłopców i dziewcząt:

b) w którym dniu urodziło się najwięcej dzieci? Podaj datę i liczbę dzieci.

Tutaj musimy zastosować grupowanie wg daty i policzyć liczbę rekordów w każdej z grup. Wykonamy to za pomocą funkcji agregującej COUNT(*):

Wynik odczytujemy z ostatniego wiersza.

c) imiona kobiet w wieku poniżej 25 lat, które urodziły dzieci o wadze powyżej 4000 g

W zapytaniu musimy połączyć ze sobą obie tabele:

d) miona i daty urodzenia dziewczynek, które odziedziczyły imię po matce

Znów łączymy tabele w zapytaniu:

e) daty, w których urodziły się bliźnięta

Warunek: ta sama data urodzenia oraz ta sama matka. Musimy zatem zgrupować wyniki zapytania względem kolumn data_urodzenia i id_mamy. Następnie należy policzyć ilość rekordów w każdej grupie i wyświetlić ze zgrupowania tylko te, które mają wartość COUNT(*) większą od 1:

Matura z informatyki, 2010, poziom rozszerzony

Szkoła dysponuje danymi zawartymi w trzech plikach: uczniowie.txt, oceny.txt, przedmioty.txt.

Plik uczniowie.txt zawiera następujące dane o uczniach: id_ucznia, nazwisko, imie, ulica, dom, id_klasy.
Plik oceny.txt zawiera dane o ocenach: id_ucznia, ocena, data, id_przedmiotu.
Plik przedmioty.txt zawiera dane o przedmiotach: id_przedmiotu, nazwa_przedmiotu, nazwisko_naucz, imie_naucz.

Korzystając z danych zawartych w plikach uczniowie.txt, oceny.txt, przedmioty.txt oraz z dostępnych narzędzi informatycznych wykonaj poniższe polecenia.

Poza rejonem szkoły leżą ulice Worcella oraz Sportowa. Podaj, ilu uczniów mieszka poza rejonem szkoły (czyli na jednej z tych dwóch ulic).
Wypisz wszystkie oceny ucznia Jana Augustyniaka z języka polskiego.
Oblicz, ile dziewcząt i ilu chłopców jest w poszczególnych klasach. Wynik przedstaw w postaci zestawienia: id_klasy, liczba dziewcząt, liczba chłopców. Załóż, że imiona dziewcząt (i tylko dziewcząt) kończą się na literę a.
Utwórz zestawienie dla klasy 2a zawierające nazwy przedmiotów i średnie ocen klasy z tych przedmiotów (średnie podaj z zaokrągleniem do dwóch miejsc po przecinku). Zestawienie posortuj nierosnąco według średnich ocen.
Utwórz zestawienie uporządkowane alfabetycznie według nazwisk zawierające wykaz osób z klasy 2c, które w kwietniu 2009 roku otrzymały oceny niedostateczne (imię, nazwisko, przedmiot).
Podaj nazwisko, imię, klasę oraz średnią ocen osoby, która osiągnęła najwyższą średnią ocen w całej szkole (jest tylko jedna taka osoba).

Rozwiązanie

Na podstawie opisu umieszczonego w zadaniu, tworzymy trzy tabele:

uczniowie
id_ucznia	nazwisko	imie	ulica	dom	id_klasy
INT	VARCHAR(20)	VARCHAR(15)	VARCHAR(20)	INT	CHAR(2)

oceny
id_ucznia	ocena	data	id_przedmiotu
INT	INT	DATE	INT

przedmioty
id_przedmiotu	nazwa_przedmiotu	nazwisko_naucz	imie_naucz
INT	VARCHAR(15)	VARCHAR(20)	VARCHAR(15)

Tabele są powiązane relacjami:

oceny.id_ucznia → uczniowie.id_ucznia

oceny.id_przedmiotu → przedmioty.id_przedmiotu

Logujemy się do mysql i wpisujemy:

Pliki uczniowie.txt, oceny.txt, przedmioty.txt kopiujemy do katalogu /tmp i wczytujemy do tabel:

a) liczba uczniów mieszkających na ulicy Worcella i Sportowa

Po prostu zliczamy rekordy z tabeli uczniowie, w których pole ulica jest równe Worcella lub Sportowa:

b) oceny ucznia Jana Augustyniaka z języka polskiego

Tworzymy zapytanie z trzech tabel:

c) liczba chłopców i dziewcząt w poszczególnych klasach

Tutaj wykorzystujemy funkcję agregującą SUM(), funkcję warunkową IF() oraz grupowanie rekordów wg id_klasy.

Funkcja SUM() zlicza dla każdego rekordu danej klasy to, co jest umieszczone w jej nawiasach. Umieścimy tam funkcję warunkową IF(). Posiada ona trzy argumenty:

IF(warunek,w1,w2). Funkcja wyznacza wartość logiczną warunku. Jeśli otrzyma prawdę, to zwraca jako wynik w1. Inaczej zwraca jako wynik w2.

Tworzymy następujące zapytanie:

d) zestawienie średnich ocen klasy 2a

Tutaj wykorzystujemy funkcję agregującą AVG(), która oblicza średnią arytmetyczną. Zaokrąglenie do dwóch miejsc po przecinku uzyskamy za pomocą funkcji ROUND(x,2). Rekordy muszą być zgrupowane wg przedmiotów. Wyniki będą sortowane malejąco wg średnich ocen. W zapytaniu musimy połączyć wszystkie trzy tabele, ponieważ informację o ocenie i klasie uzyskujemy poprzez tabelę uczniowie.

e) osoby z 2c, które w kwietniu 2009 otrzymały ocenę ndst

Ponieważ w wynikach zapytania musimy umieścić ucznia oraz przedmiot, w zapytaniu należy użyć wszystkich trzech tabel. Miesiąc odczytamy z pola data za pomocą funkcji MONTH(d), która zwraca numer miesiąca (1 = styczeń, 2 = luty,...). Rok odczytamy za pomocą YEAR(d).

f) nazwisko, imię, klasa oraz średnia ocen osoby, która osiągnęła najwyższą średnią ocen w całej szkole

Rozwiązanie polega na wyświetleniu listy uczniów posortowaną wg rosnących średnich i odczytanie ostatniego rekordu: