Zadanie 1. Dziennik ocen

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

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:

Id_ucznia Imie      Nazwisko       Klasa
123/2011  Wojciech  Banasik        IV  E
124/2011  Monika    Baranowska     IV  E
125/2011  Janusz    Czerwinski     IV  E

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

Przykład:

Id_przedmiotu   Nazwa_przedmiotu
1               j.polski
2               j.angielski
3               j.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:

Id_oceny   Data         Id_ucznia   Id_przedmiotu   Ocena
1          2014-09-08   704/2014    1               2
2          2014-09-08   312/2012    1               4
3          2014-09-08   649/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 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.

SELECT Oceny.Ocena, Oceny.Data
FROM Oceny

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

SELECT Przedmioty.Nazwa_przedmiotu, Oceny.Ocena, Oceny.Data
FROM Oceny
INNER JOIN Przedmioty ON Oceny.Id_przedmiotu = Przedmioty.Id_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.

SELECT Przedmioty.Nazwa_przedmiotu, Oceny.Ocena, Oceny.Data
FROM Oceny
INNER JOIN Przedmioty ON Oceny.Id_przedmiotu = Przedmioty.Id_przedmiotu
WHERE Oceny.Ocena = 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.

SELECT Przedmioty.Nazwa_przedmiotu, Oceny.Ocena,
    MONTH(Oceny.Data) AS Miesiac
FROM Oceny
INNER JOIN Przedmioty ON Oceny.Id_przedmiotu = Przedmioty.Id_przedmiotu
WHERE Oceny.Ocena = 5

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

RjK5FnV0csK3T

Zrzut ekranu przedstawia tabelę o nagłówkach: Nazwa_przemioty, Ocena, Miesiac.
W kolumnie Nazwa_przedmiotu znajdują się powtarzalne nazwy przedmiotów.
W kolumnie Ocena wypisana jest liczba 5.
W kolumnie miesiąc wypisana jest liczba 9.

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.

SELECT Przedmioty.Nazwa_przedmiotu,
    COUNT(Oceny.Ocena) AS [Liczba piatek],
    MONTH(Oceny.Data) AS Miesiac
FROM Oceny
INNER JOIN Przedmioty ON Oceny.Id_przedmiotu = Przedmioty.Id_przedmiotu
WHERE Oceny.Ocena = 5
GROUP BY Przedmioty.Nazwa_przedmiotu, MONTH(Oceny.Data)

Oto wynik kwerendy:

R13jq11uXHi1X

Zrzut ekranu przedstawia tabelę o nagłówkach:
Nazwa_przedmiotu, Ilosc piatek, Miesiac.
W kolumnie Nazwa przedmiotu znajdują się pogrupowane nazwy przedmiotów.
W kolumnie ilość piątek wypisano wartości liczbowe.
W kolumnie miesiąc wypisano numery będące oznaczeniem miesiąca.

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

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.

TRANSFORM COUNT(Oceny.Ocena)
SELECT Przedmioty.Nazwa_przedmiotu
FROM Oceny
INNER JOIN Przedmioty ON Oceny.Id_przedmiotu = Przedmioty.Id_przedmiotu
WHERE Oceny.Ocena = 5
GROUP BY Przedmioty.Nazwa_przedmiotu
PIVOT MONTH(Oceny.Data);

R17ruDvG0ADcX

Zrzut ekranu przedstawia tabelę o nagłówkach:
Nazwa_przedmiotu oraz nagłówki liczbowe: 9, 10, 11, 12 przedstawiające miesiąc.
Pod Nazwa_przedmiotu wypisane są nazwy przedmiotów.
Pod oznaczeniami miesięcy znajdują się wartości liczbowe określające ilość piątek z danego przedmiotu w określonym miesiącu.

Kwerenda krzyżowa w LibreOffice Base

LibreOffice Base nie obsługuje rozszerzeń TRANSFORM i PIVOT 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ć:

SELECT Przedmioty.Nazwa_przedmiotu,
  COUNT(CASE WHEN MONTH(Oceny.Data) = 9 THEN Oceny.Ocena END) AS "9",
  COUNT(CASE WHEN MONTH(Oceny.Data) = 10 THEN Oceny.Ocena END) AS "10",
  COUNT(CASE WHEN MONTH(Oceny.Data) = 11 THEN Oceny.Ocena END) AS "11",
  COUNT(CASE WHEN MONTH(Oceny.Data) = 12 THEN Oceny.Ocena END) AS "12"
FROM Oceny
INNER JOIN Przedmioty ON Oceny.Id_przedmiotu = Przedmioty.Id_przedmiotu
WHERE Oceny.Ocena = 5
GROUP BY Przedmioty.Nazwa_przedmiotu

RD3L7Yr0ok3tD

Zrzut ekranu przedstawia tabelę programu LibreOffice Writer o nagłówkach:
Nazwa_przedmiotu oraz nagłówki liczbowe: 9, 10, 11, 12 przedstawiające miesiąc.
Pod Nazwa_przedmiotu wypisane są nazwy przedmiotów.
Pod oznaczeniami miesięcy znajdują się wartości liczbowe określające ilość piątek z danego przedmiotu w określonym miesiącu. Pod spodem widoczne jest pole z formułami w kolejnych wierszach: SELECT Przedmioty.Nazwa_przedmiotu COUNT(CASE WHEN MONTH(Oceny.Data) = 9 THEN Oceny.Ocena END) AS „9”, COUNT(CASE WHEN MONTH(Oceny.Data) = 10 THEN Oceny.Ocena END) AS „10”, COUNT(CASE WHEN MONTH(Oceny.Data) = 11 THEN Oceny.Ocena END) AS „11”, COUNT(CASE WHEN MONTH(Oceny.Data) = 12 THEN Oceny.Ocena END) AS „12” FROM Oceny INNER JOIN Przedmioty ON Oceny.Id_przedmiotu = Przedmioty.Id_przedmiotu WHERE Oceny.Ocena = 5 GROUP BY Przedmioty.Nazwa_przedmiotu

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.

Rozwiązanie

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

Najpierw wypisujemy wszystkie informacje z tabeli Uczniowie.

SELECT Uczniowie.Imie, Uczniowie.Nazwisko
FROM Uczniowie

Następnie dołączamy do niej oceny.

SELECT Uczniowie.Imie, Uczniowie.Nazwisko, Oceny.Ocena
FROM Uczniowie
INNER JOIN Oceny ON Oceny.Id_ucznia = Uczniowie.Id_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ą.

SELECT Uczniowie.Imie, Uczniowie.Nazwisko, Oceny.Ocena
FROM Uczniowie
INNER JOIN Oceny ON Oceny.Id_ucznia = Uczniowie.Id_ucznia
WHERE Uczniowie.Klasa = 'IV E'

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

Zrzut ekranu przedstawia tabelę o nagłówkach:
Imie, Nazwisko, Ocena.
W kolumnie Imie wypisano Wojciech.
W kolumnie Nazwisko wypisano: Banasik.
W kolumnie ocena wypisano oceny.

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:

SELECT Uczniowie.Imie, Uczniowie.Nazwisko, Oceny.Ocena, COUNT(Oceny.Ocena) AS [Liczba ocen]
FROM Uczniowie INNER JOIN Oceny ON Oceny.Id_ucznia = Uczniowie.Id_ucznia
WHERE Uczniowie.Klasa = 'IV E'
GROUP BY Uczniowie.Imie, Uczniowie.Nazwisko, Oceny.Ocena

R1ct2niatU07E

Zrzut ekranu przedstawia tabelę o nagłówkach:
Imie, Nazwisko, Ocena, Ilosc ocen.
W kolumnie Imie wypisano alfabetycznie imiona.
W kolumnie Nazwisko wypisano nazwiska.
W kolumnie ocena wypisano oceny.
W kolumnie Ilosc Ocen wypisano sumę danej oceny dla danej osoby.

Kwerenda krzyżowa w MS Access

Wartość obliczaną (liczbę ocen) umieszczamy w funkcji TRANSFORM. Natomiast z funkcji SELECT i GROUP 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).

TRANSFORM COUNT(Oceny.Ocena)
SELECT Uczniowie.Imie, Uczniowie.Nazwisko
FROM Uczniowie INNER JOIN Oceny ON Oceny.Id_ucznia = Uczniowie.Id_ucznia
WHERE Uczniowie.Klasa = 'IV E'
GROUP BY Uczniowie.Imie, Uczniowie.Nazwisko
PIVOT Oceny.Ocena;

RYQNzq2zyeOia

Zrzut ekranu przedstawia tabelę o nagłówkach: Imie, Nazwisko oraz nagłówki liczbowe przedstawiające oceny od 1 do 6.
W kolumnie Imie wypisano imiona.
W kolumnie Nazwisko wypisano nazwiska.
W kolumnach ocen wypisano sumy danej oceny dla podanego ucznia.

Kwerenda krzyżowa w LibreOffice Base

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

SELECT Uczniowie.Imie, Uczniowie.Nazwisko,
  COUNT( CASE WHEN Oceny.Ocena = 1 THEN Oceny.Ocena END) AS "1",
  COUNT( CASE WHEN Oceny.Ocena = 2 THEN Oceny.Ocena END) AS "2",
  COUNT( CASE WHEN Oceny.Ocena = 3 THEN Oceny.Ocena END) AS "3",
  COUNT( CASE WHEN Oceny.Ocena = 4 THEN Oceny.Ocena END) AS "4",
  COUNT( CASE WHEN Oceny.Ocena = 5 THEN Oceny.Ocena END) AS "5",
  COUNT( CASE WHEN Oceny.Ocena = 6 THEN Oceny.Ocena END) AS "6"
FROM Uczniowie INNER JOIN Oceny ON Oceny.Id_ucznia = Uczniowie.Id_ucznia
WHERE Uczniowie.Klasa = 'IV E'
GROUP BY Uczniowie.Imie, Uczniowie.Nazwisko

ROkrhBRYqihFV

Zrzut ekranu przedstawia tabelę o nagłówkach: Imie, Nazwisko oraz nagłówki liczbowe przedstawiające oceny od 1 do 6.
W kolumnie Imie wypisano imiona.
W kolumnie Nazwisko wypisano nazwiska.
W kolumnach ocen wypisano ilości danej oceny dla podanego ucznia. Pod spodem widoczne jest pole z formułami w kolejnych wierszach: SELECT ”Uczniowie”.”Imie”, „Uczniowie”.”Nazwisko”, COUNT( CASE WHEN ”Oceny”. ”Ocena” = 1 THEN ”Oceny”. ”Ocena” END) AS "1", COUNT( CASE WHEN ”Oceny”. ”Ocena” = 2 THEN ”Oceny”. ”Ocena” END) AS "2", COUNT( CASE WHEN ”Oceny”. ”Ocena” = 3 THEN ”Oceny”. ”Ocena” END) AS "3", COUNT( CASE WHEN ”Oceny”. ”Ocena” = 4 THEN ”Oceny”. ”Ocena” END) AS "4", COUNT( CASE WHEN ”Oceny”. ”Ocena” = 5 THEN ”Oceny”. ”Ocena” END) AS "5", COUNT( CASE WHEN ”Oceny”. ”Ocena” = 6 THEN ”Oceny”. ”Ocena” END) AS "6" FROM ”Uczniowie” INNER JOIN ”Oceny” ON ”Oceny”. ”Id_ucznia” = ”Uczniowie”. ”Id_ucznia” WHERE ”Uczniowie”. ”Klasa” = 'IV E' GROUP BY ”Uczniowie”. ”Imie”, ”Uczniowie”. ”Nazwisko”

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:

Słownik