Informatyka dla klasy IIIK

Bazy danych - Ćwiczenia

Nieco zmienione zadanie maturalne z 2014, poziom podstawowy

W Bajtkowie trwa rekrutacja dzieci do przedszkoli prowadzona za pośrednictwem systemu komputerowego. Rodzice wybierają maksymalnie trzy przedszkola, a ich kolejność ustalają na liście preferencji. W plikach dzieci.txt, preferencje.txt i przedszkola.txt zebrano dane o dzieciach, ich listach preferencji oraz przedszkolach biorących udział w rekrutacji. Dane w wierszach są rozdzielone przecinkami.

W pliku dzieci.txt znajdują się 2443 wiersze z informacjami o dzieciach. W każdym wierszu znajdują się następujące dane rozdzielone średnikami: numer PESEL poprzedzony literą p, nazwisko, imię, płeć, wiek.

Przykład:

p06281200672,Adrian,Kacper,chlopiec,6
p06300604359,Baranowski,Piotr,chlopiec,6
p06262204017,Bartoszewicz,Marcin,chlopiec,6
p06311704394,Benka,Karol,chlopiec,6
p06253012074,Bialowas,Pawel,chlopiec,6

Uwaga: każde dziecko ma inny numer PESEL; może być wiele dzieci o tym samym nazwisku i imieniu, ale wówczas będą one miały różne numery PESEL.

W pliku preferencje.txt znajdują się 4754 wiersze z informacjami o preferencjach w ubieganiu się o miejsce w przedszkolu. W każdym wierszu znajdują się następujące dane rozdzielone średnikami: numer PESEL dziecka poprzedzony literą p, pozycja na liście preferencji danego dziecka, identyfikator przedszkola.

Przykład:

p07270202084,1,38
p08310501583,1,48
p09290609238,1,57
p09290609238,2,61
p07222600090,1,57

W pliku przedszkola.txt znajduje się 47 wierszy z informacjami o przedszkolach. W każdym wierszu znajdują się następujące dane rozdzielone średnikami: identyfikator przedszkola, nazwa przedszkola, liczba miejsc w przedszkolu.

Przykład:

66,Niepubliczne Przedszkole Cogito,50
14,Niepubliczne Przedszkole Krasnal,30
13,Niepubliczne Przedszkole Kraina Teczy,30
57,Przedszkole Niepubliczne Radosny Zakatek,30
52,Przedszkole Niepubliczne Stokrotka,25

Wykorzystując dane zawarte w tych plikach oraz dostępne narzędzia informatyczne, wykonaj poniższe polecenia. Odpowiedzi zapisz w pliku wyniki.txt (w pierwszym wierszu umieść swoje imię, nazwisko oraz rok zadania maturalnego) i wyślij go na komputer nauczyciela za pomocą programu Filezilla. Odpowiedź do każdego podpunktu poprzedź numerem tego podpunktu.

Utwórz uporządkowaną alfabetycznie według nazwisk listę sześcioletnich dziewczynek, których preferencje zawierają Przedszkole nr 75 lub Przedszkole nr 71.
Podaj imiona i nazwiska trzech pierwszych osób w kolejności alfabetycznej według nazwisk, które na liście preferencji mają po dokładnie trzy przedszkola.
Podaj nazwę przedszkola, które znajduje się na liście preferencji wyłącznie na pierwszym miejscu (nikt nie wybrał go jako możliwość drugą albo dalszą), oraz liczbę osób, które je wybrały.
Podaj trzy pierwsze wiersze zestawienia, uporządkowanego niemalejąco według średniej liczby chętnych dzieci, zawierającego dla każdego przedszkola następujące informacje: nazwę, liczbę miejsc, liczbę chętnych dzieci, średnią liczbę dzieci na jedno miejsce zaokrągloną do jednego miejsca po przecinku.

Nieco zmienione zadanie maturalne z 2013, poziom podstawowy

Baltazar spisuje informacje o swoich znajomych i ich hobby, które przechowuje w plikach. W każdym pliku informacje zapisane w wierszach są rozdzielone przecinkami. Wiersze kończą się znakami \r\n.

Plik osoby.txt zawiera 200 wierszy z informacjami o znajomych: Id_osoba, Imię, Nazwisko.

Przykład:

o001,Jan,Nadolicki

o002,Anna,Augustowska

Plik hobby.txt zawiera 50 wierszy z informacjami o rodzajach hobby: Id_hobby, Hobby.

Przykład:

h001,numizmatyka

h002,historia

Plik ma_hobby.txt przechowuje w 400 wierszach informacje o tym, czym interesują się znajomi Baltazara: Id, Id_osoba, Id_hobby.

Przykład:

1,o152,h005

2,o065,h043

Podaj wszystkie rodzaje hobby rozpoczynające się od litery „m” lub "t".
Podaj, ile osób interesuje się poszczególnymi rodzajami hobby.
Utwórz zestawienie imion i nazwisk tych osób, których zainteresowaniem jest programowanie lub narciarstwo. Zestawienie posortuj niemalejąco, alfabetycznie według nazwisk.
Utwórz zestawienie imion i nazwisk tych osób, które mają więcej niż 4 rodzaje hobby. Zestawienie posortuj niemalejąco, alfabetycznie według nazwisk, a tam, gdzie nazwiska się powtarzają – według imion.
Podaj hobby, którymi interesuje się największa liczba osób.

Zadanie maturalne z 2012, poziom podstawowy

W plikach kandydaci.txt i zgloszenia.txt (podane pliki pobierz do katalogu tmp na swoim dysku twardym) znajdują się informacje o naborze na studia w pewnej uczelni wyższej. Dane w poszczególnych wierszach rozdzielone są pojedynczymi znakami przecinka. Jeden wiersz pliku kandydaci.txt zawiera następujące informacje o jednej osobie, oddzielone przecinkami: identyfikator osoby, imię, nazwisko, matematyka, informatyka, fizyka, język obcy, płeć. Pola matematyka, informatyka, fizyka, język obcy zawierają liczby punktów (nieujemne liczby całkowite), uzyskanych na egzaminie maturalnym na poziomie rozszerzonym (odpowiednio) z tych przedmiotów. Wartość pola płeć to k (kobieta) lub m (mężczyzna).

Przykład:

k160,Wiktor,Marszalek,27,69,46,89,m
k097,Krystian,Hofman,100,100,68,100,m
k059,Krystian,Kozak,14,86,44,100,m
k065,Ewelina,Blizniak,82,83,100,100,k
k139,Kacper,Lach,100,66,62,100,m

Jeden wiersz pliku zgloszenia.txt zawiera informację o zgłoszeniu jednej osoby na jeden kierunek studiów. Jedna osoba mogła dokonać zgłoszeń na kilka kierunków. W wierszu znajdują się następujące informacje: kierunek studiów, identyfikator kandydata.

Przykład:

fizyka,k116
fizyka,k083
elektronika,k056
elektronika,k165
mechatronika,k085
informatyka,k075
mechatronika,k012
zarzadzanie,k188

Liczbą punktów rekrutacyjnych kandydata na studia nazywać będziemy sumę punktów uzyskanych z matematyki, informatyki, fizyki i języka obcego.

Wykorzystując dane zawarte w tych plikach oraz dostępne narzędzia informatyczne, wykonaj poniższe polecenia.

Podaj osoby, które uzyskały 400 punktów rekrutacyjnych. Wynik przedstaw w postaci listy zawierającej imiona i nazwiska, uporządkowanej alfabetycznie według nazwisk.
Utwórz zestawienie, w którym podasz, ilu kandydatów zgłosiło się na informatykę, ilu na matematykę, a ilu na fizykę.
Podaj imiona i nazwiska wszystkich tych osób, które zgłosiły się jako kandydaci, dokładnie na pięć kierunków studiów.
Podaj liczbę kobiet oraz liczbę mężczyzn, którzy zgłosili się jako kandydaci na informatykę.
Oblicz średnią liczbę punktów z matematyki, uzyskanych przez kandydatów, którzy zgłosili się na fizykę. Wynik zaokrąglij do dwóch miejsc po przecinku.

Pomoc

Utwórz następujące tabele:

TABELA kandydaci
id_k	imię	nazwisko	matematyka	informatyka	fizyka	język	płeć
CHAR(4)	VARCCHAR(20)	VARCCHAR(20)	INT	INT	INT	INT	CHAR(1)

TABELA zgłoszenia
kierunek	id_k
VARCHAR(20)	CHAR(4)

Pola id_k łączą obie tabele ze sobą.

Dane załaduj do utworzonych tabel za pomocą poleceń:

LOAD DATA INFILE '/tmp/kandydaci.txt'
INTO TABLE kandydaci
FIELDS TERMINATED BY ',';

LOAD DATA INFILE '/tmp/zgloszenia.txt'
INTO TABLE zgłoszenia
FIELDS TERMINATED BY ',';

Zadanie maturalne z 2011, poziom podstawowy

Ośrodek wypoczynkowy „Promyk” wynajmuje domki letniskowe pracownikom. Ze względu na dużą liczbę chętnych nałożono ograniczenie – pracownik może zarezerwować domek tylko raz w ciągu roku. Dane są trzy pliki tekstowe o nazwach: domki.txt, pracownicy.txt, rezerwacje.txt. Zawierają one informacje na temat domków, pracowników i rezerwacji domków wykonanych przez pracowników w 2010 roku.

Dane w wierszach każdego z plików rozdzielone są przecinkami.

Plik domki.txt zawiera następujące dane: numer domku, liczbę pokoi, dostępność garażu oraz cenę za dobę.

Przykład:

1 4 Tak 200
2 4 Nie 160

Plik pracownicy.txt zawiera następujące dane: identyfikator pracownika, nazwisko i imię.

Przykład:

1 Wroblewski Jan
2 Wiecek Jaremi

Plik rezerwacje.txt zawiera dane o rezerwacjach: numer rezerwacji, identyfikator pracownika, numer rezerwowanego domku oraz liczbę zarezerwowanych dni.

Przykład:

1 5 2 2
2 20 5 2

Korzystając z danych zawartych w plikach domki.txt, pracownicy.txt i rezerwacje.txt oraz z dostępnych narzędzi informatycznych, wykonaj poniższe polecenia.

Utwórz zestawienie zawierające dla każdego domku jego numer oraz łączną liczbę dni, na które ten domek był zarezerwowany w ciągu całego sezonu.
Podaj nazwiska i imiona pracowników, którzy rezerwowali domek nr 2. Zestawienie posortuj alfabetycznie według imion pracowników.
Podaj nazwisko i imię pracownika, który zapłacił najwięcej za wynajem domku oraz kwotę, którą zapłacił (zgodnie z rezerwacją). Jest tylko jeden taki pracownik.
Podaj liczbę rezerwacji domków z garażem oraz liczbę rezerwacji domków bez garażu.
Utwórz zestawienie najdłuższych rezerwacji dla poszczególnych domków. W zestawieniu podaj dla każdego domku jego numer oraz liczbę dni najdłuższej rezerwacji dla tego domku.

Pomoc

Utwórz następujące tabele:

TABELA domki
nr_d	liczba_p	garaż	cena
INT	INT	CHAR(3)	INT

TABELA pracownicy
id_p	nazwisko	imię
INT	VARCHAR(20)	VARCHAR(20)

TABELA rezerwacje
nr_r	id_p	nr_d	liczba_d
INT	INT	INT	INT

Pola id_d (identyfikator domku) łączą ze sobą tabele domki i rezerwacje.

Pola id_p (identyfikator pracownika) łączą ze sobą tabele pracownicy i rezerwacje.

Dane załaduj do utworzonych tabel za pomocą poleceń:

LOAD DATA INFILE '/tmp/domki.txt'
INTO TABLE domki
FIELDS TERMINATED BY ',';

LOAD DATA INFILE '/tmp/pracownicy.txt'
INTO TABLE pracownicy
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\r\n';

LOAD DATA INFILE '/tmp/rezerwacje.txt'
INTO TABLE rezerwacje
FIELDS TERMINATED BY ',';

Zadanie maturalne z 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 przecinkami: identyfikator, płeć (c – córka, s – syn), imię, data urodzenia, waga [g], wzrost [cm] oraz identyfikator matki.

Przykład:

1,c,Agnieszka,1999-11-20,2450,48,33
2,s,Daniel,1999-11-16,4100,55,143
3,s,Maciej,1999-11-20,3350,55,34
4,c,Laura,1999-11-17,3850,56,15
5,s,Wojciech,1999-11-20,4200,57,163
6,s,Sebastian,1999-11-22,3650,52,75
7,s,Mikołaj,1999-11-21,3100,54,149
8,s,Kacper,1999-11-21,3150,52,133

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

Przykład:

1,Agata,25
2,Agnieszka,33
3,Agnieszka,23
4,Aleksandra,36
5,Aleksandra,19
6,Alicja,35
7,Alicja,31
8,Alina,40

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.

Pomoc

Utwórz następujące tabele:

TABELA noworodki
id_n	płeć	imię_n	data	waga	wzrost	id_m
INT	CHAR(1)	VARCHAR(20)	DATE	INT	INT	INT

TABELA mamy
id_m	imię_m	wiek
INT	VARCHAR(20)	INT

Pola id_m (identyfikator mamy) łączą ze sobą obie tabele.

Dane załaduj do utworzonych tabel za pomocą poleceń:

LOAD DATA INFILE '/tmp/noworodki.txt'
INTO TABLE noworodki
FIELDS TERMINATED BY ',';

LOAD DATA INFILE '/tmp/mamy.txt'
INTO TABLE mamy
FIELDS TERMINATED BY ',';