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.

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:

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:

• Wprowadzenie do kwerend, etap IDzjP3iyvcWprowadzenie do kwerend, etap I.

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.

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

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:

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:

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

Wersja parametryczna zapytania w MS Access

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

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:

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

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 AND i OR, najlepiej w postaci tabelarycznej. Załóżmy, że wyrażenie logiczne składa się z dwóch warunków składowych:

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

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

R1RYQw5tA6A1v

Zrzut ekranu przedstawia okno Konstruktor wyrażeń.
Na samej górze znajduje się informacja: Wprowadź wyrażenie, aby zdefiniować obliczeniowe pole kwerendy: (Przykłady wyrażeń: [pole1] + [pole2] i [pole1] < 5).
Poniżej znajduje się puste pole.
Po prawej stornie znajdują się przyciski: OK, Anuluj, Pomoc, Mniej.
Poniżej znajdują się trzy sekcje: Elementy wyrażeń, Kategorie wyrażeń, Wartości wyrażeń.
W sekcji Elementy wyrażeń znajdują się: Oceny 4 i 5 z matematyki, Funkcje, Szkola.accdb, Stałe, Operatory (Zaznaczone czerwonym prostokątem), Typowe wyrażenia. W sekcji Kategorie wyrażeń znajdują się: Wszystkie, Arytmetyczne, Ciąg, Logiczne, Porównawcze (zaznaczone czerwonym prostokątem).
W sekcji Wartości wyrażeń znajdują się: znak mniejszości, znak mniejsze równe, znak mniejsze większe, znak równości, znak większości, znak większe równe, Between, In, Like.

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:

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

Ilustracja

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

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:

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:

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

Zrzut ekranu przedstawia tabele o nagłówkach: Ocena, IDklasy, NazwaPrzedmiotu, imie, nazwisko.
W kolumnie Ocena wypisano wartości 4 i 5.
W kolumnie IDklasy wypisano: 2b.
W kolumnie NazwaPrzedmiotu wypisano matematyka i fizyka.
W kolumnie imie wypisano imiona.
W kolumnie nazwisko wypisano nazwiska.

Przykładowe rozwiązanie w LibreOffice Base

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

Wersja parametryczna zapytania w MS Access

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

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:

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

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:

 NowyAtrybut: IIf(warunek_logiczny;jeśli_prawda;jeśli_fałsz)

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:

 Semestr: IIf(Year([Data]) = 2008;"zimowy";"letni")

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

Zrzut ekranu przedstawia okno Konstruktor wyrażeń.
Na samej górze znajduje się informacja: Wprowadź wyrażenie, aby zdefiniować obliczeniowe pole kwerendy: (Przykłady wyrażeń: [pole1] + [pole2] i [pole1] < 5).
Poniżej znajduje się pole, w które wpisano: Semestr:IIF(Year([Data])=2008;"zimowy";"letni").
Po prawej stornie znajdują się przyciski: OK, Anuluj, Pomoc, Mniej.
Poniżej znajdują się trzy sekcje: Elementy wyrażeń, Kategorie wyrażeń, Wartości wyrażeń.
W sekcji Elementy wyrażeń czerwonym prostokątem zaznaczono: Funkcje wbudowane.
W sekcji Kategorie wyrażeń czerwonym prostokątem zaznaczono: Przepływ sterowania programu.
W sekcji Wartości wyrażeń czerwonym prostokątem zaznaczono: Year.
Poniżej znajduje się zaznaczony czerwonym prostokątem wpis: Zwraca wariant typu Liczba całkowita zawierający liczbę całkowitą reprezentującą rok.

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

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:

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

Zrzut ekranu przedstawia okno Konstruktor wyrażeń.
Na samej górze znajduje się informacja: Wprowadź wyrażenie, aby zdefiniować obliczeniowe pole kwerendy: (Przykłady wyrażeń: [pole1] + [pole2] i [pole1] < 5).
Poniżej znajduje się pole, w które wpisano: Semestr:IIf(Year([Data])=2008;"zimowy";"letni").
Po prawej stornie znajdują się przyciski: OK, Anuluj, Pomoc, Mniej.
Poniżej znajdują się trzy sekcje: Elementy wyrażeń, Kategorie wyrażeń, Wartości wyrażeń.
W sekcji Elementy wyrażeń czerwonym prostokątem zaznaczono: Funkcje wbudowane.
W sekcji Kategorie wyrażeń czerwonym prostokątem zaznaczono: Przepływ sterowania programu.
W sekcji Wartości wyrażeń czerwonym prostokątem zaznaczono: IIf.
Poniżej znajduje się wpis: Zwraca jedną z dwóch części w zależności od wyniku wyznaczania wartości wyrażenia.

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

Zrzut ekranu przedstawia tabelę o nagłówkach:
imie, nazwisko, NazwaPrzedmiotu, Semestr, Ocena.
W kolumnie imie wypisano: Aneta.
W kolumnie nazwisko wypisano: Cygan.
W kolumnie NazwaPrzedmiotu wypisano: angielski.
W kolumnie semestr wypisano wartości letni i zimowy.
W kolumnie Ocena wypisano oceny.

• Zwróconych rekordów: 7

Przykładowe rozwiązanie w LibreOffice Base

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

 CASEWHEN( YEAR( [Data] ) = 2008, 'zimowy', 'letni' )

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

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:

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:

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