Ćwiczenia związane z grupowaniem danych przeprowadzimy na przykładzie bazy danych dziennika szkolnego, pochodzącej 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

Grupowanie to proces dzielenia rekordów według pewnego kryterium. Samo dokonanie takiego podziału nie stanowi jednak celu operacji grupowania.

Podział rekordów służy jedynie pośrednio do wyznaczenia wartości funkcji agregującej dla każdej z grup. Tego typu funkcje znamy już z etapu III tej serii e‑materiałów.

W zadaniu rekordy reprezentujące oceny w dzienniku grupujemy według atrybutu NazwaPrzedmiotu, zaś celem operacji jest wyznaczenie średniej oceny.

Ponieważ rekordy zostaną podzielone na grupy według nazwy przedmiotu, to otrzymamy wartość średniej oceny osobno dla każdej grupy (każdego szkolnego przedmiotu). Funkcją agregującą jest więc funkcja Średnia:

Przykładowe rozwiązanie w MS Access

Warto zwrócić uwagę, iż w zadaniu interesują nas tylko oceny uczniów z klasy 2a, stąd nałożono dodatkowe kryterium dla pola IDklasy. Uzyskane wartości średnie uporządkowano także malejąco w wierszu Sortuj.

Co istotne, to że również dla atrybutu IDklasy w wierszu Suma włączono grupowanie.

Operacja grupowania oczywiście może odbywać się według więcej niż jednego atrybutu, lecz w tym zadaniu z racji nałożonego kryterium na pole IDklasy jedyną zwracaną w wynikach zapytania grupą będą i tak tylko uczniowie klasy 2a.

Przypomnijmy także, iż dostęp do wiersza Suma uzyskamy w MS Access po wciśnięciu odpowiedniego przycisku na zakładce Projektowanie:

Ponieważ wartość średnia uzyskana dla każdej grupy (czyli w praktyce dla każdego przedmiotu szkolnego) ma zostać zaokrąglona do czterech miejsc po przecinku, to po kliknięciu na pole Ocena w widoku projektu, przechodzimy do panelu Arkusz właściwości:

Tutaj, analogicznie jak to przedstawiono w etapie III tego bloku e‑materiałów, określamy stałoprzecinkowy format wartości oraz liczbę miejsc dziesiętnych:

R1KTb69wSLpbf

Zrzut ekranu przedstawia okno Arkusz właściwości, w którym wybrano widok Ogólne. W tabeli o 5 wierszach zaznaczono na czerwono Format: stałoprzecinkowy oraz Miejsca dziesiętne: 4.

Teraz powinniśmy już dysponować poprawnymi, zaokrąglonymi średnimi ocenami dla każdego przedmiotu szkolnego realizowanego w klasie 2a.

Poprawne wyniki kwerendy

RYpQp7MmAAQpA

Zrzut ekranu przedstawia tabelę o 3 kolumnach: NazwaPrzedmiotu, IDklasy, ŚredniaOfOcena.
W kolumnie NazwaPrzedmiotu znajdują się nazwy przedmiotów, w kolumnie IDklasy znajduje się nazwa klasy 2a, w kolumnie ŚredniaOfOcena znajdują się wartości liczbowe.

• Zwróconych rekordów: 11

Grupowanie to potężny mechanizm bazodanowy – wystarczyło jedno zapytanie, aby uzyskać średnią ocenę klasy 2a dla wszystkich jedenastu przedmiotów szkolnych!

Zamiast pisać jedenaście kwerend różniących się tylko wartością atrybutu NazwaPrzedmiotu, stworzyliśmy jedną kwerendę, która podzieliła na grupy wszystkie oceny według wartości tego pola.

To nie tylko podział – celem grupowania było wyznaczenie średniej osobno dla każdego przedmiotu (w praktyce grupy rekordów). To właśnie dlatego w grupowaniu musi zostać określona funkcja agregująca.

Przykładowe rozwiązanie w LibreOffice Base

Konstrukcja kwerendy grupującej w LibreOffice Base jest podobna do rozwiązania z pakietu Microsoft Access. Najważniejsza różnica dotyczy wykorzystania rzutowania (konwersji) typu funkcją CAST w polu zawierającym średnią:

R1SQfbGz54WrB

Zrzut ekranu przedstawia kreator kwerend w programie LibreOffice Base o nazwie Szkola.odb: Średnie ocen z przedmiotów 2a - LibreOffice Base
: Projekt kwerendy. Na górze znajdują się 3 tabele Przedmioty, Oceny, Uczniowie.
Tabela Przedmioty zawiera takie pola jak IDprzedmiotu(klucz główny), NazwaPrzedmiotu, Nazwisko_naucz, Imie_naucz.
Tabela Oceny zawiera pola IDoceny(klucz główny), IDucznia, Ocena, Data, IDprzedmiotu
Tabela Uczniowie zawiera takie pola jak: IDucznia(klucz główny), nazwisko, imie, ulica, dom, IDklasy.
Tabele Przedmioty oraz Uczniowie połączone są relacją jeden do wielu z tabelą Oceny.
Niżej znajduje się tabela o 7 wierszach podpisanych jako: Pole, Alias, Tabela, Sortowanie, Widoczny, Funkcja, Kryterium, lub.
W wierszu Pole wpisano: NazwaPrzedmiotu, IDklasy, CAST(”Oceny”.”Ocena” AS DOUBLE)
W wierszu Alias w trzeciej komórce wpisano: ŚredniaOcen
W wierszu Tabela wpisano: Przedmioty, Uczniowie
W wierszu Alias w trzeciej komórce wpisano: malejąco.
W wierszu Widoczny w trzech komórkach widnieje kwadrat ze znakiem zaznaczenia wewnątrz
W wierszu Funkcja wpisano: Grupuj, Grupuj, Średnia. Jest on w czerwonej ramce.
W wierszu Kryteria wpisano "2a" w 2 kolumnie
Wiersz lub jest pusty.

Wartość średniej zostaje skonwertowana do typu zmiennoprzecinkowego DOUBLE, aby można było następnie w widoku danych zaokrąglić tę wartość do czterech miejsc dziesiętnych. Klikamy prawym przyciskiem myszy na kolumnie z uzyskaną średnią, wybierając opcję Formatowanie kolumn:

Wersja parametryczna zapytania w MS Access

W sytuacji, w której interesuje nas średnia uczniów z każdego przedmiotu szkolnego, jednak dla nazwy klasy wprowadzonej z klawiatury:

Wersja parametryczna zapytania w LibreOffice Base

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

RYpXe2z3Nlzqy

Zrzut ekranu przedstawia kreator kwerend w programie LibreOffice Base o nazwie Szkola.odb: Średnie ocen z przedmiotów klasa X - LibreOffice Base
: Projekt kwerendy. Na górze znajdują się 3 tabele Przedmioty, Oceny, Uczniowie.
Tabela Przedmioty zawiera takie pola jak IDprzedmiotu (klucz główny), NazwaPrzedmiotu, Nazwisko_naucz, Imie_naucz.
Tabela Oceny zawiera pola IDoceny(klucz główny), IDucznia, Ocena, Data, IDprzedmiotu
Tabela Uczniowie zawiera takie pola jak: IDucznia(klucz główny), nazwisko, imie, ulica, dom, IDklasy.
Tabele Przedmioty oraz Uczniowie połączone są relacją jeden do wielu z tabelą Oceny.
Niżej znajduje się tabela o 7 wierszach podpisanych jako: Pole, Alias, Tabela, Sortowanie, Widoczny, Funkcja, Kryterium, lub.
W wierszu Pole wpisano: NazwaPrzedmiotu, IDklasy, CAST(”Oceny”.”Ocena” AS DOUBLE)
W wierszu Alias w trzeciej komórce wpisano: ŚredniaOcen
W wierszu Tabela wpisano: Przedmioty, Uczniowie
W wierszu Alias w trzeciej komórce wpisano: malejąco.
W wierszu Widoczny w trzech komórkach widnieje kwadrat ze znakiem zaznaczenia wewnątrz
W wierszu Funkcja wpisano: Grupuj, Grupuj, Średnia.
W wierszu Kryterium wpisano :IDklasy w 2 kolumnie
Wiersz lub jest pusty.

Podstawowe zasady grupowania danych przeprowadzanego w pakietach Microsoft Access oraz LibreOffice Base brzmią więc następująco:

1. Jeżeli w projekcie kwerendy włączono grupowanie według któregoś z atrybutów, to wszystkie pozostałe pola kwerendy również muszą wziąć udział w grupowaniu, chyba że dla pola wskazano funkcję agregującą zamiast grupowania.

2. Jedno z pól musi mieć wskazaną z listy funkcję agregującą, gdyż nie może istnieć grupowanie, które nie ma swojego matematycznego celu.

Wersja kwerendy z polem obliczeniowym

Przygotujemy teraz alternatywną wersję kwerendy z zadania pierwszego. Będzie ona zawierać w rezultatach dodatkowe pole obliczeniowe o nazwie: Wynik, w którym pojawi się zapisana słownie informacja, zależna od wartości średniej uzyskanej przez klasę 2a dla każdego przedmiotu:

• dla wartości średniej mniejszej od 3,000 informacja: niezadowalający;

• dla wartości średniej większej lub równej 3,000 informacja: zadowalający.

Kwerenda z polem obliczeniowym w MS Access

Aby rozwiązać to zadanie, w MS Access tworzymy nową kwerendę Ocena wyników klasy 2a na podstawie wcześniej zapytania o nazwie Średnie ocen z przedmiotów 2a:

RXEDXYPwAP95u

Zrzut ekranu przedstawia kreator kwerend w programie MS Access o nazwie Ocena wyników klasy 2a
Na górze znajdują się tabela: Średnie ocen z przedmiotów 2a, która zawiera pola: *, NazwaPrzedmiotu, IDklasy, ŚredniaOfOcena. Niżej znajduje się tabela o 6 wierszach podpisanych jako: Pole, Tabela, Sortuj, Pokaż, Kryteria, lub.
W wierszu Pole wpisano: NazwaPrzedmiotu, IDklasy, ŚredniaOfOcena, Wynik: IIf([ŚredniaOfOcena]<3;"niezadowalający";"zadowalający")– komórka ta jest obwiedziona czerwoną ramką
W wierszu Tabela wpisano w trzech pierwszych komórkach: Średnie ocen z przedmiotów
Wiersz Sortuj jest pusty
W wierszu Pokaż znajdują się cztery zaznaczone pola wyboru.
Wiersz Kryteria jest pusty
Wiersz lub jest pusty.

Jednak tym razem do rezultatów kwerendy dodajemy nowe pole obliczeniowe o nazwie Wynik, którego wartość ustalamy w zależności od pola ŚredniaOfOcena – wartość w polu oznaczonym czerwoną ramką jest następująca:

Wynik: IIf([ŚredniaOfOcena]<3;"niezadowalający";"zadowalający")

Rozpoczynamy od zfediniowania nazwy pola obliczeniowego, zaś jego wartość zależy od spełnienia (bądź nie) warunku instrukcji warunkowej.

Poprawne wyniki kwerendy z polem obliczeniowym

RJxYrcjRtCTqF

Zrzut ekranu przedstawia tabelę o 4 kolumnach: NazwaPrzedmiotu, IDklasy, ŚredniaOfOcena, Wynik.
W kolumnie NazwaPrzedmiotu znajdują się nazwy przedmiotów, w kolumnie IDklasy znajduje się nazwa klasy 2a, w kolumnie ŚredniaOfOcena znajdują się wartości liczbowe, a w kolumnie wynik napis: zadowalający, jeśli średnia z poprzedniej kolumny jest większa od 3, a niezadowalający, jeśli mniejsza od 3.

• Zwróconych rekordów: 11

Kwerenda z polem obliczeniowym w LibreOffice Base

Analogicznie do rozwiązania w MS Access, skorzystamy z wcześniej stworzonego zapytania o nazwie Średnie ocen z przedmiotów 2a:

RpQloaoAbPxCC

Zrzut ekranu przedstawia kreator kwerend w programie LibreOffice Base o nazwie Szkola.odb: Średnie ocen z przedmiotów 2a - LibreOffice Base
: Projekt kwerendy. Na górze znajdują się 3 tabele Przedmioty, Oceny, Uczniowie.
Tabela Przedmioty zawiera takie pola jak IDprzedmiotu (klucz główny), NazwaPrzedmiotu, Nazwisko_naucz, Imie_naucz.
Tabela Oceny zawiera pola IDoceny(klucz główny), IDucznia, Ocena, Data, IDprzedmiotu
Tabela Uczniowie zawiera takie pola jak: IDucznia(klucz główny), nazwisko, imie, ulica, dom, IDklasy.
Tabele Przedmioty oraz Uczniowie połączone są relacją jeden do wielu z tabelą Oceny.
Niżej znajduje się tabela o 7 wierszach podpisanych jako: Pole, Alias, Tabela, Sortowanie, Widoczny, Funkcja, Kryterium, lub.
W wierszu Pole wpisano: NazwaPrzedmiotu, IDklasy, CAST(”Oceny”.”Ocena” AS DOUBLE)
W wierszu Alias w trzeciej komórce wpisano: ŚredniaOcen
W wierszu Tabela wpisano: Przedmioty, Uczniowie
W wierszu Sortowanie w trzeciej komórce wpisano (bez sortowania) – pole to znajduje się w czerwonej ramce
W wierszu Widoczny w trzech komórkach widnieje kwadrat ze znakiem zaznaczenia wewnątrz
W wierszu Funkcja wpisano: Grupuj, Grupuj, Średnia.
W wierszu Kryterium wpisano: ‘2a’ w 2 kolumnie
Wiersz lub jest pusty.

Co ważne, LibreOffice Base wymaga usunięcia z tego zapytania malejącego sortowania wyników (patrz czerwona ramka) – w tym pakiecie można tego dokonać dopiero w kwerendzie pochodnej:

Rk9Gqz41zopdQ

Zrzut ekranu przedstawia kreator kwerend w programie LibreOffice Base o nazwie Szkola.odb: Ocena wyników klasy 2a - LibreOffice Base
: Projekt kwerendy. Na górze znajdują się tabela: Średnie ocen z przedmiotów 2a, która zawiera pola: *, NazwaPrzedmiotu, IDklasy, ŚredniaOcen. Niżej znajduje się tabela o 6 wierszach podpisanych jako: Pole, Alias, Tabela, Sortowanie, Widoczny, Funkcja, Kryterium
W wierszu Pole wpisano: NazwaPrzedmiotu, IDklasy, ŚredniaOcen, CASEWHEN( [ŚredniaOcen] < 3, 'niezadowalający', 'zadowalający' ) – komórka ta jest obwiedziona czerwoną ramką
Wiersz Alias jest pusty
W wierszu Tabela wpisano w trzech pierwszych komórkach: Średnie ocen z przedmiotów
W wierszu Sortowanie w czwartej komórce wybrano: malejący
W wierszu Widoczny znajdują się cztery zaznaczone pola wyboru.
Wiersz Funkcja jest pusty
Wiersz Kryterium jest pusty.

Do rezultatów kwerendy dodaliśmy nowe pole obliczeniowe o nazwie Wynik, którego wartość ustalamy w zależności od pola ŚredniaOcen – wartość w polu oznaczonym czerwoną ramką jest następująca:

CASEWHEN( [ŚredniaOcen] < 3, 'niezadowalający', 'zadowalający' )

Wyniki będą analogiczne jak w MS Access, zauważmy też włączenie sortowania malejącego dopiero w kwerendzie Ocena wyników klasy 2a zawierającej pole obliczeniowe.

Wnioski płynące z zadania

• Chcąc wykorzystać funkcję agregującą w celu obliczenia wartości podsumowującej dla grupy rekordów, należy zastanowić się, jakie pola wybrać do grupowania,

• Zaokrąglenie wartości otrzymanej w wyniku wykonania funkcji agregującej wymaga określenia formatu wartości oraz liczby miejsc dziesiętnych.

• Zapytanie używające mechanizmu grupowania może być kwerendą parametryczną,

• Dodatkowe pole obliczeniowe pozwala wyznaczyć wartości powstające na podstawie pól pierwotnie zwróconych w rezultatach kwerendy.

Zadanie 2

W rozwiązaniu tego polecenia również warto użyć mechaniki grupowania danych. Wszystkie oceny chcemy podzielić na grupy według osób, które dany stopień w dzienniku uzyskały.

Następnie dla każdej grupy ocen konkretnego ucznia danej klasy wyznaczymy średnią tych wartości – funkcją agregującą będzie więc Średnia:

Przykładowe rozwiązanie w Microsoft Access

Zwróćmy jednak uwagę, że może zdarzyć się, iż w dzienniku szkolnym pojawią się osoby o identycznych imionach i nazwiskach. W analizowanej bazie danych istnieją aż trzy uczennice o imieniu i nazwisku Magdalena Zielinska – w klasach: 1c, 1e, 2b. Można to sprawdzić w tabeli Uczniowie.

Ponieważ interesuje nas piątka najlepszych uczniów w szkole, wynikowe średnie sortujemy malejąco oraz zwracamy tylko pięć pierwszych rekordów – patrz czerwona ramka obok opcji Zwróć na zakładce Projektowanie.

Zaokrąglenie wartości realizujemy identycznie jak w poprzednim poleceniu.

Poprawne wyniki kwerendy

R1TeivIYPZQpG

Zrzut ekranu przedstawia tabelę o 5 kolumnach: ŚredniaOfOcena, imie, nazwisko, IDucznia, IDklasy.
W kolumnie ŚredniaOfOcena znajduje się średnia ocen, w kolumnie imie znajdują się imiona uczniów, w tabeli nazwisko znajdują się nazwiska uczniów, w kolumnie IDucznia numer przypisany uczniowi, a w kolumnie IDklasy symbol klasy.

• Zwróconych rekordów: 5

Przykładowe rozwiązanie w LibreOffice Base

Mechanika grupowania jest identyczna jak w Microsoft Access; nie zapominajmy o uwzględnieniu atrybutu unikalnego opisującego ucznia oraz o ograniczeniu liczby zwracanych przez kwerendę rekordów do pięciu:

Ponownie, z powodu konieczności zaokrąglenia wartości, konwertujemy wynik średniej do typu zmiennoprzecinkowego DOUBLE oraz w widoku danych klikamy prawym przyciskiem myszy na kolumnie z uzyskaną średnią, wybierając opcję Formatowanie kolumn.

Wersja parametryczna zapytania w Microsoft Access

Załóżmy, że tym razem interesuje nas pięciu uczniów o najwyższej średniej, lecz tylko uczęszczających do klasy, której nazwę wprowadzimy z klawiatury:

Ciekawostka

Oczywiście może się zdarzyć, iż uczeń na szóstym miejscu w zestawieniu osiągnie dokładnie taką samą wartość średniej jak osoba na miejscu piątym!

Jak zachowa się Microsoft Access oraz LibreOffice Base w sytuacji uzyskania wyników ex aequo?

Rozważmy rezultaty powyższej kwerendy zwrócone dla klasy 2b:

R1KApyTHoevUQ

Zrzut ekranu przedstawia tabelę o 5 kolumnach: ŚredniaOfOcena, imie, nazwisko, IDucznia, IDklasy.
W kolumnie ŚredniaOfOcena znajduje się średnia ocen, w kolumnie imie znajdują się imiona uczniów, w tabeli nazwisko znajdują się nazwiska uczniów, w kolumnie IDucznia numer przypisany uczniowi, a w kolumnie IDklasy symbol klasy. W przypadku dwóch pierwszych komórek w kolumnie: ŚredniaOfOcena wartości liczbowe w nich zawarte są takie same – znajdują się one w czerwonej ramce.

Co wydarzyłoby się w przypadku przebudowania kwerendy w taki sposób, aby zwracała średnią tylko najlepszego ucznia?

Zachowania obu pakietów są następujące:

• Microsoft Access pomimo zastosowania klauzuli TOP 1 w zapytaniu zwraca w rezultatach kwerendy oba rekordy,

• LibreOffice Base zwraca w analogicznej sytuacji tylko jeden z rekordów, nie sygnalizując wystąpienia remisu.

Wersja parametryczna zapytania w LibreOffice Base

Ponownie zamiast nawiasów kwadratowych wykorzystujemy zapis :IDklasy.

Wnioski płynące z zadania

• W zapytaniach zawsze korzystamy z atrybutu unikalnego w grupowaniu, tak aby zbieżność imienia i nazwiska nie spowodowała wyznaczenia średniej np. trojga uczniów, zamiast dla każdej osoby oddzielnie.

• Zarówno Microsoft Access, jak i LibreOffice Base umożliwiają ograniczenie liczby zwracanych rekordów.

• Ograniczenie liczby zwracanych rekordów w połączeniu z mechaniką sortowania wyników pozwala znaleźć top X wartości największych lub najmniejszych, gdzie przez X rozumiemy liczbę zwracanych przez kwerendę wierszy.

Zadanie 3

W modelu danych dziennika szkolnego istnieje łącznie jedenaścioro nauczycieli, wśród których nie występuje zbieżność imienia i nazwiska. Grupowania rekordów możemy zatem dokonać jedynie według pól Nazwisko_naucz oraz Imie_naucz, zaś funkcją agregującą będzie Średnia wyznaczona dla pola Ocena.

Przykładowe rozwiązanie w Microsoft Access

R1FJ12SK84mz8

Zrzut ekranu przedstawia kreator kwerend w programie MS Access o nazwie Średnia ocen wystawiana przez każdego z nauczycieli.
Na górze znajdują się 3 tabele Uczniowie, Oceny, Przedmioty.
Tabela Uczniowie zawiera takie pola jak: IDucznia(klucz główny), nazwisko, imie, ulica, dom, IDklasy.
Tabela Oceny zawiera pola IDoceny(klucz główny), IDucznia, Ocena, Data, IDprzedmiotu
Tabela Przedmioty zawiera takie pola jak IDprzedmiotu (klucz główny), NazwaPrzedmiotu, Nazwisko_naucz, Imie_naucz.
Tabele Przedmioty oraz Uczniowie połączone są relacją jeden do wielu z tabelą Oceny.
Niżej znajduje się tabela o 7 wierszach podpisanych jako: Pole, Tabela, Suma, Sortuj, Pokaż, Kryteria, lub.
W wierszu Pole wpisano: Nazwisko_naucz, Imie_naucz, Ocena
W wierszu Tabela wpisano: Przedmioty, Przedmioty, Oceny
W wierszu Suma wybrano Grupuj według w 2 kolumnach i Średnia w trzeciej kolumnie.
W wierszu Sortuj wpisano Rosnąco w ostatniej kolumnie.
W wierszu Pokaż znajdują się pola wyboru.
Wiersz Kryteria oraz lub jest pusty.

Ponieważ chcemy uporządkować wyniki, rozpoczynając od nauczyciela, który wystawia ocenę średnio najniższą, to w wierszu Sortuj wybrany został porządek rosnący.

Poprawne wyniki kwerendy

Zaokrąglenie średnich ocen do czterech miejsc po przecinku uzyskujemy w Microsoft Access tak samo, jak opisano w zadaniu pierwszym.

RuivrcB7OkV5v

Zrzut ekranu przedstawia tabelę o 3 kolumnach: Nazwisko_naucz, Imie_naucz, ŚredniaOfOcena.
W kolumnie Nazwisko_naucz znajdują się nazwiska, w kolumnie Imie_naucz znajdują się imiona, w kolumnie ŚredniaOfOcena znajdują się wartości liczbowe.

• Zwróconych rekordów: 11

Ciekawostka

Domyślny alias ŚredniaOfOcena ustanowiony automatycznie przez Microsoft Access możemy zmienić – wystarczy w widoku projektu kwerendy dopisać w wybranej kolumnie nazwę pola zakończoną dwukropkiem:

RSNEAcSE7DMU1

Zrzut ekranu przedstawia tabelę w trzech kolumnach w programie MS Access . Posiada 7 wierszy podpisanych jako: Pole, Tabela, Suma, Sortuj, Pokaż, Kryteria, lub.
W wierszu Pole wpisano: Nazwisko_naucz, Imie_naucz, Średnia: Ocena (pole zaznaczone w czerwonej ramce)
W wierszu Tabela wpisano: Przedmioty, Przedmioty, Oceny
W wierszu Suma wybrano Grupuj według w 2 kolumnach i Średnia w trzeciej kolumnie.
W wierszu Sortuj wpisano Rosnąco w ostatniej kolumnie.
W wierszu Pokaż znajdują się pola wyboru.
Wiersz Kryteria oraz lub jest pusty.

w ten sposób zmienimy domyślny alias ŚredniaOfOcena na samodzielnie zdefiniowaną wartość: Średnia.

Przykładowe rozwiązanie w LibreOffice Base

Nie zapominamy o zastosowaniu funkcji CAST, ponieważ chcemy zaokrąglić wyniki w widoku danych. Zwróćmy też uwagę, iż nowemu atrybutów nadano alias Srednia, co nie jest może koniecznością, ale może okazać się przydatne do łatwiejszej analizy wyników kwerendy.

RksAPAOgsFh4n

Zrzut ekranu przedstawia kreator kwerend w programie LibreOffice Base o nazwie Szkola.odb: Średnia ocena wystawiana przez każdego z nauczycieli. - LibreOffice Base
: Projekt kwerendy. Na górze znajdują się 3 tabele: Uczniowie, Oceny, Przedmioty.
Tabela Uczniowie zawiera takie pola jak: IDucznia (klucz główny), nazwisko, imie, ulica, dom, IDklasy.
Tabela Oceny zawiera pola IDoceny (klucz główny), IDucznia, Ocena, Data, IDprzedmiotu
Tabela Przedmioty zawiera takie pola jak IDprzedmiotu (klucz główny), NazwaPrzedmiotu, Nazwisko_naucz, Imie_naucz.
Tabele Przedmioty oraz Uczniowie połączone są relacją z tabelą Oceny.
Niżej znajduje się tabela o 7 wierszach podpisanych jako: Pole, Alias, Tabela, Sortowanie, Widoczny, Funkcja, Kryterium, lub.
W wierszu Pole wpisano: Nazwisko_naucz, Imie_naucz, CAST(”Oceny”.”Ocena” AS DOUBLE)
W wierszu Alias w trzeciej komórce wpisano: Średnia
W wierszu Tabela wpisano: Przedmioty, Przedmioty
W wierszu Sortowanie w trzeciej komórce wpisano rosnąco
W wierszu Widoczny w trzech komórkach widnieje kwadrat ze znakiem zaznaczenia wewnątrz
W wierszu Funkcja wpisano: Grupuj, Grupuj, Średnia.
Wiersz Kryterium oraz lub jest pusty.

Przypomnijmy, że w widoku danych klikamy prawym przyciskiem myszy na kolumnie z uzyskaną średnią, wybierając opcję Formatowanie kolumn, tam ustawiając pożądaną liczbę miejsc dziesiętnych – w razie wątpliwości zajrzyj do opisu zadania pierwszego.

Wersja parametryczna zapytania w Microsoft Access

Załóżmy, iż tym razem interesuje nas średnia ocena wystawiana przez nauczyciela nie w całej szkole, lecz tylko w klasie, której nazwę wprowadzimy z klawiatury:

RQDsfjwkz5cuP

Zrzut ekranu przedstawia kreator kwerend w programie MS Access o nazwie Średnia ocen wystawiana przez każdego z nauczycieli w klsie X 
Na górze znajdują się 3 tabele: Uczniowie, Oceny, Przedmioty.
Tabela Uczniowie zawiera takie pola jak: IDucznia(klucz główny), nazwisko, imie, ulica, dom, IDklasy.
Tabela Oceny zawiera pola IDoceny(klucz główny), IDucznia, Ocena, Data, IDprzedmiotu
Tabela Przedmioty zawiera takie pola jak IDprzedmiotu (klucz główny), NazwaPrzedmiotu, Nazwisko_naucz, Imie_naucz.
Tabele Przedmioty oraz Uczniowie połączone są relacją jeden do wielu z tabelą Oceny.
Niżej znajduje się tabela o 7 wierszach podpisanych jako: Pole, Tabela, Suma, Sortuj, Pokaż, Kryteria, lub.
W wierszu Pole wpisano: Nazwisko_naucz, Imie_naucz, Ocena, IDklasy
W wierszu Tabela wpisano: Przedmioty, Przedmioty, Oceny, Uczniowie
W wierszu Suma wybrano Grupuj według w 2 kolumnach, Średnia oraz Grupuj według w ostatniej kolumnie.
W wierszu Sortuj wpisano Rosnąco w trzeciej kolumnie.
W wierszu Pokaż znajdują się zaznaczone pola wyboru.
W wierszu Kryteria w czwartej komórce znajduje się zawartość: (Podaj nazwę klasy)
Wiersz lub jest pusty.

Wersja parametryczna zapytania w LibreOffice Base

Ponownie używamy operatora dwukropka oraz nazwy atrybutu, którego wartość wprowadzimy z klawiatury:

RkuTqImzEsTdd

Zrzut ekranu przedstawia kreator kwerend w programie LibreOffice Base o nazwie Szkola.odb: Średnia ocena wystawiana przez każdego z nauczycieli w klasie X - LibreOffice Base
: Projekt kwerendy. Na górze znajdują się 3 tabele: Uczniowie, Oceny, Przedmioty.
Tabela Uczniowie zawiera takie pola jak: IDucznia (klucz główny), nazwisko, imie, ulica, dom, IDklasy.
Tabela Oceny zawiera pola IDoceny (klucz główny), IDucznia, Ocena, Data, IDprzedmiotu
Tabela Przedmioty zawiera takie pola jak IDprzedmiotu (klucz główny), NazwaPrzedmiotu, Nazwisko_naucz, Imie_naucz.
Tabele Przedmioty oraz Uczniowie połączone są relacją z tabelą Oceny.
Niżej znajduje się tabela o 7 wierszach podpisanych jako: Pole, Alias, Tabela, Sortowanie, Widoczny, Funkcja, Kryterium, lub.
W wierszu Pole wpisano: Nazwisko_naucz, Imie_naucz, CAST(”Oceny”.”Ocena” AS DOUBLE), IDklasy
W wierszu Alias w trzeciej komórce wpisano: Średnia
W wierszu Tabela wpisano: Przedmioty, Przedmioty, pusta komórka, Uczniowie
W wierszu Sortowanie w trzeciej komórce wpisano rosnąco
W wierszu Widoczny w trzech komórkach widnieje kwadrat ze znakiem zaznaczenia wewnątrz
W wierszu Funkcja wpisano: Grupuj, Grupuj, Średnia, Grupuj.
W wierszu Kryterium w czwartej kolumnie wpisano :IDklasy.
Wiersz lub jest pusty.

Wnioski płynące z zadania

• Model danych dziennika szkolnego nie jest poprawnie znormalizowany – patrz etap I tej serii e‑materiałów. Z tego powodu, podziału wszystkich ocen na grupy według nauczyciela, który ocenę wystawił dokonaliśmy po imieniu i nazwisku prowadzącego zajęcia.

• W przypadku ocen szkolnych często przychodzi nam używać funkcji agregującej Średnia. Nic jednak nie stoi na przeszkodzie, żeby wykorzystywać w grupowaniu także inne znane nam funkcje, na przykład: Policz, Suma itd. Dobór funkcji agregującej to ważna umiejętność inżyniera baz danych.

Zadanie 4

W tej kwerendzie wykorzystamy mechanizm łączenia zestawów danych (ang. join). Aby znaleźć uczniów, którzy nie otrzymali żadnej oceny z matematyki, dokonamy złączenia:

• tabeli Uczniowie, w której znajdują się wszyscy uczniowie szkoły,

• kwerendy, która wyjmie z tabeli Oceny jedynie oceny z matematyki.

Kiedy dokonamy złączenia tych dwóch zestawów danych w jedną tabelę (pamiętając o logicznym powiązaniu występującym w atrybucie IDucznia), okaże się, że wartości w kolumnach pochodzących z kwerendy pozostaną puste dla tych uczniów, którzy nie otrzymali żadnej noty z matematyki.

Innymi słowy: jeśli uczeń o zadanym IDucznia występował jedynie w tabeli Uczniowie, zaś nie udało się znaleźć takiej samej wartości IDucznia w wynikach kwerendy, oznacza to, że ten uczeń nie otrzymał żadnej oceny z matematyki.

Taką sytuację można wykryć w rezultatach złączenia, używając w kryterium wyszukiwania odpowiednio:

• Is Null w programie Microsoft Access,

• IS EMPTY w pakiecie LibreOffice Base.

Przedstawmy zatem teraz sposób wykorzystania tych zapisów w praktyce.

Przykładowe rozwiązanie w Microsoft Access

Rozpoczynamy od stworzenia pomocniczej kwerendy, która wyjmie z dziennika szkolnego jedynie oceny z matematyki:

RQz0mJZjW2tkD

Zrzut ekranu przedstawia kreator kwerend w programie MS Access o nazwie: Oceny z matematyki
Na górze kreatora znajdują się 2 tabele: Oceny i Przedmioty. Tabela przedmioty jest w żółtej ramce.
Tabela Oceny zawiera pola IDoceny (klucz główny), IDucznia, Ocena, Data, IDprzedmiotu
Tabela Przedmioty zawiera takie pola jak: IDprzedmiotu (klucz główny), NazwaPrzedmiotu, Nazwisko_naucz, Imie_naucz
Tabela Przedmioty połączona jest relacją jeden do wielu z tabelą Oceny.
Niżej znajduje się tabela o 6 wierszach podpisanych jako: Pole, Tabela, Sortuj, Pokaż, Kryteria, lub.
W wierszu Pole wpisano: Ocena, NazwaPrzedmiotu, IDucznia.
W wierszu Tabela wpisano: Oceny, Przedmioty, Oceny
Wiersz Sortuj jest pusty
W wierszu Pokaż znajdują się zaznaczone pola wyboru.
W wierszu Kryteria w drugiej komórce znajduje się napis: ‘matematyka’
Wiersz lub jest pusty.

W zwracanych rezultatach pojawił się dodatkowo atrybut IDucznia, którego nie sposób pominąć, ponieważ pozostaje w powiązaniu logicznym z tabelą Uczniowie. System bazodanowy musi wiedzieć, czyje oceny zwracamy w kwerendzie – bez tej wiedzy nie udała się ustalić, którzy uczniowie ocen nie posiadają.

Program Microsoft Access udostępnia nam wygodny kreator do konstruowania złączeń wyszukujących wartości Is Null w kryterium kwerendy:

Poszukujemy uczniów znajdujących się co prawda w tabeli Uczniowie, lecz nieobecnych („niepasujących”) w wynikach kwerendy wyszukującej wszystkie oceny z matematyki. Interesuje nas zatem ostatnia opcja na liście możliwych do utworzenia kwerend:

R5hVC3WykBfPp

Zrzut ekranu przedstawia okno w programie MS Access zatytułowane: Nowa kwerenda. Po lewej stronie znajduje się żółte pole, a pod spodem napis w niebieskiej ramce: Ten kreator tworzy kwerendę znajdującą rekordy (wiersze) w jednej tabeli, które nie mają rekordów pokrewnych w drugiej tabeli. Z prawej strony znajduje się okno z czterema pozycjami, z których wybrana jest: Kreator kwerend wyszukujących niepasujące dane. Na dole okna widnieją prostokątne przyciski: OK i Anuluj.

Warto wczytać się w udostępniony w powyższym oknie (patrz niebieska ramka) opis rodzaju tworzonej kwerendy – jest to dokładnie mechanika takiego złączenia, które okazuje się przydatne w tym zadaniu.

Rozpoczynamy od wskazania tabeli, w której znajdują się rekordy (w tym zadaniu są to uczniowie) być może nieposiadający rekordów pokrewnych w innym zestawie danych (w przypadku naszego zadania nieposiadający ocen z matematyki):

RER3GYCdFR2rM

Zrzut ekranu przedstawia okno w programie MS Access zatytułowane: Kreator kwerend wyszukujących niepasujące dane. Po lewej stronie znajduje się żółte pole, w centrum napis: Utworzona kwerenda pokaże rekordy wybranej poniżej tabeli, w której nie ma pokrewnych rekordów, a którą wybierzesz na następnym ekranie. Można na przykład wyszukać klientów, którzy nie złożyli zamówień. Która tabela lub kwerenda zawiera rekordy, które chcesz mieć w wyniku kwerendy? Poniżej znajduje się okno z trzema pozycjami, z których wybrana jest Tabela:Uczniowie. Pod spodem znajduje się opcja: Wyświetl i zaznaczona pozycja: Tabele. Na dole okna widnieją prostokątne przyciski: Anuluj, <Wstecz, Dalej> oraz Zakończ.

Kolejny krok jest logiczny – wskazujemy drugi zestaw danych, w którym dla powiązanego rekordu może wystąpić Is Null w wartości kryterium:

RnxEaTle09PYh

Zrzut ekranu przedstawia okno w programie MS Access zatytułowane: Kreator kwerend wyszukujących niepasujące dane. Po lewej stronie znajduje się żółte pole, w centrum napis: Która tabela lub kwerenda zawiera pokrewne rekordy? Jeśli na przykład wybrano już klientów, a teraz szukasz klientów, którzy nie złożyli zamówień, wybierz teraz tabelę: Zamówienia. Poniżej znajduje się okno z jedną, zaznaczoną w czerwonej ramce pozycją: Kwerenda: Oceny z matematyki. Pod spodem znajduje się opcja: Wyświetl i zaznaczona pozycja: Kwerendy. Na dole okna widnieją prostokątne przyciski: Anuluj, <Wstecz, Dalej> oraz Zakończ.

Teraz trzeba wskazać atrybut, który stanowi powiązanie logiczne pomiędzy dwoma określonymi wcześniej zestawami danych:

Rh0hu0riwiCvc

Zrzut ekranu przedstawia okno w programie MS Access zatytułowane: Kreator kwerend wyszukujących niepasujące dane. Po lewej stronie znajduje się żółte pole, w centrum napis: Jaka informacja jest w obu tabelach? Na przykład obie tabele, Klienci i Zamówienia, mogą mieć pole ID Klienta. Pasujące pola mogą mieć różne nazwy. Zaznacz pasujące pola w każdej tabeli i kliknij przycisk <=>. Pod spodem znajdują się dwa okna: Pola w „Uczniowie” z opcjami: IDucznia, nazwisko, imie, ulica, dom, IDklasy oraz Pola w „Oceny z matematyki” z opcjami: Ocena, NazwaPrzedmiotu, IDucznia. W obu oknach zaznaczona jest opcja: IDucznia, pomiędzy oknami widnieje znak: <=>. Poniżej, w oknie Pola pasujące znajduje się napis: IDucznia <=> IDucznia. Na dole okna widnieją prostokątne przyciski: Anuluj, <Wstecz, Dalej> oraz Zakończ.

Zgodnie z poleceniem wybieramy pożądany zestaw informacji dotyczących ucznia:

RKgXvb4KFmc0d

Zrzut ekranu przedstawia okno w programie MS Access zatytułowane: Kreator kwerend wyszukujących niepasujące dane. Po lewej stronie znajduje się żółte pole, w centrum napis: Które pola chcesz zobaczyć w wynikach kwerendy? Pod spodem znajdują się dwa okna: Dostępne pole z opcjami: IDucznia, ulica, dom oraz Wybrane Pola z opcjami: nazwisko, imie, IDklasy. W pierwszym oknie zaznaczona jest opcja: dom, w drugim opcja: ID klasy. Między oknami znajdują się przyciski: >, >>, <, <<. Na dole okna widnieją prostokątne przyciski: Anuluj, <Wstecz, Dalej> oraz Zakończ.

W ostatnim kroku nazywamy wynikową kwerendę:

R6L8YiiOMvX0l

Zrzut ekranu przedstawia okno w programie MS Access zatytułowane: Kreator kwerend wyszukujących niepasujące dane. Po lewej stronie znajduje się żółte pole, w centrum napis: Jak chcesz nazwać kwerendę? Pod spodem znajduje się prostokątne pole z wypełnione tekstem: Brak ocen z matematyki w czerwonej ramce oraz napis: To już wszystkie informacje potrzebne kreatorowi do utworzenia kwerendy. Poniżej widnieje napis: Czy chcesz obejrzeć wyniki kwerendy, czy zmienić projekt? I zaznaczona opcja: Wyświetl wyniki. Na dole okna widnieją prostokątne przyciski: Anuluj, <Wstecz, Dalej> oraz Zakończ. Przycisk Zakończ znajduje się w czerwonej ramce.

Gdy zajrzymy do projektu świeżo utworzonej przez kreator kwerendy, zauważymy, że rzeczywiście wykorzystano klauzulę Is Null w wartości kryterium:

R1MTdriTMXK4R

Zrzut ekranu przedstawia kreator kwerend w programie MS Access o nazwie: Brak ocen z matematyki
Znajdują się tu 2 tabele: Uczniowie i Oceny z matematyki
Tabela Uczniowie zawiera pola: IDucznia (klucz główny), nazwisko, imie, ulica, dom, IDklasy.

Tabela Oceny z matematyki zawiera pola Ocena, NazwaPrzedmiotu, IDucznia.
Tabela Uczniowie połączona jest relacją z tabelą Oceny z matematyki
Niżej znajduje się tabela o 6 wierszach podpisanych jako: Pole, Tabela, Sortuj, Pokaż, Kryteria, lub.
W tabeli w wierszu Pole wpisano: nazwisko, imie, IDklasy, IDucznia
W wierszu Tabela wpisano trzy razy: Uczniowie i Oceny z matematyki
Wiersz Sortuj jest pusty
W wierszu Pokaż znajdują się zaznaczone pola wyboru.
W wierszu Kryteria w piątej komórce widnieje tekst: Is Null w czerwonej ramce.
Wiersz lub jest pusty.

Poprawne wyniki kwerendy

R1C5HafnC7oic

Zrzut ekranu przedstawia tabelę o 3 kolumnach: nazwisko, imie, IDklasy
W kolumnie nazwisko znajdują się nazwiska, w kolumnie imie znajdują się imiona, w kolumnie IDklasy znajdują się symbole poszczególnych klas.

• Zwróconych rekordów: 9

Przykładowe rozwiązanie w LibreOffice Base

Rozpoczynamy od przygotowania kwerendy pomocniczej, która zawierać będzie wszystkie wystawione w dzienniku oceny z matematyki:

RFfINeNCdQu04

Zrzut ekranu przedstawia kreator kwerend w programie LibreOffice Base o nazwie Szkola.odb: Oceny z matematyki- LibreOffice Base
: Projekt kwerendy. W oknie widoczna są 2 tabele: Oceny i Przedmioty
Tabela Oceny zawiera pola IDoceny (klucz główny), IDucznia, Ocena, Data, IDprzedmiotu
Tabela Przedmioty zawiera pola : IDprzedmiotu (klucz główny), NazwaPrzedmiotu, Nazwisko_naucz, Imie_naucz
Tabele Oceny i Przedmioty są ze sobą połączone relacją
Niżej znajduje się tabela o 7 wierszach podpisanych jako: Pole, Alias, Tabela, Sortowanie, Widoczny, Funkcja, Kryterium, lub.
W wierszu Pole wpisano: Ocena, NazwaPrzedmiotu, IDucznia
Wiersz Alias jest pusty
W wierszu Tabela wpisano: Oceny, Przedmioty, Oceny
Wiersz Sortowanie jest pusty
W wierszu Widoczny w trzech komórkach widnieje kwadrat ze znakiem zaznaczenia wewnątrz
W wierszu Kryterium w drugiej komórce widnieje tekst ‘matematyka’
Wiersz Funkcja oraz lub jest pusty.

Pakiet LibreOffice Base nie udostępnia nam kreatora wyszukiwania niepasujących danych, lecz możemy sami przygotować projekt takiej kwerendy.

Po wstawieniu do danych źródłowych kwerendy tabeli Uczniowie oraz zapytania zwracającego wszystkie oceny z matematyki, tworzymy „nitkę” powiązania logicznego pomiędzy dwoma zestawami danych, po czym klikamy na nią prawym przyciskiem myszy, wybierając opcję Edycja...:

RbLhm3GZ8n2VG

Zrzut ekranu przedstawia kreator kwerend w programie LibreOffice Base o nazwie Szkola.odb: Brak ocen z matematyki- LibreOffice Base
: Projekt kwerendy. W oknie widoczna są 2 tabele: Uczniowie i Oceny z matematyki
Tabela Uczniowie zawiera pola IDucznia (klucz główny), nazwisko, imie, ulica, dom, IDklasy
Tabela Oceny z matematyki zawiera pola : Ocena, NazwaPrzedmiotu, IDucznia
Tabele Uczniowie i Oceny z matematyki są ze sobą połączone relacją
Na tym połączeniu otwarte jest okienko w opcjami: Usuń i Edycja (w czerownej ramce).
Niżej znajduje się tabela o 7 wierszach podpisanych jako: Pole, Alias, Tabela, Sortowanie, Widoczny, Funkcja, Kryterium, lub.
W wierszu Pole wpisano: IDucznia, nazwisko, imie, IDklasy, IDucznia
Wiersz Alias jest pusty
W wierszu Tabela wpisano cztery razy Uczniowie i Oceny z matematyki w piątej komórce
Wiersz Sortowanie jest pusty
W wierszu Widoczny w pięciu komórkach widnieje kwadrat ze znakiem zaznaczenia wewnątrz
W wierszu Kryterium w piątej komórce widnieje tekst IS EMPTY
Wiersz Funkcja oraz lub jest pusty.

Pamiętajmy, iż znanemu z Microsoft Access zapisowi Is Null w LibreOffice Base odpowiada wartość kryterium: IS EMPTY.

W oknie właściwości złączenia wybieramy Złączenie prawostronne. Dlaczego? Zwróćmy uwagę, że tabela Uczniowie znalazła się po prawej stronie okna (patrz niebieska ramka poniżej), stąd to właśnie do tej „pełnej” tabeli (zawierającej wszystkie dostępne wartości IDucznia) dołączamy tabelę teoretycznie „mniejszą”, gdyż zawierającą wartości IDucznia jedynie tych osób, które otrzymały oceny z matematyki:

Rw7Xz4ERy6m21

Zrzut ekranu przedstawia okno w programie LibreOffice Base o nazwie Właściwości złączenia. W oknie pozycji złączone tabele znajdują się opcje: Oceny z matematyki oraz Uczniowie (w niebieskiej ramce). W części zatytułowanej Opcje z rozwijanej listy wybrano Złączenie prawostronne. W części zatytułowanej Złączone pola W tabeli z nagłówkami: Oceny z matematyki oraz Uczniowie poniżej znajdują się napisy: IDucznia oraz IDucznia. W dole okna widać trzy przyciski: Pomoc, OK i Anuluj.

Wskazujemy w powyższym oknie atrybut IDucznia jako złączone pola.

Wnioski płynące z zadania

• Skorzystanie z zapisu Is Null w progamie Microsoft Access lub IS EMPTY w LibreOffice Base umożliwia wyszukanie rekordów nieposiadających wartości pokrewnych w innym, złączonym zestawie danych. W praktyce mogą to być np. uczniowie nieposiadający oceny z matematyki w dzienniku szkolnym czy klienci sklepu internetowego, którzy niczego jeszcze w serwisie nie zamówili.

Słownik