Zadanie 1. Dziennik ocen

Zadanie można rozwiązać w wybranym programie bazodanowym: LibreOffice Base lub MS Access. W podanych niżej rozwiązaniach uwzględniono różnice w składni poleceń obydwu programów.

Dane są trzy pliki tekstowe: Uczniowie.txt, Przedmioty.txtOceny.txt, w których zapisano oceny wystawiane uczniom w pewnym technikum informatycznym w okresie od 1.09.2014 r. do 18.12.2014 r.

RQ6LAShjZphSH

Przycisk do pobrania folderu ZIP z treścią zadania.

Plik ZIP o rozmiarze 86.67 KB w języku polskim

Pierwszy wiersz każdego z plików jest wierszem nagłówkowym, a kolumny w wierszach rozdzielone są znakami tabulacji.

Plik o nazwie Uczniowie.txt zawiera informacje dotyczące uczniów szkoły. W każdym wierszu znajduje się: identyfikator ucznia (ID_ucznia), jego imię (Imie), nazwisko (Nazwisko) oraz oznaczenie klasy za pomocą rzymskiej liczby i litery (Klasa).

Przykład:

Linia 1. Id podkreślnik ucznia Imie Nazwisko Klasa. Linia 2. 123 prawy ukośnik 2011 Wojciech Banasik IV E. Linia 3. 124 prawy ukośnik 2011 Monika Baranowska IV E. Linia 4. 125 prawy ukośnik 2011 Janusz Czerwinski IV E.

Plik Przedmioty.txt zawiera identyfikator przedmiotu (Id_przedmiotu) oraz nazwę przedmiotu (Nazwa_przedmiotu).

Przykład:

Linia 1. Id podkreślnik przedmiotu Nazwa podkreślnik przedmiotu. Linia 2. 1 j kropka polski. Linia 3. 2 j kropka angielski. Linia 4. 3 j kropka niemiecki.

W pliku Oceny.txt zapisane są w każdym wierszu: identyfikator oceny (Id_oceny), data wystawienia oceny (Data), identyfikator ucznia (Id_ucznia), identyfikator przedmiotu (Id_przedmiotu) oraz ocena (Ocena).

Przykład:

Linia 1. Id podkreślnik oceny Data Id podkreślnik ucznia Id podkreślnik przedmiotu Ocena. Linia 2. 1 2014 minus 09 minus 08 704 prawy ukośnik 2014 1 2. Linia 3. 2 2014 minus 09 minus 08 312 prawy ukośnik 2012 1 4. Linia 4. 3 2014 minus 09 minus 08 649 prawy ukośnik 2013 3 5.

Korzystając z danych zawartych w tych plikach oraz z dostępnych narzędzi informatycznych, wykonaj poniższe polecenia. Każdą odpowiedź umieść w pliku wyniki.txt, poprzedzając ją numerem odpowiedniego zadania.

Do oceny oddajesz:

  • plik wyniki.txt zawierający odpowiedzi do poszczególnych zadań (odpowiedź do każdego zadania powinna być poprzedzona jego numerem)

  • plik(i) zawierający(e) komputerową realizację twoich obliczeń

Zadanie zostało przygotowane przez Centralną Komisję Egzaminacyjną i pojawiło się w zbiorze zadań z informatyki autorstwa CKE jako zadanie nr 98.

Zadanie 1.1

Podaj zestawienie zawierające dla każdego przedmiotu liczbę piątek wystawionych w kolejnych miesiącach od września do grudnia łącznie we wszystkich klasach.

Rozwiązanie

Pierwszym krokiem jest zidentyfikowanie danych, które będą konieczne do rozwiązania zadania. W tym przypadku są to:

  • wartości ocen – aby wiedzieć, które z nich to poszukiwane piątki,

  • daty wystawienia ocen – aby dokonać grupowania ze względu na miesiące,

  • przedmiot, z którego została wystawiona ocena.

Na początek napiszmy prostą kwerendękwerendakwerendę wybierającą wszystkie potrzebne dane z tablicy Oceny.

Linia 1. SELECT Oceny kropka Ocena przecinek Oceny kropka Data. Linia 2. FROM Oceny.

Teraz należy dołączyć tablicę Przedmioty, aby uzyskać wszystkie potrzebne informacje w kwerendzie.

Linia 1. SELECT Przedmioty kropka Nazwa podkreślnik przedmiotu przecinek Oceny kropka Ocena przecinek Oceny kropka Data. Linia 2. FROM Oceny. Linia 3. INNER JOIN Przedmioty ON Oceny kropka Id podkreślnik przedmiotu znak równości Przedmioty kropka Id podkreślnik przedmiotu.

Koniecznie trzeba zamieścić warunek, który usunie z wyniku kwerendy wszystkie oceny niebędące piątkami.

Jeśli przejrzymy dane, zauważymy, że wszystkie oceny w bazie zostały wystawione między wrześniem a grudniem, więc warunek sprawdzający datę nie jest konieczny.

Linia 1. SELECT Przedmioty kropka Nazwa podkreślnik przedmiotu przecinek Oceny kropka Ocena przecinek Oceny kropka Data. Linia 2. FROM Oceny. Linia 3. INNER JOIN Przedmioty ON Oceny kropka Id podkreślnik przedmiotu znak równości Przedmioty kropka Id podkreślnik przedmiotu. Linia 4. WHERE Oceny kropka Ocena znak równości 5.

Kwerenda zawiera informację o pełnej dacie z dokładnością co do dnia. Ale potrzebny jest nam tylko numer miesiąca, w którym ocena została wystawiona. W tym celu należy zastosować funkcję MONTH(data) pobierającą numer miesiąca z daty.

Nowe pole opisujące numer miesiąca nazwijmy Miesiac.

Linia 1. SELECT Przedmioty kropka Nazwa podkreślnik przedmiotu przecinek Oceny kropka Ocena przecinek. Linia 2. MONTH otwórz nawias okrągły Oceny kropka Data zamknij nawias okrągły AS Miesiac. Linia 3. FROM Oceny. Linia 4. INNER JOIN Przedmioty ON Oceny kropka Id podkreślnik przedmiotu znak równości Przedmioty kropka Id podkreślnik przedmiotu. Linia 5. WHERE Oceny kropka Ocena znak równości 5.

Uzyskaliśmy listę wszystkich piątek wystawionych ze wszystkich przedmiotów wraz z numerem miesiąca, w którym zostały wystawione.

RjK5FnV0csK3T
Źródło: Contentplus.pl sp. z o.o., licencja: CC BY-SA 3.0.

W kolejnym kroku musimy te dane pogrupować i sprawdzić liczbę piątek dla każdego miesiąca i przedmiotu. W tym celu należy zastosować funkcję GROUP BY i umieścić tam te dwa pola - pole Nazwa_przedmiotu i pole Month(Data).

Obliczenie liczby piątek można przeprowadzić za pomocą funkcji COUNT.

Linia 1. SELECT Przedmioty kropka Nazwa podkreślnik przedmiotu przecinek. Linia 2. COUNT otwórz nawias okrągły Oceny kropka Ocena zamknij nawias okrągły AS otwórz nawias kwadratowy Liczba piatek zamknij nawias kwadratowy przecinek. Linia 3. MONTH otwórz nawias okrągły Oceny kropka Data zamknij nawias okrągły AS Miesiac. Linia 4. FROM Oceny. Linia 5. INNER JOIN Przedmioty ON Oceny kropka Id podkreślnik przedmiotu znak równości Przedmioty kropka Id podkreślnik przedmiotu. Linia 6. WHERE Oceny kropka Ocena znak równości 5. Linia 7. GROUP BY Przedmioty kropka Nazwa podkreślnik przedmiotu przecinek MONTH otwórz nawias okrągły Oceny kropka Data zamknij nawias okrągły.

Oto wynik kwerendy:

R13jq11uXHi1X
Źródło: Contentplus.pl sp. z o.o., licencja: CC BY-SA 3.0.
Ważne!

Pamiętaj, że kiedy stosujesz funkcję GROUP BY, to każda kolumna, która pojawiła się w funkcji SELECT, musi:

  • znaleźć się także w poleceniu GROUP BY,

  • lub być agregowana przez funkcję agregującąfunkcja agregującafunkcję agregującą.

W przeciwnym razie kwerenda się nie uruchomi.

W tym przypadku agregowane jest pole Ocena poprzez funkcję COUNT, a pola Month(Data)Nazwa_przedmiotu znajdują się w funkcji GROUP BY.

W wyniku wykonania tej kwerendy otrzymujemy wszystkie informacje, o które jesteśmy proszeni w zadaniu.

Teoretycznie zadanie można skończyć na tym etapie, zyskując pełną liczbę punktów na egzaminie maturalnym, ponieważ polecenie nie wymaga zestawienia tabelarycznego. Wykonamy tutaj jednak ostatnie przekształcenie, dzięki któremu uzyskamy pełnoprawną kwerendę krzyżową.

Kwerenda krzyżowa w MS Access

Usuwamy liczbę ocen i datę z wyrażeń SELECT oraz GROUP BY, a następnie umieszczamy je odpowiednio w poleceniach TRANSFORM oraz PIVOT. Ponieważ w kwerendach krzyżowych funkcje SELECT/GROUP BY definiują nazwy rekordów, a wartość PIVOT nazwy pól, otrzymamy tabelę zawierającą skondensowane zestawienie ocen względem przedmiotów i miesięcy.

Linia 1. TRANSFORM COUNT otwórz nawias okrągły Oceny kropka Ocena zamknij nawias okrągły. Linia 2. SELECT Przedmioty kropka Nazwa podkreślnik przedmiotu. Linia 3. FROM Oceny. Linia 4. INNER JOIN Przedmioty ON Oceny kropka Id podkreślnik przedmiotu znak równości Przedmioty kropka Id podkreślnik przedmiotu. Linia 5. WHERE Oceny kropka Ocena znak równości 5. Linia 6. GROUP BY Przedmioty kropka Nazwa podkreślnik przedmiotu. Linia 7. PIVOT MONTH otwórz nawias okrągły Oceny kropka Data zamknij nawias okrągły średnik.
R17ruDvG0ADcX
Źródło: Contentplus.pl sp. z o.o., licencja: CC BY-SA 3.0.

Kwerenda krzyżowa w LibreOffice Base

LibreOffice Base nie obsługuje rozszerzeń TRANSFORMPIVOT specyficznych dla MS Access, dlatego używamy tu klauzuli CASE WHEN warunek THEN wartość END. Pozwala ona pogrupować oceny według miesięcy, zanim zostaną policzone. Zapytanie przyjmie więc następującą postać:

Linia 1. SELECT Przedmioty kropka Nazwa podkreślnik przedmiotu przecinek. Linia 2. COUNT otwórz nawias okrągły CASE WHEN MONTH otwórz nawias okrągły Oceny kropka Data zamknij nawias okrągły znak równości 9 THEN Oceny kropka Ocena END zamknij nawias okrągły AS cudzysłów 9 cudzysłów przecinek. Linia 3. COUNT otwórz nawias okrągły CASE WHEN MONTH otwórz nawias okrągły Oceny kropka Data zamknij nawias okrągły znak równości 10 THEN Oceny kropka Ocena END zamknij nawias okrągły AS cudzysłów 10 cudzysłów przecinek. Linia 4. COUNT otwórz nawias okrągły CASE WHEN MONTH otwórz nawias okrągły Oceny kropka Data zamknij nawias okrągły znak równości 11 THEN Oceny kropka Ocena END zamknij nawias okrągły AS cudzysłów 11 cudzysłów przecinek. Linia 5. COUNT otwórz nawias okrągły CASE WHEN MONTH otwórz nawias okrągły Oceny kropka Data zamknij nawias okrągły znak równości 12 THEN Oceny kropka Ocena END zamknij nawias okrągły AS cudzysłów 12 cudzysłów. Linia 6. FROM Oceny. Linia 7. INNER JOIN Przedmioty ON Oceny kropka Id podkreślnik przedmiotu znak równości Przedmioty kropka Id podkreślnik przedmiotu. Linia 8. WHERE Oceny kropka Ocena znak równości 5. Linia 9. GROUP BY Przedmioty kropka Nazwa podkreślnik przedmiotu.
RD3L7Yr0ok3tD
Kwerenda krzyżowa i jej wynik w programie LibreOffice Base.
Źródło: Contentplus.pl sp. z o.o., licencja: CC BY-SA 3.0.

Na koniec otrzymaliśmy zestawienie, które pokazuje, ile zostało wystawionych piątek w poszczególnych miesiącach (od września do grudnia) we wszystkich klasach dla każdego przedmiotu.

Zadanie 1.2

Podaj zestawienie, które dla poszczególnych uczniów z klasy IV E przedstawia liczbę każdej z otrzymanych ocen: 1, 2, 3, 4, 5, 6.

Zadanie zostało opracowane przez autora e‑materiału w oparciu o treści ze zbioru zadań autorstwa CKE.

Rozwiązanie

Ponownie zaczynamy od wybrania danych, które są konieczne do utworzenia kwerendy. Tym razem są to: Imie, NazwiskoOcena dla każdego ucznia.

Najpierw wypisujemy wszystkie informacje z tabeli Uczniowie.

Linia 1. SELECT Uczniowie kropka Imie przecinek Uczniowie kropka Nazwisko. Linia 2. FROM Uczniowie.

Następnie dołączamy do niej oceny.

Linia 1. SELECT Uczniowie kropka Imie przecinek Uczniowie kropka Nazwisko przecinek Oceny kropka Ocena. Linia 2. FROM Uczniowie. Linia 3. INNER JOIN Oceny ON Oceny kropka Id podkreślnik ucznia znak równości Uczniowie kropka Id podkreślnik ucznia.

W kolejnym kroku należy wybrać tylko tych uczniów, którzy należą do klasy IV E. Można do tego użyć zwykłego porównania (=) lub funkcji LIKE, przy czym porównanie (=) ma mniejszą złożoność obliczeniową.

Linia 1. SELECT Uczniowie kropka Imie przecinek Uczniowie kropka Nazwisko przecinek Oceny kropka Ocena. Linia 2. FROM Uczniowie. Linia 3. INNER JOIN Oceny ON Oceny kropka Id podkreślnik ucznia znak równości Uczniowie kropka Id podkreślnik ucznia. Linia 4. WHERE Uczniowie kropka Klasa znak równości apostrof IV E apostrof.

W ten sposób utworzyliśmy kwerendę, która dla każdego ucznia z klasy IV E  listuje wszystkie jego oceny. Następnie należy pogrupować rekordy według uczniów oraz ocen, tak aby żaden uczeń nie był powtórzony w ramach każdej jego oceny.

RSICcZdUsPErF
Źródło: Contentplus.pl sp. z o.o., licencja: CC BY-SA 3.0.

Wynik działania tej kwerendy pokazuje wszystkie oceny, jakie otrzymali uczniowie klasy IV E, a obok znajduje się imię i nazwisko ucznia, który daną ocenę otrzymał.

Teraz, gdy rekordy są odpowiednio pogrupowane, możemy sprawdzić liczbę „wystąpień” każdej oceny za pomocą funkcji COUNT. To działa, ponieważ funkcja COUNT liczy indywidualne rekordy o tych samych wartościach pól GROUP BY – czyli sprawdza, ile razy powtórzyła się trójka: Imie, Nazwisko, Ocena, zwracając liczbę każdej z ocen dla każdego z uczniów.

Zapytanie dla MS Access:

Linia 1. SELECT Uczniowie kropka Imie przecinek Uczniowie kropka Nazwisko przecinek Oceny kropka Ocena przecinek COUNT otwórz nawias okrągły Oceny kropka Ocena zamknij nawias okrągły AS otwórz nawias kwadratowy Liczba ocen zamknij nawias kwadratowy. Linia 2. FROM Uczniowie INNER JOIN Oceny ON Oceny kropka Id podkreślnik ucznia znak równości Uczniowie kropka Id podkreślnik ucznia. Linia 3. WHERE Uczniowie kropka Klasa znak równości apostrof IV E apostrof. Linia 4. GROUP BY Uczniowie kropka Imie przecinek Uczniowie kropka Nazwisko przecinek Oceny kropka Ocena.
Ważne!

W zapytaniu dla LibreOffice Base nazwy pól zawierających spację zamykamy nie w nawiasach kwadratowych, lecz w cudzysłowie. Zatem w powyższej kwerendzie [Liczba ocen] zmieniamy na "Liczba ocen".

R1ct2niatU07E
Źródło: Contentplus.pl sp. z o.o., licencja: CC BY-SA 3.0.

Kwerenda w tej formie przedstawia wszystkie informacje konieczne do ukończenia zadania. Jednak ponownie zwiększymy jej przejrzystość poprzez przekształcenie jej do kwerendy krzyżowej.

Kwerenda krzyżowa w MS Access

Wartość obliczaną (liczbę ocen) umieszczamy w funkcji TRANSFORM. Natomiast z funkcji SELECTGROUP BY usuwamy wszystkie wartości oprócz tych, które mają być nazwami rekordów.

Następnie w funkcji PIVOT umieszczamy pole, którego wartości staną się nazwami pól. Po wstawieniu tam pola Oceny otrzymamy sześć nagłówków pól (1, 2, 3, 4, 5, 6).

Linia 1. TRANSFORM COUNT otwórz nawias okrągły Oceny kropka Ocena zamknij nawias okrągły. Linia 2. SELECT Uczniowie kropka Imie przecinek Uczniowie kropka Nazwisko. Linia 3. FROM Uczniowie INNER JOIN Oceny ON Oceny kropka Id podkreślnik ucznia znak równości Uczniowie kropka Id podkreślnik ucznia. Linia 4. WHERE Uczniowie kropka Klasa znak równości apostrof IV E apostrof. Linia 5. GROUP BY Uczniowie kropka Imie przecinek Uczniowie kropka Nazwisko. Linia 6. PIVOT Oceny kropka Ocena średnik.
RYQNzq2zyeOia
Źródło: Contentplus.pl sp. z o.o., licencja: CC BY-SA 3.0.

Kwerenda krzyżowa w LibreOffice Base

W wersji dla LibreOffice Base zamiast poleceń TRANSFORMPIVOT ponownie użyjemy klauzuli CASE WHEN warunek THEN wartość END:

Linia 1. SELECT Uczniowie kropka Imie przecinek Uczniowie kropka Nazwisko przecinek. Linia 2. COUNT otwórz nawias okrągły CASE WHEN Oceny kropka Ocena znak równości 1 THEN Oceny kropka Ocena END zamknij nawias okrągły AS cudzysłów 1 cudzysłów przecinek. Linia 3. COUNT otwórz nawias okrągły CASE WHEN Oceny kropka Ocena znak równości 2 THEN Oceny kropka Ocena END zamknij nawias okrągły AS cudzysłów 2 cudzysłów przecinek. Linia 4. COUNT otwórz nawias okrągły CASE WHEN Oceny kropka Ocena znak równości 3 THEN Oceny kropka Ocena END zamknij nawias okrągły AS cudzysłów 3 cudzysłów przecinek. Linia 5. COUNT otwórz nawias okrągły CASE WHEN Oceny kropka Ocena znak równości 4 THEN Oceny kropka Ocena END zamknij nawias okrągły AS cudzysłów 4 cudzysłów przecinek. Linia 6. COUNT otwórz nawias okrągły CASE WHEN Oceny kropka Ocena znak równości 5 THEN Oceny kropka Ocena END zamknij nawias okrągły AS cudzysłów 5 cudzysłów przecinek. Linia 7. COUNT otwórz nawias okrągły CASE WHEN Oceny kropka Ocena znak równości 6 THEN Oceny kropka Ocena END zamknij nawias okrągły AS cudzysłów 6 cudzysłów. Linia 8. FROM Uczniowie INNER JOIN Oceny ON Oceny kropka Id podkreślnik ucznia znak równości Uczniowie kropka Id podkreślnik ucznia. Linia 9. WHERE Uczniowie kropka Klasa znak równości apostrof IV E apostrof. Linia 10. GROUP BY Uczniowie kropka Imie przecinek Uczniowie kropka Nazwisko.
ROkrhBRYqihFV
Kwerenda krzyżowa i jej wynik w programie LibreOffice Base.
Źródło: Contentplus.pl sp. z o.o., licencja: CC BY-SA 3.0.

Na koniec otrzymujemy zestawienie, które pokazuje, ile każdy z uczniów dostał poszczególnych ocen.

Odpowiedzi do zadań

Odpowiedzi do zadań znajdują się w pliku wyniki.txt:

Rv84RWb5rDcp0

Przycisk do pobrania pliku TXT z treścią zadania.

Plik TXT o rozmiarze 673.00 B w języku polskim

Słownik

funkcja agregująca
funkcja agregująca

funkcja przyjmująca jako argument zbiór lub ciąg wartości, a zwracająca pojedynczą wartość jako wynik; przykładami takich funkcji są średnia, minimum, maximum

kwerenda
kwerenda

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

SQL
SQL

(od ang. Structured Query Language) strukturalny język zapytań, który stanowi standard komunikacji ze współczesnymi, relacyjnymi bazami danych