Baza danych

Dysponujemy bazą danych SQLite3 zapisaną w pliku uczniowie2.db. Ćwiczenia wykonamy w programie SQLiteStudio, ale można do tego celu użyć wiersza poleceń bazy SQLite3. Informacje na temat tych narzędzi zawarte zostały w materiale Definiowanie schematu bazy danych w języku SQL, etap IIPkXbedRKMDefiniowanie schematu bazy danych w języku SQL, etap II. Zapytania można również wykonać za pomocą skryptu języka Python, który omówiono w materiale Instrukcje wyszukiwania w języku SQL, etap IIIPkQh27XjUInstrukcje wyszukiwania w języku SQL, etap III.

Po uruchomieniu programu SQLiteStudio wybierz polecenie Bazy danych | Dodaj bazę danych i wskaż położenie pliku uczniowie2.db. Następnie połącz się z bazą, dwukrotnie klikając nazwę bazy w panelu Bazy danych.

Program SQLiteStudio ułatwia poznanie schematu bazy danych, udostępniając wszystkie informacje potrzebne do poznania tabel i łączących je relacji. Po dwukrotnym kliknięciu wybranej tabeli w panelu po lewej stronie, w zakładce Struktura zobaczymy nazwy i typy danych pól zawartych w tabeli.

R1XlKxJZPJMXW

Podgląd struktury tabeli w programie SQLiteStudio. Widoczne jest okno dialogowe. W kolumnie Bazy danych widoczna jest struktura bazy uczniowie2 (SQLLite 3). Tabele (3): Oceny, Przedmioty, Uczniowie. W strukturze widoczny jest jeszcze folder Widoki. Spośród zakładek okna dialogowego wyświetlona jest zakładka Struktura. Tabela zawiera 8 kolumn i 4 wiersze. Są to kolejno: Nazwa, typ danych, Klucz główny, Klucz obcy, Wartości unikalne, Warunek i Niepsute.

Jeżeli klikniemy zakładkę DDL, zobaczymy polecenie SQL, które zostało użyte do utworzenia danej tabeli i ewentualnych powiązań.

Rqp8BrJSzBKnG

Podgląd polecenia SQL DDL, które posłużyło do utworzenia tabeli w programie SQLiteStudio. W kolumnie Bazy danych widoczna jest struktura bazy uczniowie 2. Spośród zakładek głównego okna wyświetlono zakładkę DDL. Widoczny jest zapis. Linia 1 CREATE TABLE oceny (. Linia 2 id_ucz INTEGER NOT NULL, Linia 3 ocena DECIMAL (2, 1) NOT NULL, Linia 4 data DATE NOT NULL, Linia 5 id_prz INTEGER NOT NULL, Linia 6 FOREIGN KEY ( Linia 7 id_ucz Linia 8 ( Linia 9 ( PREFERENCES uczniowie (id_ucz), Linia 10 FOREIGN KEY ( Linia 11 id_prz Linia 12 ) Linia 13 REFERENCES przedmioty (id_prz) Linia 14 );

Ćwiczenie 1

Przeanalizuj schemat bazy, korzystając z informacji o tabelach widocznych na zakładkach Struktura i/lub DDL.

Pokaż rozwiązanie

Polecenie DDL tworzące tabelę uczniowie:

Linia 1. CREATE TABLE uczniowie otwórz nawias okrągły. Linia 2. id podkreślnik ucz INTEGER PRIMARY KEY AUTOINCREMENT przecinek. Linia 3. nazwisko VARCHAR otwórz nawias okrągły 30 zamknij nawias okrągły NOT NULL przecinek. Linia 4. imie VARCHAR otwórz nawias okrągły 30 zamknij nawias okrągły NOT NULL przecinek. Linia 5. ulica VARCHAR otwórz nawias okrągły 50 zamknij nawias okrągły NOT NULL przecinek. Linia 6. nr podkreślnik domu VARCHAR otwórz nawias okrągły 25 zamknij nawias okrągły NOT NULL przecinek. Linia 7. klasa VARCHAR otwórz nawias okrągły 2 zamknij nawias okrągły NOT NULL. Linia 8. zamknij nawias okrągły średnik.

CREATE TABLE uczniowie (
  id_ucz   INTEGER      PRIMARY KEY AUTOINCREMENT,
  nazwisko VARCHAR (30) NOT NULL,
  imie     VARCHAR (30) NOT NULL,
  ulica    VARCHAR (50) NOT NULL,
  nr_domu  VARCHAR (25) NOT NULL,
  klasa    VARCHAR (2)  NOT NULL
);

W tabeli uczniowie występują pola:

• id_ucz – INTEGER, klucz główny, identyfikator ucznia,

• nazwisko, imie, ulica, nr_domu, klasa – VARCHAR, dane tekstowe.

Polecenie DDL tworzące tabelę przedmioty:

Linia 1. CREATE TABLE przedmioty otwórz nawias okrągły. Linia 2. id podkreślnik prz INTEGER PRIMARY KEY AUTOINCREMENT przecinek. Linia 3. nazwa VARCHAR otwórz nawias okrągły 20 zamknij nawias okrągły NOT NULL przecinek. Linia 4. naucz podkreślnik nazw VARCHAR otwórz nawias okrągły 30 zamknij nawias okrągły NOT NULL DEFAULT apostrof apostrof przecinek. Linia 5. naucz podkreślnik imie VARCHAR otwórz nawias okrągły 30 zamknij nawias okrągły NOT NULL DEFAULT apostrof apostrof. Linia 6. zamknij nawias okrągły średnik.

CREATE TABLE przedmioty (
  id_prz     INTEGER      PRIMARY KEY AUTOINCREMENT,
  nazwa      VARCHAR (20) NOT NULL,
  naucz_nazw VARCHAR (30) NOT NULL DEFAULT '',
  naucz_imie VARCHAR (30) NOT NULL DEFAULT ''
);

W tabeli przedmioty występują pola:

• id_prz – INTEGER, klucz główny, identyfikator przedmiotu,

• nazwa, naucz_nazwa, naucz_imie – VARCHAR, dane tekstowe.

Polecenie DDL tworzące tabelę oceny:

Linia 1. CREATE TABLE oceny otwórz nawias okrągły. Linia 2. id podkreślnik ucz INTEGER NOT NULL przecinek. Linia 3. ocena DECIMAL otwórz nawias okrągły 2 przecinek 1 zamknij nawias okrągły NOT NULL przecinek. Linia 4. data DATE NOT NULL przecinek. Linia 5. id podkreślnik prz INTEGER NOT NULL przecinek. Linia 6. FOREIGN KEY otwórz nawias okrągły id podkreślnik ucz zamknij nawias okrągły REFERENCES uczniowie otwórz nawias okrągły id podkreślnik ucz zamknij nawias okrągły przecinek. Linia 7. FOREIGN KEY otwórz nawias okrągły id podkreślnik prz zamknij nawias okrągły REFERENCES przedmioty otwórz nawias okrągły id podkreślnik prz zamknij nawias okrągły. Linia 8. zamknij nawias okrągły średnik.

CREATE TABLE oceny (
  id_ucz INTEGER        NOT NULL,
  ocena  DECIMAL (2, 1) NOT NULL,
  data   DATE           NOT NULL,
  id_prz INTEGER        NOT NULL,
  FOREIGN KEY (id_ucz)  REFERENCES uczniowie (id_ucz),
  FOREIGN KEY (id_prz)  REFERENCES przedmioty (id_prz)
);

W tabeli oceny występują pola:

• id_ucz, id_prz – INTEGER, klucze obce, identyfikatory ucznia i przedmiotu,

• ocena – DECIMAL, pole zawierające oceny,

• data – DATE, pole przechowujące datę wystawienia oceny w formacie ISO8601 „YYYY‑MM‑DD”.

Tabele uczniowie i przedmioty łączy relacja wiele‑do‑wielu definiowana przez relacje jeden‑do‑wielu każdej z tych tabel z tabelą oceny, w której zgromadzono informacje o ocenach każdego ucznia z różnych przedmiotów.

RZ7c1IlEUtAXo

Zrzut ekranu z programu LibreOffice Base. Przedstawia relacje między trzema tabelami. Tabela uczniowie zawiera pola id_ucz, nazwisko, imie, ulica, dom, id_klasy. Tabela oceny zawiera pola: id, id_ucz, icena, data, id_przedm. Tabela przedmioty zawiera id_przedm, nazwa_przedm, nazwisko_naucz, imie_naucz. Pola id_ucz tabel uczniowie oraz oceny połączono linią. Nad linią przy tabeli uczniowie jest cyfra 1, a przy tabeli oceny litera n. Pola id_przed tabel oceny oraz przedmioty połączono linią. Nad linią przy tabeli oceny jest litera n, a przy tabeli oceny cyfra 1.

Grupowanie danych

Spróbujmy przeanalizować niektóre dane dotyczące uczniów zapisane w bazie danych.

Zacznijmy od zapytania zliczającego uczniów:

SELECT COUNT(id_ucz) AS liczba_uczniow
FROM uczniowie;

Dzięki funkcji COUNT() dowiemy się, że w bazie zapisano dane 414 uczniów.

Jeśli jednak chcemy zobaczyć, ilu uczniów mieszka na tej samej ulicy, powinniśmy pogrupować uczniów według nazw ulic i dopiero później zliczyć poszczególne grupy.

SELECT ulica, COUNT(id_ucz) liczba_uczniow
FROM uczniowie
GROUP BY ulica
ORDER BY liczba_uczniow ASC;

Na przykładzie tego zapytania widzimy, że do grupowania rekordów służy klauzula GROUP BY, po której podajemy nazwę pola. Ponadto w klauzuli SELECT uwzględniliśmy pole ulica, aby widzieć nazwy ulic, dla których zliczamy uczniów. Zapytanie pokazuje również, że klauzula AS jest opcjonalna, kiedy tworzymy aliasy pól. Klauzulę sortowania ORDER BY dodaliśmy dla czytelności wyników.

Początkowe zwrócone rekordy:

Kosmiczna     6
Worcella      6
Ksiezycowa    7
Baczynskiego  8
Kopernika     8

Uzyskanie takiej informacji wymaga sięgnięcia tylko do jednej tabeli, czyli uczniowie. W klauzuli SELECT wybieramy pole klasa, według którego grupujemy rekordy, oraz pole id_ucz, które zliczamy. W ten sposób otrzymamy tabelę z liczbą uczniów w poszczególnych klasach.

SELECT klasa, COUNT(id_ucz) liczba_uczniow
FROM uczniowie
GROUP BY klasa;

Początkowe zwrócone rekordy:

1a  30
1b  29
1c  30
1d  27
1e  32

Problem sugeruje, że należy pogrupować uczniów według klas, a następnie policzyć średnią ich ocen. Źródłem kwerendy będzie zatem tabela uczniowie. Z niej wybieramy pole klasa, według którego będą grupowane rekordy. Drugą wymaganą tabelą będą oceny, z której dodajemy pole ocena i nakładamy na nie funkcję AVG() wyliczającą średnią. Zapis w języku SQL:

SELECT klasa, AVG(ocena) srednia_ocen
FROM oceny, uczniowie
USING (id_ucz)
GROUP BY klasa;

Do złączenia tabel użyliśmy klauzuli USING, która może zastąpić klauzulę JOIN ON, jeżeli pola tworzące relację mają taką samą nazwę. Wyświetlone średnie ocen można zaokrąglić przy użyciu funkcji ROUND(), której argumentami są średnia ocena wyliczona przez funkcję AVG() oraz precyzja. Początek kwerendy będzie miał wtedy postać:

SELECT klasa, ROUND(AVG(ocena), 2) srednia_ocen

Początkowe zwrócone rekordy:

1a  2.96
1b  3
1c  3.03
1d  2.99
1e  2.96

Źródłem danych w zapytaniu będą tabele oceny i przedmioty. Dane trzeba pogrupować według pola nazwa z tabeli przedmioty. W klauzuli SELECT obok nazwy przedmiotu umieścimy funkcję AVG(), której przekażemy jako argument pole ocena. Rekordy uporządkujemy rosnąco według średnich ocen, a ich liczbę ograniczymy do jednego.

Kod SQL prezentuje się następująco:

SELECT przedmioty.nazwa Przedmiot, AVG(ocena) Średnia
FROM oceny, przedmioty
WHERE oceny.id_prz = przedmioty.id_prz
GROUP BY przedmioty.nazwa
ORDER BY Średnia ASC
LIMIT 1

Operacja grupowania nie wyklucza sortowania w klauzuli ORDER BY ani ograniczania liczby zwracanych rekordów w klauzuli LIMIT.

Wynik kwerendy:

informatyka  2.93494228751312

Użycie podzapytań

Rozwiązanie zaczniemy od skonstruowania zapytania, które pobierze wszystkie oceny uczniów z podanej klasy. W wynikach kwerendy chcemy mieć nazwę klasy, nazwę przedmiotu oraz oceny, dlatego w klauzuli SELECT umieszczamy pola klasa, nazwa i ocena. Każde z tych pól jest w innej tabeli, warunki złączenia definiujemy za pomocą pól id_prz oraz id_ucz, które tworzą relację, w klauzuli WHERE. Dodajemy również warunek pozwalający wybrać rekordy klasy 1a. Kod SQL:

SELECT klasa, nazwa, ocena
FROM oceny, przedmioty, uczniowie
WHERE oceny.id_prz = przedmioty.id_prz
  AND oceny.id_ucz = uczniowie.id_ucz
  AND klasa = '1a';

Wynikiem kwerendy będzie tabela ocen podanej klasy. Początkowe rekordy:

1a  niemiecki   4
1a  angielski   3
1a  niemiecki   3
1a  niemiecki   4
1a  fizyka      4

Teraz zaprojektujemy końcową kwerendę, która pogrupuje oceny według nazw przedmiotów, obliczy średnią dla każdej grupy i wyświetli nazwy przedmiotów oraz średnie ocen. Poprzednie zapytanie umieścimy w klauzuli FROM jako źródło końcowej kwerendy.

Kod SQL przyjmie postać:

SELECT nazwa, AVG(ocena)
FROM
   (SELECT klasa, nazwa, ocena
    FROM oceny, przedmioty, uczniowie
    WHERE oceny.id_prz = przedmioty.id_prz
      AND oceny.id_ucz = uczniowie.id_ucz
      AND klasa = '1a')
GROUP BY nazwa;

ORDER BY 2 DESC

ORDER BY AVG(ocena) DESC

ORDER BY srednia DESC

Kwerenda parametryczna

Ostatnia kwerenda byłaby o wiele bardziej użyteczna, gdyby pozwalała na podawanie nazwy klasy przed wykonaniem. Taką możliwość łatwo uzyskać, przekształcając zapytanie w kwerendę parametrycznąkwerenda parametrycznakwerendę parametryczną. Wystarczy zamiast nazwy klasy wpisać znak zapytania. Zapytanie przyjmie następującą postać:

SELECT nazwa, AVG(ocena)
FROM
   (SELECT klasa, nazwa, ocena
    FROM oceny, przedmioty, uczniowie
    WHERE oceny.id_prz = przedmioty.id_prz
      AND oceny.id_ucz = uczniowie.id_ucz
      AND klasa = ?)
GROUP BY nazwa;

RzIH6SAyF2Ubk

Okno Parametry zapytania w programie SQLiteStudio. Tytuł okna Parametry zapytania. Proszę podać wartości dla parametrów zapytania. [1]? opcja do wyboru Wartość null. Poniżej dwie zakładki: Liczba oraz Tekst. Wyświetlono Tekst. W polu wpisani 1a. Poniżej dwa przyciski Abort, OK.

Podane zapytanie wykona się w programie SQLiteStudio, który wyświetli okno dialogowe pozwalające podać odpowiednią wartość. Nazwę klasy należy wprowadzić w zakładce Tekst i kliknąć OK.

W wierszu poleceń bazy SQLite lub w zapytaniach wykonywanych przy użyciu skryptu Pythona kwerenda nie zadziała.

Filtrowanie grup rekordów

Problem postawiony w zadaniu podobny jest do zadania, w którym chcieliśmy otrzymać średnie oceny wszystkich klas. Rozwiązanie opiera się więc na poprzedniej kwerendzie:

SELECT klasa, AVG(ocena) srednia_ocen
FROM oceny, uczniowie
USING(id_ucz)
GROUP BY klasa
HAVING srednia_ocen > 3;

Nowością jest klauzula HAVING, która może występować tylko po klauzuli grupującej. Używamy jej wtedy, kiedy nakładamy jakieś warunki na grupy rekordów.

Zwrócone rekordy:

1c  3.03068783068783
2a  3.02972195589645
2d  3.04003724394786
3b  3.01615074024226
3e  3.02485795454545

Warunki z klauzuli WHERE nakładane są przed grupowaniem. Tym samym odfiltrowane rekordy nie są brane pod uwagę przy grupowaniu. W powyższym przykładzie moglibyśmy wykluczyć z otrzymanego zestawienia, np. klasy o profilu matematycznym. Na potrzeby przykładu załóżmy, że są to wszystkie klasy z literą „a” w nazwie:

SELECT klasa, AVG(ocena) srednia_ocen
FROM oceny, uczniowie
USING(id_ucz)
WHERE klasa NOT LIKE '%a'
GROUP BY klasa
HAVING srednia_ocen > 3;

Zwrócone rekordy:

1c  3.03068783068783
2d  3.04003724394786
3b  3.01615074024226
3e  3.02485795454545

Słownik