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.

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;

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

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

Tabele są powiązane relacjami:

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';