Ćwiczenia przeprowadzimy na przykładzie bazy danych dziennika szkolnego. Baza danych 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.

Zadanie 1

Aby znaleźć nazwiska na literę B, trzeba posłużyć się operatorem służącym do wyszukiwania zadanej frazy (wzorca) w wartości tekstowej pola. W tym celu posłużymy się operatorem LIKE, który oznacza: podobne, takie jak.

Poszukiwany wzorzec może znajdować się zarówno na początku tekstu, jak i wewnątrz lub na końcu łańcucha znaków. Poznajmy teraz podstawowe zapisy stosowane w poszukiwaniu zgodności ze wzorcem:

W przypadku naszych poszukiwań nazwisk rozpoczynających się na literę B, powinniśmy posłużyć się wzorcem: B*.

W kwerendzie z naszego zadania pozostało jeszcze zdefiniowanie kryterium dla pola IDklasy – w poleceniu chodzi przecież tylko o uczniów klasy 2c. Zobaczmy przykładowe rozwiązania.

Przykładowe rozwiązanie w MS Access

Zwróćmy uwagę na zastosowanie nowej klauzuli Like (czerwona ramka), a także na uporządkowanie wyników kwerendy w kolejności rosnącej według zwróconych nazwisk:

RWDLMHETbITuC

Zrzut ekranu przedstawia kreator kwerend w programie MS Access o nazwie Nazwisko na literę B w klasie 2C.
Na górze znajduje się tabela Uczniowie zawierająca takie pola jak: *, IDucznia(klucz główny), nazwisko, imie, ulica, dom, IDklasy.
Niżej znajduje się tabela o 6 wierszach podpisanych jako: Pole, Tabel, Sortuj, Pokaż, Kryteria, lub.
W wierszu Pole wpisano: IDklasy, nazwisko, imie.
W wierszu Tabela wpisano: Uczniowie, Uczniowie, Uczniowie.
W wierszu Sortuj wpisano Rosnąco w 2 kolumnie.
W wierszu Pokaż znajdują się pola wyboru.
W wierszu Kryteria wpisane są "2c", Like "B*" (jest zaznaczone czerwonym prostokątem.
Wiersz lub jest pusty.

Przykładowe rozwiązanie w LibreOffice Base

W zależności od używanego programu stosujemy nieco inne wartości kryterium dla pola nazwisko:

• MS Access: Like "B*" – tylko pierwsza litera klauzuli Like zapisana jest wielką literą, wartość wzorca tekstowego definiujemy w cudzysłowie.

• LibreOffice Base: LIKE 'B*' – całą klauzulę LIKE zapisujemy wielkimi literami, a wzorzec tekstowy definiujemy w apostrofach.

R1KwMsHSomrCs

Zrzut ekranu przedstawia kreator kwerend w programie LibreOffice Base o nazwie Nazwisko na literę B w klasie 2C.
Na górze znajduje się tabela Uczniowie zawierająca takie pola jak: IDucznia(klucz główny), nazwisko, imie, ulica, dom, IDklasy.
Niżej znajduje się tabela o 8 wierszach podpisanych jako: Pole, Alias, Tabela, Sortowanie, Widoczny, Funkcja, Kryterium, lub.
W wierszu Pole wpisano: IDklasy, nazwisko, imie.
Wiersz Alias jest pusty
W wierszu Tabela wpisano: Uczniowie, Uczniowie, Uczniowie.
W wierszu Sortowanie wpisano Rosnąco w 2 kolumnie.
W wierszu Widoczny znajdują się pola wyboru.
Wiersz Funkcja jest pusty.
W wierszu Kryteria wpisane są "2c", Like "B*" (jest zaznaczone czerwonym prostokątem.
Wiersz lub jest pusty.

Poprawne wyniki kwerendy

Rekordy uporządkowano alfabetycznie według nazwisk:

Rl5EPWmnqFG6J

Zrzut ekranu przedstawia tabelę o 3 kolumnach: IDklasy, nazwisko, imie.
W pierwszej kolumnie wpisane są 2c, w drugiej kolumnie nazwiska, a w trzeciej kolumnie imiona.

• Zwróconych rekordów: 4

Wersja parametryczna zapytania w MS Access

Załóżmy, że chcemy wyszukać uczniów, których nazwisko rozpoczyna się na literę B, ale uczęszczających do klasy o nazwie wprowadzonej z klawiatury:

Wersja parametryczna zapytania w LibreOffice Base

Zamiast używać nawiasów kwadratowych [ ], znanych z MS Access, zastosowano tu zapis z operatorem dwukropka i nazwą atrybutu o wartości wprowadzanej z klawiatury:

Rnc73Km2eiW26

Zrzut ekranu przedstawia kreator kwerend w programie LibreOffice Base o nazwie Nazwisko na literę B w klasie 2C.
Na górze znajduje się tabela Uczniowie zawierająca takie pola jak: IDucznia(klucz główny), nazwisko, imie, ulica, dom, IDklasy.
Niżej znajduje się tabela o 8 wierszach podpisanych jako: Pole, Alias, Tabela, Sortowanie, Widoczny, Funkcja, Kryterium, lub.
W wierszu Pole wpisano: IDklasy, nazwisko, imie.
Wiersz Alias jest pusty
W wierszu Tabela wpisano: Uczniowie, Uczniowie, Uczniowie.
W wierszu Sortowanie wpisano Rosnąco w 2 kolumnie.
W wierszu Widoczny znajdują się pola wyboru.
Wiersz Funkcja jest pusty.
W wierszu Kryteria wpisane są ":IDklasy", Like "B*" .
Wiersz lub jest pusty.

Wnioski płynące z zadania

• Operator LIKE umożliwia wyszukiwanie zadanego wzorca w tekstowych wartościach atrybutów.

• Podstawowymi operatorami towarzyszącymi użyciu LIKE są przede wszystkim zapisy: znak *, zastępujący dowolną liczbę znaków oraz symbol ?, oznaczający dokładnie jeden znak. Pozostałe użyteczne operatory przedstawione zostały w tabeli.

• W języku SQL znak * wewnątrz klauzuli LIKE zastąpimy symbolem %, zamiast ? zapiszemy: _ (znak podkreślenia).

• Tekstową wartość wzorca zapiszemy w MS Access w cudzysłowie, natomiast w LibreOffice Base w apostrofach.

• Pakiet MS Access preferuje zapis: Like, zaś w LibreOffice Base operator zapisuje się wielkimi literami: LIKE.

Zadanie 2

W zapytaniu tym wyznaczymy liczbę rekordów zwróconych przez kwerendę wyszukującą jedynie wskazane w treści zadania oceny.

Zarówno pakiet MS Access, jak i LibreOffice Base oferują możliwość wyznaczenia kilku elementarnych właściwości matematycznych (średnia, suma, liczba zwróconych rekordów, wartość minimalna lub maksymalna), dzięki zastosowaniu tzw. funkcji agregujących dane:

Wspomniane w tabeli funkcje znajdują największe zastosowanie podczas procesu tzw. grupowania danych – więcej informacji na ten temat znajdziesz w e‑materiale: Kwerendy modyfikujące, etap IVPxhH71mMLKwerendy modyfikujące, etap IV.

Nawet bez użycia grupowania funkcje agregujące mogą nam posłużyć do znalezienia odpowiedzi na wiele pytań, pod warunkiem zastosowania kwerendy pomocniczej. Algorytm złożony z dwóch kroków przedstawia się następująco:

1. Kwerenda pomocnicza wyjmuje z całego zbioru tylko te rekordy, które spełniają wszystkie kryteria.

2. Wyniki zwrócone przez kwerendę pomocniczą stanowią zbiór danych, na rzecz których wywołamy funkcję agregującą.

Przedstawmy zatem wykorzystanie kwerendy pomocniczej do wyznaczenia liczby wszystkich ocen 5 z przedmiotu chemia, zapisanych w bazie danych.

Przykładowe rozwiązanie w MS Access

Rozpoczynamy od wyjęcia z bazy danych tylko ocen 5 z przedmiotu chemia – to jest właśnie nasza kwerenda pomocnicza:

ROfMGaNtoYS5X

Zrzut ekranu przedstawia kreator kwerend w programie MS Access o nazwie Piątki z chemii.
Na górze znajdują się 2 tabele Przedmioty oraz Oceny, które są połączone ze sobą relacją jeden do wielu.
Tabela Przedmioty zawiera pola: IDprzedmiotu(klucz główny), Nazwaprzedmiotu, Nazwisko_naucz, Imie_naucz.
Tabela Oceny zawiera pola: IDoceny(klucz główny, IDucznia, Ocena, Data, IDprzedmiotu.
Poniżej znajduje się tabela o sześciu wierszach podpisanych jako: Pole, Tabela, Sortuj, Pokaż, Kryteria, lub.
W wierszu Pole wpisano: NazwaPrzedmiotu, Ocena.
W Tabeli wpisano: Przemioty, Oceny.
Wiersz Sortuj jest pusta.
W wierszu Pokaż znajdują się pola wyboru.
W wierszu Kryteria: wpisano "chemia" w pierwszej kolumnie.
Wiersz lub jest pusty

Następnie, w widoku projektu nowego zapytania przechodzimy do zakładki Kwerendy, po czym dodajemy do projektu wyniki kwerendy pomocniczej:

Dostęp do użycia funkcji agregujących w projekcie kwerendy uzyskamy w MS Access, w zakładce Projektowanie:

Wciśnięcie wskazanego przycisku spowoduje pojawienie się w projekcie kwerendy wiersza Suma wraz z listą wyboru funkcji możliwych do użycia w zapytaniu. W pakiecie LibreOffice Base zawsze możemy wskazać funkcję agregującą, dzięki stale obecnemu wierszowi o nazwie Funkcja.

Przykładowe rozwiązanie w LibreOffice Base

Kwerenda pomocnicza – wyszukujemy tylko oceny 5 z przedmiotu chemia:

R1aH5MI3nIpnY

Zrzut ekranu przedstawia kreator kwerend w programie LibreOffice Base o nazwie Piątki z chemii.
Na górze znajdują się 2 tabele Przedmioty oraz Oceny, które są połączone ze sobą relacją jeden do wielu.
Tabela Przedmioty zawiera pola: IDprzedmiotu(klucz główny), Nazwaprzedmiotu, Nazwisko_naucz, Imie_naucz.
Tabela Oceny zawiera pola: IDoceny(klucz główny, IDucznia, Ocena, Data, IDprzedmiotu.
Poniżej znajduje się tabela o 8 wierszach podpisanych jako: Pole, Alias, Tabela, Sortowanie, Widoczny, Funkcja, Kryteria, lub.
W wierszu Pole wpisano: NazwaPrzedmiotu, Ocena.
W wierszu Tabela wpisano: Przemioty, Oceny.
Wiersz Sortowanie jest pusty.
W wierszu Widoczny znajdują się pola wyboru.
Wiersz Funkcja jest pusty
W wierszu Kryterium: wpisano "chemia" w pierwszej kolumnie i w drugiej 5.
Wiersz lub jest pusty.

Wyznaczamy liczbę ocen 5 z wykorzystaniem funkcji Liczba, która jest odpowiednikiem funkcji Policz, znanej z MS Access:

RF1qveLVpp2XG

Zrzut ekranu przedstawia kreator kwerend w programie LibreOffice Base o nazwie Liczba piątek z chemii.
Na górze znajduje się tabela o nazwie Piątki z chemii zawierająca pola: Nazwa Przedmiotu oraz Ocena.
Poniżej znajduje się tabela z 8 wierszami: Pole, Alias, Tabela, Sortowanie, Widoczny, Funkcja, Kryteria, lub.
W wierszu Pole wpisano Ocena.
Wiersz Alias jest pusty.
W wierszu Tabela wpisano Piątki z chemii.
Wiersz sortowanie jest pusty.
W wierszu Widoczny znajdują się pola wyboru.
W wierszu funkcja wpisane jest Liczba(zaznaczone czerwonym prostokątem)
Wiersz Kryterium jest pusty.
Wiersz lub jest pusty.
Po prawo znajduję się okno dodaj tabelę lub kwerendę, w którym wybrano opcje Kwerendy(zaznaczone czerwonym prostokątem).
Poniżej znajdują się nazwy kwerend, w której kwerenda Piątki z chemii jest zaznaczona czerwonym prostokątem.

Zwróćmy uwagę, że w projekcie zapytania – zamiast tabel – również użyto wyników zwróconych przez kwerendę pomocniczą.

Poprawne wyniki kwerendy

Zwrócona przez kwerendę pomocniczą liczba ocen 5 z przedmiotu chemia to łącznie 245 rekordów.

RQDGT4LuZoT1T

Zrzut ekranu przedstawiający wynik Funkcji PoliczOfOCena w postaci tabeli 1 kolumnowej i 1 wierszowej, w której wpisane jest 245.

Wnioski płynące z zadania

• Dzięki użyciu funkcji agregujących dane możliwe staje się wyznaczenie wartości statystycznej atrybutu, np. średniej.

• Możemy skorzystać z funkcji wyznaczających: liczbę zwróconych przez zapytanie rekordów, średnią, sumę, odchylenie standardowe lub wariancję oraz najmniejszą, bądź największą wartość danego atrybutu.

• Funkcja agregująca zliczająca liczbę zwróconych przez kwerendę pomocniczą rekordów nosi nazwę: Policz w MS Access, Liczba lub Ilosc (w zależności od wersji) w LibreOffice Base oraz COUNT, w języku SQL.

• W programie MS Access użycie funkcji w projekcie kwerendy jest możliwe po wciśnięciu przycisku Suma, w zakładce Projektowanie.

Zadanie 3

W kryteriach projektu kwerendy pomocniczej użyjemy imienia i nazwiska nauczycielki. Ponadto w wynikach uwzględnimy wartość wystawionej oceny, jak również kryterium nazwy klasy, gdyż interesują nas tylko oceny wystawione uczniom klasy 1e.

Przykładowe rozwiązanie w MS Access

RpnNtPadOBYDk

Zrzut ekranu przedstawia projekt kwerendy w programie MS Access: Oceny wystawione przez Alinę Bednarek w 1e.
Poniżej znajdują się trzy tabele: Przedmioty, Oceny, Uczniowie.
Tabela Przedmioty zawiera pola: IDprzedmiotu (z ikoną klucza), NazwaPrzedmiotu, Nazwisko_naucz, Imie_naucz.
Tabela Oceny zawiera pola: IDoceny (ikona klucza), IDucznia, Ocena, Data, IDprzedmiotu.
Tabela Uczniowie zawiera pola: IDucznia (ikona klucza), naziwsko, imie, ulica, dom, IDklasy.
Tabela Przedmioty oraz Uczniowie połączone są z Tabelą Oceny relacją jeden do wielu.
Poniżej znajduje się tabela o sześciu wierszach podpisanych jako: Pole, Tabela, Sortuj, Pokaż, Kryteria, lub.
W wierszu Pole wypisano: Ocena, Imie_naucz, Nazwisko_naucz, IDklasy.
W wierszu Tabela wypisano: Oceny, Przedmioty, Przedmioty, Uczniowie.
Wiersz Sortuj jest pusty.
W wierszu Pokaż znajdują się pola wyboru.
Każde z nich zaznaczono.
W wierszu Kryteria wypisano kolejno zaczynając od drugiej kolumny: "Alina", "Bednarek", "1e".
Wiersz lub jest pusty.

Dysponując określonym zestawem ocen, spełniającym wszystkie kryteria, pora wyznaczyć wartość średnią oceny.

Podobnie jak w poprzednim przykładzie, w widoku projektu wynikowej kwerendy używamy nie tabel, lecz wyników kwerendy pomocniczej:

Jak widać: na liście wyboru w wierszu Suma wskazano funkcję Średnia. Przypomnijmy także, że wiersz Suma staje się widoczny dopiero po kliknięciu przycisku na wstążce Projektowanie:

Ostatnim zadaniem do zrealizowania w poleceniu jest zaokrąglenie wartości średniej do czterech miejsc po przecinku. W tym celu w widoku projektu – po kliknięciu pola Ocena –włączamy dla tego atrybutu Arkusz właściwości:

Dzięki temu w dodatkowym panelu uzyskujemy dostęp do atrybutów Format oraz Miejsca dziesiętne:

RVUS0PULIuP5w

Zrzut ekranu przedstawia Arkusz właściwości.
Na górze widnieje typ zaznaczenia: Pole tekstowe.
Poniżej została wybrana opcja Ogólne.
Poniżej znajduję się tabela.
Zaznaczono w niej 2 wiersze Format: Stałoprzecinkowy i Miejsca dziesiętne: 4.

Co ważne – określenie jedynie liczby miejsc dziesiętnych nie spowoduje zaokrąglenia wartości w widoku arkusza danych! Potrzebne jest dodatkowo określenie formatu liczby.

W naszym przykładzie możemy zdecydować się na format stałoprzecinkowy lub standardowy.

Poprawne wyniki kwerendy

Widok arkusza danych potwierdza, że wartość średniej rzeczywiście została zaokrąglona do czterech miejsc dziesiętnych:

Ra2R4Nnszgw0l

Zrzut ekranu przedstawiający wynik Funkcji ŚredniaOfOcena w postaci tabeli 1 kolumnowej i 1 wierszowej, w której wpisane jest 2,8991.

Przykładowe rozwiązanie w LibreOffice Base

Rozpoczynamy od stworzenia kwerendy pomocniczej, która uwzględnia wszystkie kryteria z polecenia oraz zwraca wartość samej wystawionej oceny:

R1J5FVpTYTIbv

Zrzut ekranu przedstawia projekt kwerendy w programie LibreOffice Base: Oceny wystawione przez Alinę Bednarek w 1e.
Poniżej znajdują się trzy tabele: Przedmioty, Oceny, Uczniowie.
Tabela Przedmioty zawiera pola: IDprzedmiotu (z ikoną klucza), NazwaPrzedmiotu, Nazwisko_naucz, Imie_naucz.
Tabela Oceny zawiera pola: IDoceny (ikona klucza), IDucznia, Ocena, Data, IDprzedmiotu.
Tabela Uczniowie zawiera pola: IDucznia (ikona klucza), naziwsko, imie, ulica, dom, IDklasy.
Tabela Przedmioty oraz Uczniowie połączone są z Tabelą Oceny relacją jeden do wielu.
Poniżej znajduje się tabela o 8 wierszach podpisanych jako: Pole, Alias, Tabela, Sortowanie, Widoczny ,Funkcja , Kryterium, lub.
W wierszu Pole wypisano: Ocena, Imie_naucz, Nazwisko_naucz, IDklasy.
Wiersz Alias jest pusty
W wierszu Tabela wypisano: Oceny, Przedmioty, Przedmioty, Uczniowie.
Wiersz Sortowanie jest pusty.
W wierszu Widoczny znajdują się pola wyboru.
Każde z nich zaznaczono.
Wiersz Funkcja jest pusty.
W wierszu Kryterium wypisano kolejno zaczynając od drugiej kolumny: "Alina", "Bednarek", "1e".
Wiersz lub jest pusty.

Następnie w projekcie wynikowej kwerendy, zamiast tabel bazy danych, używamy wyników zapytania pomocniczego:

Dodatkowo, aby można było wartość średnią zaokrąglić, używamy funkcji CAST, której zadaniem jest tzw. rzutowanie (konwersja, zamiana) typu zmiennej.

Decydujemy się na typ DOUBLE, który obsługuje liczby zmiennoprzecinkowe. Słówko AS to klauzula oznaczająca: traktuj jako.

A zatem kompletny zapis:

CAST( "Ocena" AS DOUBLE )

przeczytamy w następujący sposób:

• Wartość pola Ocena traktuj jako liczbę zmiennoprzecinkową typu DOUBLE.

To jednak nie zagwarantuje nam zaokrąglenia wartości do czterech miejsc po przecinku. W pakiecie LibreOffice Base dokonamy tego nie w widoku projektu, lecz w widoku danych, czyli w wynikach kwerendy.

Klikamy prawym przyciskiem myszy na kolumnie z uzyskaną średnią, wybierając opcję Formatowanie kolumn:

R143InExDn5pN

Zrzut ekranu przedstawiający okno Format pola w programie LibreOffice Base.
Na górze została wybrana opcja Format Pola.
Poniżej znajdują się listy wyboru
Pierwsza od lewej to Kategoria, w której wybrano opcję Liczba (zaznaczona czerwonym prostokątem). Następna to Format, w której znajdują się różne zapisy tej samej liczby.
Następna to Język, w której wybrano Polski.
Poniżej znajdują się Opcje.
W Miejsca dziesiętne wybrano 4(oznaczone czerwonym prostokątem), liczby ujemne na czerwono nie jest zaznaczone.
W Zera wiodące wybrano 1, separator tysięcy nie jest zaznaczony.
Poniżej znajduje się kod formatu, w którym wpisano 0,000.
Poniżej znajdują się przyciski od lewej Pomoc, Resetuj, OK, Anuluj.

Przedstawiona konfiguracja zapewni nam zaokrąglenie wartości średniej do czterech miejsc po przecinku.

Wyniki kwerendy będą oczywiście identyczne jak te uzyskane w MS Access.

Wnioski płynące z zadania

• Systemy bazodanowe wymagają określenia odpowiedniego sposobu formatowania wartości pola stworzonego w wyniku działania funkcji agregującej – jest to szczególnie istotne w przypadku liczb zmiennoprzecinkowych.

• Zaokrąglenie wartości zmiennoprzecinkowej w MS Access jest możliwe dzięki arkuszowi właściwości pola. Nie można jednak zapomnieć o wskazaniu formatu wartości pola – sama liczba miejsc po przecinku nie wystarczy do skutecznego zaokrąglenia.

• Pakiet LibreOffice Base używa funkcji CAST do rzutowania (konwersji) typu pola stworzonego w wyniku działania funkcji agregującej. Zaokrąglenie tej wartości zmiennoprzecinkowej staje się możliwe dopiero w widoku danych.

Słownik