Baza danych

W załączonych plikach znajdziesz bazę danych Uczniowie w formacie przeznaczonym dla programu LibreOffice Base (rozszerzenie .odb) oraz Microsoft Access (rozszerzenie .accdb). Pobierz odpowiedni plik i zapisz na dysku.

Funkcje agregujące

Język SQL zawiera zestaw użytecznych funkcji agregującychfunkcje agregującefunkcji agregujących, które służą do wykonywania obliczeń bądź innych operacji na grupach rekordów. Są to:

• COUNT() – zwraca liczbę rekordów spełniających podane kryteria;

• AVG() – zwraca średnią wartość wyliczoną dla pól zawierających liczby;

• SUM() – oblicza sumę dla pól zawierających liczby;

• MIN() – zwraca wartość minimalną zapisaną we wskazanych polach liczbowych i tekstowych;

• MAX() – zwraca wartość maksymalną zapisaną we wskazanych polach liczbowych i tekstowych.

Funkcji agregujących możemy używać między innymi do:

• wykonywania obliczeń na wynikach zwracanych przez inne klauzule,

• liczenia zgrupowanych rekordów,

• wyliczania średniej albo sumy wartości z wybranej kolumny,

• znajdowania wartości skrajnych we wskazanej kolumnie.

Wykorzystamy przedstawione funkcje do przeanalizowania informacji z przykładowej bazy.

Na początku sprawdźmy, ile kobiet i ilu mężczyzn zostało zapisanych w bazie. Informację na temat płci znajdziemy w tabeli uczniowie – jest to pole typu BOOLEAN, czyli przechowujące wartość TRUE (PRAWDA, Tak) lub FALSE (FAŁSZ, Nie). W naszym przypadku wartość PRAWDA (Tak) oznacza płeć żeńską.

Zacznijmy od kwerendy zliczającej wszystkie rekordy:

SELECT COUNT(*)
FROM uczniowie;

Wynikiem będzie liczba 156 oraz nagłówek pola COUNT(*) (LibreOffice Base) lub Expr1000 (Microsoft Access).

Do poprzedniego zapytania dodamy warunek, dzięki któremu z tabeli wybrane zostaną rekordy, w których pole plec będzie miało wartość PRAWDA:

SELECT COUNT(*) AS liczba_kobiet
FROM uczniowie
WHERE plec = TRUE;

Wynikiem będzie liczba 65 oraz nagłówek pola liczba_kobiet dzięki użyciu klauzuli AS, która pozwala podawać aliasy pól.

Zliczać można również tylko wybrane pola, co jest przydatne np. wtedy, kiedy szukamy wartości unikalnych. Wtedy używamy klauzuli DISTINCT:

SELECT COUNT(DISTINCT imie) AS liczba_imion
FROM uczniowie
WHERE plec = TRUE;

SELECT COUNT(*) AS liczba_imion
FROM
(SELECT DISTINCT imie FROM uczniowie WHERE plec = TRUE);

Wynikiem kwerendy będzie wartość 31, czyli liczba niepowtarzających się imion żeńskich zapisanych w tabeli.

Warunki służące do wskazania zliczanych wierszy można formułować nie tylko za pomocą klauzuli WHERE. Oto przykłady użycia instrukcji warunkowychinstrukcja warunkowainstrukcji warunkowych:

SELECT
  COUNT(CASE WHEN plec = 1 THEN plec END) AS liczba_kobiet,
  COUNT(CASE WHEN plec = 0 THEN plec END) AS liczba_mezczyzn
FROM uczniowie;

SELECT COUNT(lk) AS liczba_kobiet, COUNT(lm) AS liczba_mezczyzn
FROM
(SELECT IIf(plec = TRUE, 1) AS lk, IIf(plec = FALSE, 0) AS lm FROM uczniowie);

SELECT COUNT(lk) AS liczba_kobiet, COUNT(lm) AS liczba_mezczyzn
FROM
(SELECT SWITCH(plec = TRUE, 1) AS lk, SWITCH(plec = FALSE, 0) AS lm FROM uczniowie);

SELECT plec, COUNT(plec) AS liczba
FROM uczniowie
GROUP BY plec;

Jeszcze innym sposobem uzyskania informacji dotyczącej liczby kobiet jest użycie funkcji SUM(). Ponieważ typ BOOLEAN jest podtypem liczbowym, możemy przekształcić go na liczbę i zsumować.

SELECT SUM(CAST (plec AS INT)) AS liczba_kobiet
FROM uczniowie;

SELECT ABS(SUM(INT(plec))) AS liczba_kobiet
FROM uczniowie;

Teraz spróbujmy się dowiedzieć, jakie są średnie wyniki egzaminów z różnych przedmiotów:

SELECT AVG(egz_hum), AVG(egz_mat), AVG(egz_jez)
FROM uczniowie;

Po wykonaniu kwerendy może się okazać, że dokładność wyników (liczba cyfr po przecinku) znacznie przekracza nasze potrzeby. Możemy jednak w prosty sposób ograniczyć długość części ułamkowej – użyjemy w tym celu funkcji ROUND():

SELECT ROUND(AVG(egz_hum), 2)
FROM uczniowie;

Oprócz wyniku średniego możemy wyświetlić wynik minimalny i maksymalny:

SELECT MIN(egz_hum), ROUND(AVG(egz_hum), 2), MAX(egz_hum)
FROM uczniowie;

Dzięki funkcjom MIN() i MAX() wyszukamy też najlepsze i najgorsze oceny wśród uczniów wskazanej płci (w tym wypadku kobiet):

SELECT MIN(egz_hum), MAX(egz_hum)
FROM uczniowie
WHERE plec = TRUE;

Klauzula GROUP BY

Chcemy dowiedzieć się, jaka jest średnia ocen z wybranego przedmiotu we wskazanej klasie. W wynikach kwerendy chcemy zobaczyć nazwę przedmiotu, nazwę klasy i średnią ocen. Zacznijmy od kwerendy wybierającej odpowiednie pola z różnych tabel:

W LibreOffice Base napiszemy:

SELECT przedmioty.nazwa, klasy.nazwa, ocena
FROM oceny
INNER JOIN uczniowie ON uczniowie.id = oceny.id_ucznia
INNER JOIN przedmioty ON przedmioty.id = oceny.id_przedmiotu
INNER JOIN klasy ON klasy.id = uczniowie.id_klasy
WHERE przedmioty.nazwa='polski' AND klasy.nazwa='3A';

lub:

SELECT przedmioty.nazwa, klasy.nazwa, ocena
FROM oceny, przedmioty, uczniowie, klasy
WHERE oceny.id_przedmiotu = przedmioty.id
      AND oceny.id_ucznia = uczniowie.id
      AND uczniowie.id_klasy = klasy.id
      AND przedmioty.nazwa = 'polski'
      AND klasy.nazwa = '3A';

W Microsoft Access składnia jest bardziej skomplikowana, musimy uwzględnić nawiasy po klauzuli FROM:

SELECT przedmioty.nazwa, klasy.nazwa, ocena
FROM (klasy INNER JOIN uczniowie ON klasy.id = uczniowie.id_klasy)
     INNER JOIN (przedmioty INNER JOIN oceny ON przedmioty.id = oceny.id_przedmiotu) ON uczniowie.id = oceny.id_ucznia
WHERE przedmioty.nazwa="polski" AND klasy.nazwa="3A";

W powyższych kwerendach warto zauważyć, że pola zawierające nazwę przedmiotu i klasy mają taką samą nazwę, dlatego musieliśmy je poprzedzić nazwami tabel. Drugą ważną rzeczą jest uwzględnienie w warunkach złączeń tabeli uczniowie. Mimo że nie wybieramy z niej żadnego pola, tylko dzięki niej możemy odczytać oceny podanej klasy.

Kiedy mamy listę ocen, wystarczy je zgrupować według pól podanych w klauzuli SELECT, czyli nazwy przedmiotu i klasy, i użyć funkcji AVG() na polu ocena.

W LibreOffice Base:

SELECT przedmioty.nazwa, klasy.nazwa, ROUND(AVG(ocena), 2)
FROM oceny
INNER JOIN uczniowie ON uczniowie.id = oceny.id_ucznia
INNER JOIN przedmioty ON przedmioty.id = oceny.id_przedmiotu
INNER JOIN klasy ON klasy.id = uczniowie.id_klasy
WHERE przedmioty.nazwa='polski' AND klasy.nazwa='3A'
GROUP BY przedmioty.nazwa, klasy.nazwa;

W Microsoft Access:

SELECT przedmioty.nazwa, klasy.nazwa, ROUND(AVG(ocena), 2)
FROM (klasy INNER JOIN uczniowie ON klasy.id = uczniowie.id_klasy) INNER JOIN (przedmioty INNER JOIN oceny ON przedmioty.id = oceny.id_przedmiotu) ON uczniowie.id = oceny.id_ucznia
WHERE przedmioty.nazwa="polski" AND klasy.nazwa="3A"
GROUP BY przedmioty.nazwa, klasy.nazwa;

Funkcja ROUND() zaokrągla podaną wartość do podanej liczby miejsc po przecinku, w tym wypadku dwóch. Wynik podanych kwerend to: polski, 3A, 3,5.

Słownik