Przeczytaj
Ćwiczenia związane z grupowaniem danych przeprowadzimy na przykładzie bazy danych dziennika szkolnego, pochodzącej 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 DBMSDBMS):
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 IWprowadzenie 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.
Warto pamiętać o analizie modelu danych, której dokonaliśmy w e‑materiale: Wprowadzenie do kwerend, etap IWprowadzenie do kwerend, etap I.
Wskazaliśmy wówczas wiele niedoskonałości modelu danych, który zastosowano w podanym zadaniu maturalnym. Rozważmy np. często pojawiającą się 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.
Zadanie 1
Przygotuj zestawienie średnich ocen uzyskanych przez wszystkich uczniów klasy 2a dla każdego przedmiotu szkolnego. Podaj kolejno: nazwę przedmiotu oraz średnią ocenę klasy z danych zajęć szkolnych. Wyniki uporządkuj malejąco, według średniej oceny. Ocenę średnią zaokrąglij do czterech miejsc po przecinku.
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.
Aby można było mówić o grupowaniu danych, nie wystarczy podać nazwy atrybutu, według którego podzielimy rekordy na poszczególne grupy. Musi także istnieć cel grupowania, czyli funkcja agregująca określająca, jaki rodzaj analizy wyznaczymy dla każdej z grup.
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
![Zrzut ekranu przedstawia kreator kwerend w programie MS Access o nazwie Średnie ocen z przedmiotów 2a.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, Tabela, Suma, Sortuj, Pokaż, Kryteria, lub. W wierszu Pole wpisano: NazwaPrzedmiotu, IDklasy, Ocena. W wierszu Tabela wpisano: Przedmioty, Uczniowie, OcenyW wierszu suma wybrano Grupuj według we wszystkich 3 kolumnach. Wiersz oznaczony jest czerwonym prostokątem. W wierszu Sortuj wpisano Malejąco w ostatniej kolumnie. W wierszu Pokaż znajdują się pola wyboru. W wierszu Kryteria wpisano "2a" w 2 kolumnie Wiersz lub jest pusty.](https://static.zpe.gov.pl/portal/f/res-minimized/R18luYHHrROb2/1665414477/213ETarjqbow1wUITpfiTLcPa7xTYzJs.png)
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
:
![Zrzut ekranu przedstawia zakładkę Projektowanie, w której zaznaczono czerwonym prostokątem opcję Sumy.](https://static.zpe.gov.pl/portal/f/res-minimized/RLKVg8VuGlzql/1665414478/2MO6q2AcyqdXdT52K1ZvPtVSWxgw29xb.png)
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
:
![Zrzut ekranu przedstawia zakładkę Projektowanie, w której zaznaczono czerwonym prostokątem opcję Arkusz właściwości.](https://static.zpe.gov.pl/portal/f/res-minimized/R1Hq3MrT5nGl3/1665414478/2fvPkA3ZjUZlUzM4Kmkrbq9fCbyMb0sC.png)
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:
![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.](https://static.zpe.gov.pl/portal/f/res-minimized/R1KTb69wSLpbf/1665414478/1jgtqkHcw5LNrgGBGng5dxWOTNwynPMW.png)
Teraz powinniśmy już dysponować poprawnymi, zaokrąglonymi średnimi ocenami dla każdego przedmiotu szkolnego realizowanego w klasie 2a.
Poprawne wyniki kwerendy
![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.](https://static.zpe.gov.pl/portal/f/res-minimized/RYpQp7MmAAQpA/1665414479/2ksZYviL3854zc7cTnkm8XqipWAkG4Ao.png)
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ą:
![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.](https://static.zpe.gov.pl/portal/f/res-minimized/R1SQfbGz54WrB/1665414479/2Ys1s5li9eg6v544LD3PW72Mxc6F7ArZ.png)
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
:
![Zrzut ekranu przedstawia fragment dokumentu w programie LibreOffice Base Średnie ocen z przedmiotów 2a – Szkola LibreOffice Base : Widok danych tabeli. Na górze znajduje się pasek menu, a poniżej tabela z nagłówkami: NazwaPrzedmiotu, IDklasy, ŚredniaOcen. W komórkach tabeli znajdują się znawy przedmiotów, symbole klasy oraz średnia ocen z dokładnością do 4 miejsc po przecinku. Ten wiersz jest zaznaczony kolorem niebieskim, na jego tle znajduje się rozwinięta lista z zaznaczoną w czerwonej ramce pozycją: Formatowanie kolumn… . Na tle dokumentu znajduje się duże okno z mniejszymi okienkami. W okienku Kategoria w czerwonej ramce znajduje się opcja: Liczba, w okienku Język: Polski, w polu opcje – Miejsca dziesiętne wybrana cyfra liczba 4 (pole w czerwonej ramce), a w Zera wiodące cyfra 1. W polu Kod formatu widnieje wartość: 0,0000, a na dole okna są cztery prostokątne przyciski: Pomoc, Resetuj, OK, Anuluj.](https://static.zpe.gov.pl/portal/f/res-minimized/R12ZnNSQ8jmLU/1665414479/R3m2DZh6qVzQnrktlX87yM25dn7SEZax.png)
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:
![Zrzut ekranu przedstawia kreator kwerend w programie MS Access o nazwie Średnie ocen z przedmiotów klasa XNa 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, Tabela, Suma, Sortuj, Pokaż, Kryteria, lub. W wierszu Pole wpisano: NazwaPrzedmiotu, IDklasy, Ocena. W wierszu Tabela wpisano: Przedmioty, Uczniowie, OcenyW wierszu suma wybrano Grupuj według w 2 kolumnach i Średnia w trzeciej kolumnie. W wierszu Sortuj wpisano Malejąco w ostatniej kolumnie. W wierszu Pokaż znajdują się pola wyboru. W wierszu Kryteria wpisano [Podaj nazwę klasy] w 2 kolumnie Wiersz lub jest pusty.](https://static.zpe.gov.pl/portal/f/res-minimized/RNDTTvmbhkQ11/1665414480/3wkCKVhUf25tMBpdFtTTzDlDvFFKAZTu.png)
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:
![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.](https://static.zpe.gov.pl/portal/f/res-minimized/RYpXe2z3Nlzqy/1665414480/x6AskC751V0gd5icrDVneG5feFBE0Tbe.png)
Podstawowe zasady grupowania danych przeprowadzanego w pakietach Microsoft Access oraz LibreOffice Base brzmią więc następująco:
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.
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:
![Zrzut ekranu przedstawia kreator kwerend w programie MS Access o nazwie Ocena wyników klasy 2aNa 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.](https://static.zpe.gov.pl/portal/f/res-minimized/RXEDXYPwAP95u/1665414480/1wheE2ms5Qhg0ZbiwWnMAvI307VQOMPi.png)
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
![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.](https://static.zpe.gov.pl/portal/f/res-minimized/RJxYrcjRtCTqF/1665414481/1jRVJAQ8jswIIxGv4tAERw91Zh2ysuEl.png)
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:
![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.](https://static.zpe.gov.pl/portal/f/res-minimized/RpQloaoAbPxCC/1665414481/2A4KWGejyoDfQxGRrzpjZxtEZ0VnRUsC.png)
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:
![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.](https://static.zpe.gov.pl/portal/f/res-minimized/Rk9Gqz41zopdQ/1665414481/1Xm9uRsQ4oJgOjJ4P1cRd2868UDNhRH2.png)
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
Przygotuj zestawienie pięciu najlepszych uczniów szkoły, czyli osób, które uzyskały najwyższą średnią ze wszystkich otrzymanych ocen. Podaj kolejno: wartość obliczonej średniej z dokładnością do czterech miejsc dziesiętnych, imię i nazwisko ucznia, jego identyfikator w systemie bazodanowym oraz nazwę klasy, do której uczęszcza.
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
![Zrzut ekranu przedstawia kreator kwerend w programie MS Access o nazwie: Top uczniowie szkoły wg średniej Z menu programu wybrana jest zakładka Projektowanie, w której zaznaczono opcję Sumy i Nazwy tabel, a w czerwonym prostokącie w polu Zwróć znajduje się wartość 5. Na górze kreatora znajdują się 2 tabele: Oceny i Uczniowie. 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. Tabela Uczniowie połączona jest 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: Ocena, imie, nazwisko, IDucznia (komorka oznaczona jest czerwonym prostokątem), IDklasy. W wierszu Tabela wpisano: Oceny, i cztery razy: Uczniowie.W wierszu Suma wybrano: Średnia i cztery razy: Grupuj według W wierszu Sortuj wpisano Malejąco w pierwszej kolumnie. W wierszu Pokaż znajdują się zaznaczone pola wyboru. Wiersz Kryteria oraz lub jest pusty.](https://static.zpe.gov.pl/portal/f/res-minimized/R1XWplMJIPSyj/1665414481/slDbG2t1dTLumo0YheVNnpXoxH4MrRoy.png)
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.
Zwróćmy uwagę – obecność atrybutu unikalnego IDucznia w rezultatach kwerendy pozwala uniknąć błędu wynikającego z ewentualnej zbieżności nazwisk uczniów.
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
![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.](https://static.zpe.gov.pl/portal/f/res-minimized/R1TeivIYPZQpG/1665414482/1NWOdXf0Dc6AcssWgdUvGjyjJKypsuZP.png)
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:
![Zrzut ekranu przedstawia kreator kwerend w programie LibreOffice Base o nazwie Szkola.odb: Top uczniowie wg średniej- LibreOffice Base : Projekt kwerendy. W górnej części znajduje się menu programu z wybraną cyfrą 5 z prawej strony. Cyfra ta znajduje się w czerwonej ramce. Na górze kreatora znajdują się 2 tabele: Oceny i Uczniowie 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 Oceny i Uczniowie 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: CAST(”Oceny”.”Ocena” AS DOUBLE), imie, nazwisko, IDucznia (komórka w czerwonej ramce), IDklasy W wierszu Alias w pierwszej komórce wpisano: Średnia W wierszu Tabela pierwsza komórka jest pusta, w kolejnych wpisano: Uczniowie, Uczniowie, Oceny, Uczniowie W wierszu Sortowanie w pierwszej komórce wpisano: malejąco W wierszu Widoczny w pięciu komórkach widnieje kwadrat ze znakiem zaznaczenia wewnątrz W wierszu Funkcja wpisano: Średnia, Grupuj, Grupuj, Grupuj, Grupuj Wiersz Kryterium oraz lub jest pusty.](https://static.zpe.gov.pl/portal/f/res-minimized/R15UNyP0KlSTz/1665414482/1cKTGV0RICHIdO0fNXm0ibD4SfKfAmo5.png)
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:
![Zrzut ekranu przedstawia kreator kwerend w programie MS Access o nazwie: Top uczniowie klasy X wg średniej Z menu programy wybrana jest zakładka Projektowanie, w której zaznaczono opcję Sumy i Nazwy tabel, a w czerwonym prostokącie w polu Zwróć znajduje się wartość 5. Na górze kreatora znajdują się 2 tabele: Oceny i Uczniowie. Tabela Oceny zawiera pola IDoceny (klucz główny), IDucznia, Ocena, Data, IDprzedmiotu Tabela Uczniowie zawiera pola: IDucznia (klucz główny), nazwisko, imie, ulica, dom, IDklasy. Tabela Uczniowie połączona jest 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 tabeli w wierszu Pole wpisano: Ocena, imie, nazwisko, IDucznia (komorka oznaczona jest czerwonym prostokątem), IDklasy. W wierszu Tabela wpisano: Oceny, i cztery razy: Uczniowie.W wierszu Suma wybrano: Średnia i cztery razy: Grupuj według W wierszu Sortuj wpisano Malejąco w pierwszej kolumnie. W wierszu Pokaż znajdują się zaznaczone pola wyboru. W wierszu Kryteria w piątej komórce widnieje tekst: [Podaj nazwę klasy] Wiersz lub jest pusty.](https://static.zpe.gov.pl/portal/f/res-minimized/Rfn6RFy6ZfaUP/1665414482/1PNIYwjvHIXF4vM1bDjr29nbXubLbeKY.png)
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
:
![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.](https://static.zpe.gov.pl/portal/f/res-minimized/R1KApyTHoevUQ/1665414483/ZTTujozB6ho4QANLpm9rUduTyUklk8r.png)
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
.
![Zrzut ekranu przedstawia kreator kwerend w programie LibreOffice Base o nazwie Szkola.odb: Top uczniowie klasy X wg średniej- LibreOffice Base : Projekt kwerendy. W górnej części znajduje się menu programu z wybraną cyfrą 5 z prawej strony. Cyfra ta znajduje się w czerwonej ramce. Na górze kreatora znajdują się 2 tabele: Oceny i Uczniowie 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 Oceny i Uczniowie 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: CAST(”Oceny”.”Ocena” AS DOUBLE), imie, nazwisko, IDucznia (komórka w czerwonej ramce), IDklasy W wierszu Alias w pierwszej komórce wpisano: Średnia W wierszu Tabela pierwsza komórka jest pusta, w kolejnych wpisano: Uczniowie, Uczniowie, Oceny, Uczniowie W wierszu Sortowanie w pierwszej komórce wpisano: malejąco W wierszu Widoczny w pięciu komórkach widnieje kwadrat ze znakiem zaznaczenia wewnątrz W wierszu Funkcja wpisano: Średnia, Grupuj, Grupuj, Grupuj, Grupuj W wierszu Kryterium w piatej komórce wipsano :IDklasy Wiersz lub jest pusty.](https://static.zpe.gov.pl/portal/f/res-minimized/ROonOYIMOLRRQ/1665414483/ma7q2zgcx7xybA0TEt23maaMLskneiKu.png)
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 przezX
rozumiemy liczbę zwracanych przez kwerendę wierszy.
Zadanie 3
Stwórz zestawienie średniej wartości ze wszystkich ocen, które wystawił w dzienniku każdy z nauczycieli. Podaj kolejno: nazwisko i imię nauczyciela oraz średnią ze wszystkich ocen umieszczonych w dzienniku przez tę osobę. Zestawienie uporządkuj rosnąco według wartości średniej, czyli od osoby teoretycznie oceniającej uczniów najsurowiej, zaś wartość średniej podaj z dokładnością do czterech miejsc po przecinku.
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.
Jak wspominaliśmy w analizie modelu danych przeprowadzonej jeszcze w etapie I niniejszych ćwiczeń: Wprowadzenie do kwerend, etap IWprowadzenie do kwerend, etap I, w bazie danych powinna istnieć tabela Nauczyciele, dzięki czemu każdy wykładowca posiadałby własny unikalny identyfikator – wówczas nawet ewentualna zbieżność imion czy nazwisk nauczycieli nie stanowiłaby najmniejszego problemu.
Przykładowe rozwiązanie w Microsoft Access
![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, OcenyW 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.](https://static.zpe.gov.pl/portal/f/res-minimized/R1FJ12SK84mz8/1665414483/1hNW6lYN89KCrCDw8tGcQmzLpO6uB6RD.png)
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.
![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.](https://static.zpe.gov.pl/portal/f/res-minimized/RuivrcB7OkV5v/1665414484/1kJcTGo74EtdN6wasxvZR2coQajPcVJa.png)
Zwróconych rekordów: 11
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:
![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, OcenyW 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.](https://static.zpe.gov.pl/portal/f/res-minimized/RSNEAcSE7DMU1/1665414484/1jFLat3CPJQkbty2IoanA6AcforegzpE.png)
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.
![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.](https://static.zpe.gov.pl/portal/f/res-minimized/RksAPAOgsFh4n/1665414484/yqVqS5Npk1aqDBNPXoEKcsjcZ4ziXKVE.png)
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:
![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.](https://static.zpe.gov.pl/portal/f/res-minimized/RQDsfjwkz5cuP/1665414484/1rEmvHt9Hpd3OSfF3K0ST4HKGiC0ddPT.png)
Wersja parametryczna zapytania w LibreOffice Base
Ponownie używamy operatora dwukropka oraz nazwy atrybutu, którego wartość wprowadzimy z klawiatury:
![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.](https://static.zpe.gov.pl/portal/f/res-minimized/RkuTqImzEsTdd/1665414485/1DdOVytgHikmyLlBQVyjiFCH8PUgOAOj.png)
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
Przygotuj zestawienie wszystkich uczniów w szkole, którym nie wystawiono w dzienniku ani jednej oceny z matematyki. Podaj kolejno: nazwisko i imię ucznia oraz IDklasy, do której uczęszcza.
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:
![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.](https://static.zpe.gov.pl/portal/f/res-minimized/RQz0mJZjW2tkD/1665414485/23nrCHl38QUQkul2fOIHdXX3N4yl7yPh.png)
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:
![Zrzut ekranu przedstawia pasek menu w programie MS Access . Wybrana jest zakładka Tworzenie oraz ikonka wraz z napisem: Kreator kwerend. Znajduje się ona w czerwonej ramce.](https://static.zpe.gov.pl/portal/f/res-minimized/RjfLCrsZERtjl/1665414485/1F6KoRabnG66ASoqbT5eqvfNFLQxXSzL.png)
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:
![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.](https://static.zpe.gov.pl/portal/f/res-minimized/R5hVC3WykBfPp/1665414485/muj34IMYok99ZuXGOJJoUOVmZUQ8aT1s.png)
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):
![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.](https://static.zpe.gov.pl/portal/f/res-minimized/RER3GYCdFR2rM/1665414486/8g78onVqwRyC3grXXNxP7Ja6cpZBZl1Y.png)
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:
![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.](https://static.zpe.gov.pl/portal/f/res-minimized/RnxEaTle09PYh/1665414486/1szAPOLoL67RvtOAsCvSdgQwMkRo96OF.png)
Teraz trzeba wskazać atrybut, który stanowi powiązanie logiczne pomiędzy dwoma określonymi wcześniej zestawami danych:
![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.](https://static.zpe.gov.pl/portal/f/res-minimized/Rh0hu0riwiCvc/1665414486/1zp2DTDr6rOOBjEXYqVMnjTntTwcu4Qc.png)
Zgodnie z poleceniem wybieramy pożądany zestaw informacji dotyczących ucznia:
![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.](https://static.zpe.gov.pl/portal/f/res-minimized/RKgXvb4KFmc0d/1665414487/2RX3NvMiwoF8tC5ncSwvlpuJRLeYzoFK.png)
W ostatnim kroku nazywamy wynikową kwerendę:
![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.](https://static.zpe.gov.pl/portal/f/res-minimized/R6L8YiiOMvX0l/1665414487/2einI8W425gTtPKmkYDFcURx9QOUm16o.png)
Gdy zajrzymy do projektu świeżo utworzonej przez kreator kwerendy, zauważymy, że rzeczywiście wykorzystano klauzulę Is Null
w wartości kryterium:
![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.](https://static.zpe.gov.pl/portal/f/res-minimized/R1MTdriTMXK4R/1665414487/19EVpl35wfhYK7bwUlUJKhpw0vhLTN7k.png)
Poprawne wyniki kwerendy
![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.](https://static.zpe.gov.pl/portal/f/res-minimized/R1C5HafnC7oic/1665414487/17zToxi3O8wF0qVCqC3nTXtlmNVIkWhG.png)
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:
![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.](https://static.zpe.gov.pl/portal/f/res-minimized/RFfINeNCdQu04/1665414488/FowVbi6rv5xAGrMHrcS0S0G1OykLM1x5.png)
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...
:
![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.](https://static.zpe.gov.pl/portal/f/res-minimized/RbLhm3GZ8n2VG/1665414488/DO3VscUgy4maA5ODNX9AoR89X2MjBcLz.png)
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:
![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.](https://static.zpe.gov.pl/portal/f/res-minimized/Rw7Xz4ERy6m21/1665414488/2D8bRscPkcGk9J12ZPas2JrZc5Smrt3B.png)
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 lubIS 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
(ang. query – zapytanie); jest to zapisane z użyciem języka SQL
polecenie skierowane do systemu bazodanowego, którego celem może być: pobranie, dodanie, modyfikacja lub usunięcie danych albo sposobu ich przechowywania w systemie
(ang. Database Management System) lub w wersji polskiej: SZBD (System Zarządzania Bazą Danych) to 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: MySQL, MariaDB, FireBird, PostgreSQL, Microsoft SQL Server, SQLite, Oracle Database, Microsoft Access, LibreOffice Base