Rozwiązanie zebranych w tym e‑materiale ćwiczeń pozwoli w praktyce doskonalić umiejętność posługiwania się kwerendamikwerendakwerendami wybierającymi oraz parametrycznymi w konkretnych sytuacjach problemowych.

Pliki potrzebne do ćwiczeń

Ćwiczenia przeprowadzimy na przykładzie bazy danych dziennika szkolnego, która pochodzi z zadania maturalnego.

Zadanie zostało opracowane przez Centralną Komisję Egzaminacyjną i pojawiło się na egzaminie maturalnym z informatyki w maju 2010 r. (poziom rozszerzony, cz. II). Cały arkusz można znaleźć na stronie internetowej CKE.

Niezbędne do zrealizowania kwerend pliki bazodanowe (w zależności od użytkowanego DBMSDBMSDBMS):

  • Szkola.accdb dla pakietu MS Access,

  • Szkola.odb dla oprogramowania LibreOffice Base.

można pobrać tutaj:

R1eo1Svp1xmjz

Plik zawierający bazy danych.

Plik ZIP o rozmiarze 1.16 MB w języku polskim

Sposób importu rekordów z plików tekstowych, udostępnionych w zadaniu maturalnym, do bazy danych w wybranym formacie oraz dokładny opis modelu danych wraz z jego ograniczeniami przedstawiono w e‑materiale:

W bazie danych powinny znajdować się zaimportowane tabele:

  • Uczniowie - z atrybutami opisującymi uczniów szkoły: IDucznia, nazwisko, imie, ulica, dom, IDklasy;

  • Oceny - z danymi na temat uzyskanych ocen: IDoceny, IDucznia, Ocena, Data, IDprzedmiotu;

  • Przedmioty - z danymi o realizowanych w szkole przedmiotach: IDprzedmiotu, NazwaPrzedmiotu, Nazwisko_naucz, Imie_naucz.

Ważne!

Warto pamiętać o analizie modelu danych, której dokonaliśmy w e‑materiale:

Wskazaliśmy wówczas wiele niedoskonałości modelu danych, który zastosowano w podanym zadaniu maturalnym. Rozważmy np. często występującą sytuację, gdy w szkole zatrudnieni zostaliby dwaj nauczyciele, prowadzący ten sam przedmiot. Wówczas tabela Przedmioty traci spójność, gdyż aktualnie pozwala przypisać tylko jednego nauczyciela do jednego przedmiotu. To pokazuje, jak błędy popełnione w fazie projektowania logicznego modelu (brak wydzielenia nauczycieli do osobnej tabeli) utrudniają lub nawet uniemożliwiają późniejszą pracę z danymi.

Oprócz tabel baza danych musi posiadać zdefiniowane powiązania logiczne (relacje), występujące pomiędzy kluczami podstawowymi i obcymi, co również omówiono szczegółowo w I etapie tej serii e‑materiałów.

Zadania do zrealizowania

W tej części niniejszego e‑materiału znajdziesz zestaw poleceń do wykonania, wraz z instrukcjami dotyczącymi ważnych funkcjonalności wybranego systemu DBMSDBMSDBMS, które będą się pojawiać w kolejnych sytuacjach problemowych

Zadanie 1

Stwórz kwerendę, która wybierze z bazy danych dziennika szkolnego wszystkie oceny uzyskane z polskiego przez Jana Augustyniaka.

W etapie I tej serii e‑materiałów stworzyliśmy kwerendę, która wykorzystywała jako kryterium wyszukiwania wartość jednego z atrybutów. Tym razem kryteriów naszych poszukiwań jest kilka.

Aby znaleźć Jana Augustyniaka, możemy oczywiście posłużyć się jego imieniem i nazwiskiem, wyjmując z tabeli Uczniowie tylko te osoby, które jednocześnie mają na imię „Jan” oraz na nazwisko „Augustyniak”.

Czy to jednak na pewno wystarczy? Może przecież zdarzyć się i tak, że do szkoły uczęszcza kilku uczniów o takim imieniu i nazwisku!

Wyszukiwanie w pełni jednoznaczne nastąpi tylko w sytuacji, gdy do naszych poszukiwań użyjemy atrybutu unikalnego, takiego jak IDucznia z tabeli Uczniowie, czyli klucza podstawowego.

Oczywiście wartość tego identyfikatora można sprawdzić pomocniczą kwerendą lub dodać do poniższego projektu pole IDucznia. Jednak spróbujmy rozwiązać zadanie bez użycia klucza, mając jednak na uwadze konieczność weryfikacji zwróconych ocen – czy na pewno są to stopnie uzyskane przez jedną osobę?

Przykładowe rozwiązanie w MS Access

Podobnie jak w etapie I, korzystamy tu ze wstążki Tworzenie, po czym wybieramy opcję Projekt kwerendy:

RwAZtVGIXQTm41

Przykładowe rozwiązanie w LibreOffice Base

Korzystając z LibreOffice Base postępujemy tak jak w MS Access, z tą jednak różnicą, że w zapisie tekstowych wartości kryteriów stosujemy apostrofy zamiast cudzysłowu:

R1ZCEV24yPzoY1

Poprawne wyniki kwerendy

Gdy przyjrzymy się zwróconym rezultatom zapytania, to dzięki obecności kolumny IDklasy w wynikach, możemy się przekonać, iż wszystkie znalezione oceny należą do Jana Augustyniaka z klasy 1e. Oczywiście zakładamy, że w klasie 1e znajduje się tylko jedna osoba o takim imieniu i nazwisku.

  • Zwróconych rekordów: 3

R16hMyuB1QmKV1

Wersja parametryczna zapytania w MS Access

Znajdźmy teraz wszystkie oceny Jana Augustyniaka dla nazwy przedmiotu wpisanej z klawiatury:

RCeadnl56kwj21

Wersja parametryczna zapytania w LibreOffice Base

Tym razem zamiast używać nawiasów kwadratowych [ ], stosujemy zapis z operatorem dwukropka i nazwą atrybutu wprowadzaną z klawiatury:

R1S7bpIXrx4MQ1

Wnioski płynące z zadania

  • Podczas pracy z systemem DBMSDBMSDBMS oraz analizy uzyskanych rezultatów kwerendy należy zachować czujność – samo zwrócenie wyników przez zapytanie nie oznacza jeszcze, że prezentowane dane są kompletne oraz że dotyczą właściwej liczby unikalnych obiektów. To dzięki istnieniu w bazie danych unikalnych wartości kluczy, możliwe jest wyszukiwanie jednoznacznych wyników. W naszym przypadku pewność, iż w szkole istnieje tylko jeden uczeń o imieniu i nazwisku Jan Augustyniak, uzyskalibyśmy tylko włączając do rezultatów zapytania klucz podstawowy: IDucznia.

  • Kryteriów wyszukiwania można w kwerendzie zdefiniować wiele, zaś połączenie logiczne pomiędzy wartościami wpisanymi w różne kolumny, ale w te same wiersze, to koniunkcja, czyli logiczny spójnik AND. Jedynie spełnienie jednocześnie wszystkich kryteriów kwalifikuje rekord do zwrócenia w wynikach kwerendy.

  • Zapytania w wersji parametrycznej przygotowujemy w MS Access wpisując w polu Kryteria wartość w nawiasach kwadratowych, z ewentualnym poleceniem zapisanym wewnątrz nawiasów: [Podaj nazwę przedmiotu].

  • Kwerenda parametryczna w LibreOffice Base powstaje analogicznie w wartości pola Kryterium, z użyciem operatora dwukropka i nazwy atrybutu wprowadzanego z klawiatury, np. :NazwaPrzedmiotu.

Zadanie 2

Stwórz zestawienie zawierające wszystkie oceny co najmniej dobre, które otrzymali uczniowie klasy 2b z przedmiotów matematyka lub fizyka.

Ważne!

Użyte w zadaniu sformułowanie co najmniej dobre oznacza, że bierzemy pod uwagę oceny dobre (4), bardzo dobre (5) oraz celujące (6).

Oznacza to, że wykorzystać należy alternatywę, którą w kwerendzie można zapisać >=4 ale również 4 OR 5 OR 6.

Potrafimy już wykorzystywać kilka kryteriów jednocześnie, których jednoczesne spełnienie kwalifikuje rekord do zwrócenia w wynikach kwerendy – stosujemy wówczas logiczny spójnik AND.

Jak poradzić sobie w sytuacji, gdy w zapytaniu wystarczy spełnienie zaledwie jednego z istniejących kryteriów? Wówczas użyć należy alternatywy logicznej, czyli spójnika OR. Dokładnie taka sytuacja zachodzi w naszym zadaniu, gdyż zajmujemy się ocenami z matematyki lub z fizyki.

Przypomnijmy różnicę w działaniu spójników logicznych ANDOR, najlepiej w postaci tabelarycznej. Załóżmy, że wyrażenie logiczne składa się z dwóch warunków składowych:

Warunek pierwszy

Warunek drugi

AND (koniunkcja)

OR (alternatywa)

false

false

false

false

false

true

false

true

true

false

false

true

true

true

true

true

Ważne!

Wartość logiczna wyrażenia złożonego z warunków składowych połączonych spójnikiem AND jest prawdziwa tylko wtedy, gdy wszystkie warunki składowe są prawdziwe.

Wartość logiczna wyrażenia złożonego z warunków składowych połączonych spójnikiem OR jest prawdziwa wtedy, gdy co najmniej jeden z warunków składowych jest prawdziwy.

Oczywiście wyszukanie ocen tylko z matematyki lub z fizyki to zaledwie jedno z kryteriów kwerendy, poza tym interesują nas jedynie oceny uczniów klasy 2b, a ponadto oceny co najmniej dobre. Atrybut IDklasy powinien mieć zatem ustawione kryterium 2b

Natomiast jeśli chodzi o ocenę co najmniej dobrą, możemy się domyślać, że skoro ocena jest wartością liczbową, to do odfiltrowania pożądanych rekordów skuteczny będzie następujący zapis: >=4.

W razie wątpliwości co do użycia możliwych operatorów porównawczych w MS Access pomocą posłuży okno konstruktora wyrażeń, które można wywołać w widoku projektu kwerendy (wstążka Projektowanie):

R7WdrdHsRLkl51

W tym miejscu możemy łatwo sprawdzić wszystkie dostępne operatory porównania wartości:

R1RYQw5tA6A1v

Utwierdzamy się więc w przekonaniu, iż wartość kryterium: >=4 dla atrybutu Ocena zdoła odfiltrować oceny niebędące czwórkami, piątkami lub szóstkami. Inne poprawne składniowo propozycje to np.:

  • zapis: >3 – również zadziała, ponieważ w dzienniku nie ma ocen niecałkowitych;

  • zapis: Between 4 And 6 – ang. between oznacza wartość „pomiędzy”;

  • zapis: In (4;6) – możliwe dwie wartości wymienione w nawiasie; odpowiednik zbioru akceptowanych wartości, a nie przedziału liczbowego.

Pozostaje nam zatem jeszcze tylko zrealizować logiczną alternatywę. Wydawać by się mogło, iż do tego celu warto wykorzystać dodatkowy wiersz oznaczony etykietą lub w widoku projektu kwerendy:

Ważne!

Program LibreOffice Base nie oferuje funkcjonalności konstruktora wyrażeń.

RweI2aX4uuau21

Jednak w rezultatach zapytania widzimy oceny inne niż czwórki i piątki, a w dodatku uzyskane nie tylko przez uczniów klasy 2b:

RhnCnXHMekqN2

Skąd takie zachowanie kwerendy? Otóż zapis alternatywy logicznej połączył dwa zestawy (wiersze) kryteriów – poniżej oznaczono je czerwoną i niebieską ramką:

R1Oz4uOWfsmrS1

A zatem najpierw wybrano czwórki, piątki i szóstki uzyskane przez uczniów klasy 2b z matematyki (czerwona ramka), a następnie wszystkie oceny dowolnych uczniów z fizyki (niebieska ramka).

Przykładowe rozwiązanie w MS Access

Prawidłowo zrealizowana kwerenda powinna wyglądać następująco:

R7iJPPavm021B1

Oczywiście zapis ten jest niepotrzebnie nadmiarowy, więc choć jest poprawny składniowo, w praktyce często zastępujemy go użyciem spójnika Or w jednym wierszu kryterium:

R1Ifr0HHVdKrc1

Poprawne wyniki kwerendy

Poniżej przedstawiono jedynie kilkanaście rekordów rozwiązania, ponieważ ocen spełniających kryteria jest w dzienniku łącznie aż 63.

RFBeRkTcOpoe5

Przykładowe rozwiązanie w LibreOffice Base

W tym pakiecie należy użyć zapisu dwuwierszowego alternatywy:

R1Y98gQQXqhGr1

Wersja parametryczna zapytania w MS Access

Znajdźmy wszystkie oceny 4 i 5 z matematyki lub fizyki, lecz dla dowolnej klasy w szkole:

RV3fj6ge9xm9t1

Wersja parametryczna zapytania w LibreOffice Base

Ponownie, zamiast użycia nawiasów kwadratowych [] stosujemy zapis z operatorem dwukropka i nazwą atrybutu. Program zapyta o wartość IDklasy jeden raz, pomimo wystąpienia powtórzenia – LibreOffice Base rozumie, że chodzi nam o logiczną alternatywę, a nie o dwukrotne wprowadzenie wartości:

RhgdEBvh5G2aq1

Wnioski płynące z zadania

  • Zapis przedziału lub zbioru liczbowego staje się możliwy i łatwy w realizacji dzięki operatorom porównawczym: <, <= , <>, >, >=, Between, In, Like.

  • Alternatywa logiczna zrealizowana w zapisie wielowierszowym wymaga osobnej definicji wszystkich kryteriów kwerendy w każdym wierszu z osobna, również w przypadku tworzenia kwerendy parametrycznej.

  • W pakiecie MS Access dostępny jest dla alternatywy logicznej także wygodniejszy zapis jednowierszowy, który pozwala uniknąć redundancji wartości kryteriów.

Zadanie 3

Znajdź wszystkie oceny uczennicy Anety Cyganjęzyka angielskiego, a dodatkowo podziel je według semestru, w którym zostały one wystawione. Semestr zimowy w szkole trwał od 1 września 2008 r. do 31 grudnia 2008 r. Semestr letni rozpoczął się 1 stycznia 2009 r. i zakończył 31 sierpnia 2009 r.

To zapytanie wymaga od nas stworzenia w kwerendzie nowego, wirtualnego atrybutu – na przykład o nazwie Semestr, którego wartość określi, czy dana ocena została zdobyta w semestrze zimowym czy letnim.

Gdy mamy do czynienia z taką sytuacją, warto skorzystać z instrukcji warunkowej. Na podstawie sprawdzenia warunku logicznego podejmujemy decyzję o zawartości nowego pola:

Linia 1. NowyAtrybut dwukropek IIf otwórz nawias okrągły warunek podkreślnik logiczny średnik jeśli podkreślnik prawda średnik jeśli podkreślnik fałsz zamknij nawias okrągły.

Wręcz idealnie pasuje to do naszej sytuacji! Nowy atrybut o nazwie Semestr przyjmie wartość tekstową zimowy, jeżeli rok uzyskania oceny miał wartość 2008 - w przeciwnym wypadku wartością będzie napis letni, bo taką ocenę uzyskano już w roku 2009:

Linia 1. Semestr dwukropek IIf otwórz nawias okrągły Year otwórz nawias okrągły otwórz nawias kwadratowy Data zamknij nawias kwadratowy zamknij nawias okrągły znak równości 2008 średnik cudzysłów zimowy cudzysłów średnik cudzysłów letni cudzysłów zamknij nawias okrągły.

Przypomnijmy również, że takie sprawdzenie roku nadaje się do weryfikacji semestru, ponieważ nasza baza danych przechowuje oceny tylko z roku szkolnego 2008/2009, a semestr zimowy zakończył się wraz z końcem roku kalendarzowego.

Oczywiście zauważyliśmy także, iż do wyliczenia roku wystawienia oceny użyto funkcji Year(). I tu znów w razie wątpliwości co do zestawu możliwych do użycia w MS Access funkcji warto skorzystać z okna konstruktora wyrażeń (wstążka Projektowanie):

ROOLWbPTIivOG

Przy okazji przedstawmy zestaw elementarnych, najczęściej stosowanych w MS Access funkcji związanych z przetwarzaniem daty i czasu:

Nazwa funkcji

Zastosowanie

Day()

zwraca liczbę całkowitą z zakresu od 1 do 31, która reprezentuje dzień miesiąca

Month()

zwraca liczbę całkowitą z zakresu od 1 do 12, która reprezentuje numer miesiąca

Year()

zwraca liczbę całkowitą reprezentującą rok kalendarzowy

Hour()

zwraca liczbę całkowitą z zakresu od 0 do 23, która reprezentuje godzinę

Minute()

zwraca liczbę całkowitą z zakresu od 0 do 59, która reprezentuje minuty

Second()

zwraca liczbę całkowitą z zakresu od 0 do 59, która reprezentuje sekundy

Argumentami tych funkcji muszą być oczywiście pola przechowujące datę i/lub czas.

Pełen zestaw funkcji możliwych do zastosowania na datach warto samodzielnie zbadać przy użyciu konstruktora wyrażeń.

W pakiecie LibreOffice Base najbardziej elementarne funkcje operujące na datach mają analogiczne nazwy, jednak zapisywane wielkimi literami, np. YEAR() zamiast Year().

Przykładowe rozwiązanie w MS Access

Pozostałe kryteria wymienione w poleceniu zrealizujemy poprzez sprawdzenie wartości atrybutów: imie, nazwisko oraz NazwaParzedmiotu.

Ostatecznie kwerenda w widoku projektu prezentuje się następująco:

R10acCtgGeMqV1

W czerwonej ramce widocznej powyżej zapisano całą omówioną wcześniej instrukcję warunkową. Warto także zauważyć, że uruchomienie konstruktora wyrażeń w danym polu pomoże nam zapisać całość w dużo wygodniejszym, większym oknie edycyjnym:

R55dGxUDL0HOD

Zapewne zauważyliśmy także w widoku projektu kwerendy wykorzystanie sortowania wyników według wartości atrybutów.

W naszym przykładzie sortowanie jest podwójne. Ważniejsza w sortowaniu jest ta kolumna, która znajduje się po lewej stronie tabeli.

Najpierw rekordy zostaną posortowane malejąco według wartości tekstowej wirtualnego atrybutu Semestr – oznacza to, że w pierwszej kolejności pojawią się oceny z semestru zimowego, a później z letniego. Dlaczego? Ponieważ litera z znajduje się w alfabecie dalej niż litera l, a nasze sortowanie jest przecież malejące.

Następnie tak posortowane oceny zostaną także uporządkowane rosnąco według wartości uzyskanego stopnia, czyli wartości pola Ocena z tabeli Oceny.

Poprawne wyniki kwerendy

Posortowane w omówiony przed chwilą sposób rekordy pojawią się w wynikach zapytania:

RjJOll8dd2TFh
  • Zwróconych rekordów: 7

Przykładowe rozwiązanie w LibreOffice Base

Ważne!

Listing funkcji znaleźć można na witrynie help.libreoffice.org.

W tym pakiecie instrukcję warunkową definiujemy za pomocą funkcji CASEWHEN:

Linia 1. CASEWHEN otwórz nawias okrągły YEAR otwórz nawias okrągły otwórz nawias kwadratowy Data zamknij nawias kwadratowy zamknij nawias okrągły znak równości 2008 przecinek apostrof zimowy apostrof przecinek apostrof letni apostrof zamknij nawias okrągły.

Wartości zapisane są tu w apostrofach, a nie w cudzysłowie. Ponadto nazwę nowego atrybutu definiujemy w wierszu zatytułowanym Alias:

RK5gU4rYF1qG41

Pozostałe kryteria oraz sposób sortowania za pomocą wartości dwóch atrybutów pozostaje analogiczny jak w programie MS Access.

Wersja parametryczna zapytania w MS Access

Aby przećwiczyć tworzenie kwerend parametrycznych, znajdźmy wszystkie oceny Anety Cygan z podziałem na semestry, lecz dla przedmiotu którego nazwę sami wprowadzimy z klawiatury:

R1dhXZ5hAENiF1

Wersja parametryczna zapytania w LibreOffice Base

Tu działamy analogicznie jak w przypadku MS Access - różnica jest taka, że zamiast użycia nawiasów kwadratowych [] pojawia się zapis z operatorem dwukropka i nazwą atrybutu:

R1euTpz4KOuyO1

Wnioski płynące z zadania

  • Wszędzie tam, gdzie na podstawie zaistniałych okoliczności należy wygenerować różną wartość wirtualnego atrybutu, istniejącego tylko na potrzeby kwerendy, warto użyć instrukcji IIf (MS Access) lub CASEWHEN (LibreOffice Base).

  • Wyjmowanie części składowych (np. dnia, miesiąca, roku) z pola przechowującego datę i czas można łatwo zrealizować za pomocą funkcji wbudowanych. Np. dla wartości roku jest to metoda Year() (MS Access) lub YEAR() (LibreOffice Base).

  • Sortowanie rekordów - oprócz tego, że jest rosnące lub malejące - może być podwójne, potrójne itd. Sortowanie zawsze następuje w kolejności od lewej strony kwerendy.

Uzbrojeni w wiedzę i doświadczenie, możemy teraz przejść do samodzielnego zrealizowania zestawu ćwiczeń.

Słownik

kwerenda
kwerenda

(ang. query – zapytanie) zapisane z użyciem języka SQL polecenie skierowane do systemu bazodanowego; jego celem może być: pobranie, dodanie, modyfikacja albo usunięcie danych, a także zmiana sposobu ich przechowywania w systemie

DBMS
DBMS

(ang. Database Management System) lub w wersji polskiej: SZBD (System Zarządzania Bazą Danych) - oprogramowanie, które obsługuje wszystkie działania administracyjne związane z bazami danych i odpowiada za realizację podstawowych funkcji: zarządzanie użytkownikami, ochronę integralności danych, zapewnienie możliwości wykonywania na zgromadzonych informacjach kwerend, dbanie o bezpieczeństwo zbioru informacji; przykładowe systemy DBMS to m.in.: MySQL, MariaDB, FireBird, PostgreSQL, Microsoft SQL Server, SQLite, Oracle Database, MS Access, LibreOffice Base