Informatyka dla klas III

Rozwiązywanie zadań 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 pracownie, gdzie umieszczać pliki dla serwera mysql.

 

Zadanie do wykonania we wtorek, 24.03.2015 na lekcji informatyki.

 

 

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.txt oraz dostępne narzędzia informatyczne, wykonaj poniższe polecenia.

  1. 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.
  2. W którym dniu urodziło się najwięcej dzieci? Podaj datę i liczbę dzieci.
    Uwaga: Jest tylko jeden taki dzień.
  3. Podaj imiona kobiet w wieku poniżej 25 lat, które urodziły dzieci o wadze powyżej 4000 g.
  4. Podaj imiona i daty urodzenia dziewczynek, które odziedziczyły imię po matce.
  5. 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:

 

CREATE TABLE noworodki
(
    id_dziecka INT PRIMARY KEY,
    plec CHAR(1) NOT NULL,
    imie_dziecka VARCHAR(20) NOT NULL,
    data_urodzenia DATE NOT NULL,
    waga INT NOT NULL,
    wzrost INT NOT NULL,
    id_mamy INT NOT NULL
);

CREATE TABLE matki
(
   id_matki INT PRIMARY KEY,
   imie_matki VARCHAR(20) NOT NULL,
   wiek INT NOT NULL
);

 

Pobieramy pliki noworodki.txt do katalogu /tmp.

Plik wczytujemy do tabeli w bazie danych

 

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

 

Sprawdź, czy dane zostały poprawnie odczytane:

 

SELECT * FROM noworodki;

 

Rekordów ma być 180.

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

 

LOAD DATA INFILE '/tmp/mamy.txt' INTO TABLE matki FIELDS TERMINATED BY ' ';
SELECT * FROM matki;

 

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:

 

SELECT imie_dziecka AS Imię, wzrost AS Wzrost
FROM noworodki
WHERE plec='s'
ORDER BY wzrost DESC;

SELECT imie_dziecka AS Imię, wzrost AS Wzrost
FROM noworodki
WHERE plec='c'
ORDER BY wzrost DESC;

 

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:

 

SELECT plec, MAX(wzrost)
FROM noworodki
GROUP BY plec;

 

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

 

SELECT imie_dziecka AS Imię, wzrost AS Wzrost
FROM noworodki
WHERE plec='s' AND wzrost=62;

oraz:

SELECT imie_dziecka AS Imię, wzrost AS Wzrost
FROM noworodki
WHERE plec='c' AND wzrost=61;

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:

 

SELECT imie_dziecka AS Imię, wzrost AS Wzrost
FROM noworodki
WHERE plec='s' AND wzrost=(SELECT MAX(wzrost) FROM noworodki WHERE plec='s');

SELECT imie_dziecka AS Imię, wzrost AS Wzrost
FROM noworodki
WHERE plec='c' AND wzrost=(SELECT MAX(wzrost) FROM noworodki WHERE plec='c');

 

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(*):

 

SELECT data_urodzenia, COUNT(*) AS 'Liczba urodzin'
FROM noworodki
GROUP BY data_urodzenia
ORDER BY 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:

 

SELECT imie_matki AS Matka, wiek AS Wiek, waga AS 'Waga dziecka'
FROM matki,noworodki
WHERE id_matki=id_mamy AND wiek < 25 AND waga > 4000
ORDER BY Matka;

 

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

Znów łączymy tabele w zapytaniu:

 

SELECT imie_dziecka AS 'Imię dziecka', data_urodzenia AS 'Data urodzenia'
FROM matki, noworodki
WHERE id_mamy=id_matki AND imie_dziecka=imie_matki
ORDER BY imie_dziecka;

 

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:

 

SELECT data_urodzenia AS 'Data narodzin bliźniąt'
FROM noworodki
GROUP BY data_urodzenia,id_mamy
HAVING COUNT(*)>1;

 

 

Matura z informatyki, 2010, poziom rozszerzony

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

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

  1. 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).
  2. Wypisz wszystkie oceny ucznia Jana Augustyniaka z języka polskiego.
  3. 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.
  4. 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.
  5. 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).
  6. 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).

 

Wyniki należy przesłać jako załącznik lub treść listu pod adres: i-lo@eduinf.waw.pl. Na początku pliku wyników należy umieścić swoje imię i nazwisko. W rozwiązaniu należy podać polecenia mysql oraz wyniki ich działania, które kopiujemy z okna terminala (tekst należy zaznaczyć myszką, kliknąć prawym przyciskiem i z menu podręcznego wybrać polecenie kopiuj) do notatnika leafpad. Odpowiedzi bez poleceń mysql nie będą oceniane.

 

Rozwiązanie częściowe:

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:

 

CREATE TABLE uczniowie
(
  id_ucznia INT PRIMARY KEY,
  nazwisko VARCHAR(20) NOT NULL,
  imie VARCHAR(15) NOT NULL,
  ulica VARCHAR(20) NOT NULL,
  dom INT,
  id_klasy CHAR(2) NOT NULL
);
CREATE TABLE oceny
(
  id_ucznia INT NOT NULL,
  ocena INT NOT NULL,
  data DATE NOT NULL,
  id_przedmiotu INT NOT NULL
);
CREATE TABLE przedmioty
(
  id_przedmiotu INT PRIMARY KEY,
  nazwa_przedmiotu VARCHAR(15) NOT NULL,
  nazwisko_naucz VARCHAR(20) NOT NULL,
  imie_naucz VARCHAR(15)
);

 

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

 

LOAD DATA INFILE '/tmp/uczniowie.txt' INTO TABLE uczniowie FIELDS TERMINATED BY ';';
LOAD DATA INFILE '/tmp/oceny.txt' INTO TABLE oceny FIELDS TERMINATED BY ';';
LOAD DATA INFILE '/tmp/przedmioty.txt' INTO TABLE przedmioty FIELDS TERMINATED BY ';';

 

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:

 

SELECT COUNT(*) AS 'Liczba uczniów spoza rejonu'
FROM uczniowie
WHERE ulica='Worcella' OR ulica='Sportowa';

 



List do administratora Serwisu Edukacyjnego Nauczycieli I LO

Twój email: (jeśli chcesz otrzymać odpowiedź)
Temat:
Uwaga: ← tutaj wpisz wyraz  ilo , inaczej list zostanie zignorowany

Poniżej wpisz swoje uwagi lub pytania dotyczące tego rozdziału (max. 2048 znaków).

Liczba znaków do wykorzystania: 2048

 

W związku z dużą liczbą listów do naszego serwisu edukacyjnego nie będziemy udzielać odpowiedzi na prośby rozwiązywania zadań, pisania programów zaliczeniowych, przesyłania materiałów czy też tłumaczenia zagadnień szeroko opisywanych w podręcznikach.



   I Liceum Ogólnokształcące   
im. Kazimierza Brodzińskiego
w Tarnowie

©2017 mgr Jerzy Wałaszek

Dokument ten rozpowszechniany jest zgodnie z zasadami licencji
GNU Free Documentation License.