Spis treści

Wprowadzenie

Przeczytaj

Prezentacja multimedialna

Sprawdź się

Dla nauczyciela

Kwerendy krzyżowe – zadania maturalne

Źródło: Zach Lucero, domena publiczna.

W tym e-materiale zmierzysz się z zadaniami z oficjalnego zbioru zadań maturalnych oraz wzorowanymi na nich ćwiczeniami. Sprawdzą one twoje umiejętności radzenia sobie z kwerendami krzyżowymi, a także z podobnymi do nich zestawieniami danych.

Pomocne będą szczegółowe instrukcje objaśniające krok po kroku schemat oraz techniki rozwiązywania tego typu zadań. Wykorzystane zostaną także pliki danych z oficjalnych zbiorów maturalnych, a ich rozwiązania będą objaśnione w kodzie SQL.

Więcej informacji o kwerendach krzyżowych znajdziesz w e-materiałach:

- Kwerendy krzyżowe, etap IOtwiera się w nowym oknie,

- Kwerendy krzyżowe, etap IIOtwiera się w nowym oknie.

Twoje cele

- Przeanalizujesz rozwiązania przykładowych zadań typu maturalnego dotyczących kwerend krzyżowych.

- Rozwiążesz przykładowe zadania maturalne, wymagające znajomości kwerend krzyżowych.

- Prześledzisz sposoby rozwiązywania zadań bazujących na złożonych zestawieniach danych.

Przeczytaj

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.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.

Plik 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:

1

Id_ucznia Imie

Nazwisko

Klasa

2

123/2011

Wojciech

Banasik

IV

E

3

124/2011

Monika

Baranowska

IV

E

4

125/2011

Janusz

Czerwinski

IV

E

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

Przykład:

1

Id_przedmiotu

Nazwa_przedmiotu

2

1

j.polski

3

2

j.angielski

4

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:

1

Id_oceny

Data

Id_ucznia

Id_przedmiotu

Ocena

2

1

2014-09-08

704/2014

1

2

3

2

2014-09-08

312/2012

1

4

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 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ę wybierającą wszystkie potrzebne dane z tablicy Oceny.

1

SELECT Oceny.Ocena, Oceny.Data

2

FROM Oceny

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

1

SELECT Przedmioty.Nazwa_przedmiotu, Oceny.Ocena, Oceny.Data

2

FROM Oceny

3

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.

1

SELECT Przedmioty.Nazwa_przedmiotu, Oceny.Ocena, Oceny.Data

2

FROM Oceny

3

INNER JOIN Przedmioty ON Oceny.Id_przedmiotu = Przedmioty.Id_przedmiotu

4

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.

1

SELECT Przedmioty.Nazwa_przedmiotu, Oceny.Ocena,

2

MONTH(Oceny.Data) AS Miesiac

3

FROM Oceny

4

INNER JOIN Przedmioty ON Oceny.Id_przedmiotu = Przedmioty.Id_przedmiotu

5

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.

Ź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.

1

SELECT Przedmioty.Nazwa_przedmiotu,

2

COUNT(Oceny.Ocena) AS [Liczba piatek],

3

MONTH(Oceny.Data) AS Miesiac

4

FROM Oceny

5

INNER JOIN Przedmioty ON Oceny.Id_przedmiotu = Przedmioty.Id_przedmiotu

6

WHERE Oceny.Ocena = 5

7

GROUP BY Przedmioty.Nazwa_przedmiotu, MONTH(Oceny.Data)

Oto wynik kwerendy:

Ź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ą.

W przeciwnym razie kwerenda się nie uruchomi.

W tym przypadku agregowane jest pole Ocena poprzez funkcję COUNT, a pola Month(Data) i 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.

1

TRANSFORM COUNT(Oceny.Ocena)

2

SELECT Przedmioty.Nazwa_przedmiotu

3

FROM Oceny

4

INNER JOIN Przedmioty ON Oceny.Id_przedmiotu = Przedmioty.Id_przedmiotu

5

WHERE Oceny.Ocena = 5

6

GROUP BY Przedmioty.Nazwa_przedmiotu

7

PIVOT MONTH(Oceny.Data);

Ź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ń 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ć:

1

SELECT Przedmioty.Nazwa_przedmiotu,

2

COUNT(CASE WHEN MONTH(Oceny.Data) = 9 THEN Oceny.Ocena END) AS "9",

3

COUNT(CASE WHEN MONTH(Oceny.Data) = 10 THEN Oceny.Ocena END) AS "10",

4

COUNT(CASE WHEN MONTH(Oceny.Data) = 11 THEN Oceny.Ocena END) AS "11",

5

COUNT(CASE WHEN MONTH(Oceny.Data) = 12 THEN Oceny.Ocena END) AS "12"

6

FROM Oceny

7

INNER JOIN Przedmioty ON Oceny.Id_przedmiotu = Przedmioty.Id_przedmiotu

8

WHERE Oceny.Ocena = 5

9

GROUP BY Przedmioty.Nazwa_przedmiotu

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, Nazwisko i Ocena dla każdego ucznia.

Najpierw wypisujemy wszystkie informacje z tabeli Uczniowie.

1

SELECT Uczniowie.Imie, Uczniowie.Nazwisko

2

FROM Uczniowie

Następnie dołączamy do niej oceny.

1

SELECT Uczniowie.Imie, Uczniowie.Nazwisko, Oceny.Ocena

2

FROM Uczniowie

3

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ą.

1

SELECT Uczniowie.Imie, Uczniowie.Nazwisko, Oceny.Ocena

2

FROM Uczniowie

3

INNER JOIN Oceny ON Oceny.Id_ucznia = Uczniowie.Id_ucznia

4

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.

Ź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:

1

SELECT Uczniowie.Imie, Uczniowie.Nazwisko, Oceny.Ocena, COUNT(Oceny.Ocena) AS [Liczba ocen]

2

FROM Uczniowie INNER JOIN Oceny ON Oceny.Id_ucznia = Uczniowie.Id_ucznia

3

WHERE Uczniowie.Klasa = 'IV E'

4

GROUP BY Uczniowie.Imie, Uczniowie.Nazwisko, Oceny.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".

Ź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 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).

1

TRANSFORM COUNT(Oceny.Ocena)

2

SELECT Uczniowie.Imie, Uczniowie.Nazwisko

3

FROM Uczniowie INNER JOIN Oceny ON Oceny.Id_ucznia = Uczniowie.Id_ucznia

4

WHERE Uczniowie.Klasa = 'IV E'

5

GROUP BY Uczniowie.Imie, Uczniowie.Nazwisko

6

PIVOT Oceny.Ocena;

Ź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ń TRANSFORM i PIVOT ponownie użyjemy klauzuli CASE WHEN warunek THEN wartość END:

1

SELECT Uczniowie.Imie, Uczniowie.Nazwisko,

2

COUNT( CASE WHEN Oceny.Ocena = 1 THEN Oceny.Ocena END) AS "1",

3

COUNT( CASE WHEN Oceny.Ocena = 2 THEN Oceny.Ocena END) AS "2",

4

COUNT( CASE WHEN Oceny.Ocena = 3 THEN Oceny.Ocena END) AS "3",

5

COUNT( CASE WHEN Oceny.Ocena = 4 THEN Oceny.Ocena END) AS "4",

6

COUNT( CASE WHEN Oceny.Ocena = 5 THEN Oceny.Ocena END) AS "5",

7

COUNT( CASE WHEN Oceny.Ocena = 6 THEN Oceny.Ocena END) AS "6"

8

FROM Uczniowie INNER JOIN Oceny ON Oceny.Id_ucznia = Uczniowie.Id_ucznia

9

WHERE Uczniowie.Klasa = 'IV E'

10

GROUP BY Uczniowie.Imie, Uczniowie.Nazwisko

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:

Plik o rozmiarze 673.00 B w języku polskim

Słownik

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

(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

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

Prezentacja multimedialna

Zadanie 2. Lista zamówień

W plikach kategorie.txt, produkty.txt, dostawcy.txt zapisano dane z firmy zajmującej się produktami spożywczymi.

Pierwszy wiersz w każdym z plików jest wierszem nagłówkowym. Dane w każdym wierszu oddzielono średnikami.

Plik o rozmiarze 849.00 B w języku polskim

W pliku kategorie.txt zapisano informacje o kategoriach produktów. Każdy wiersz zawiera następujące dane:

- IdKategorii – identyfikator kategorii,

- NazwaKategorii – nazwa kategorii.

Przykład:

1

IdKategorii;NazwaKategorii

2

1;Napoje

3

2;Desery

4

3;Zupy

W pliku produkty.txt zapisano informacje o produktach dostępnych w ofercie tej firmy. Każdy wiersz zawiera następujące dane:

- IdProduktu – identyfikator produktu,

- NazwaProduktu – nazwa produktu,

- IdKategorii – identyfikator kategorii,

- CenaJednostkowa – cena, którą należy zapłacić za jedną sztukę produktu,

- IdDostawcy – identyfikator dostawcy.

Przykład:

1

IdProduktu;NazwaProduktu;IdKategorii;CenaJednostkowa;IdDostawcy

2

1;Pomidorowa;3;25;2

3

2;Sok;1;10;3

4

3;Soda;1;9;3

W pliku dostawcy.txt zapisano informacje o dostawcach produktów do firmy. Każdy wiersz zawiera następujące dane:

- IdDostawcy – identyfikator dostawcy,

- NazwaFirmy – nazwa firmy dostawcy,

- Email – adres e-mail dostawcy.

Przykład:

1

IdDostawcy;NazwaFirmy;Email

2

1;OOO;ooo@gmail.com

3

2;FTW;ftw@gmail.com

4

3;Ekipka;ekipka@gmail.com

Wykorzystaj dostępne narzędzia informatyczne i przedstaw rozwiązania poniższych zadań. Odpowiedzi zapisz w pliku wyniki.txt. Każdą odpowiedź poprzedź cyfrą oznaczającą numer zadania.

Zaimportuj dane do wybranego przez siebie oprogramowania (MS Access lub LibreOffice Base), utwórz relacje oraz napisz/zaprojektuj kwerendy dające rozwiązanie.

Zadanie 2.1

Utwórz zestawienie, w którym dla każdej kategorii oraz dostawcy podasz, ile produktów z danej kategorii dostarcza jeden dostawca. Niech w kolumnach będą przedstawione kategorie, a w rzędach nazwa firmy dostawcy.

Przykład:

1

Dostawca

Śniadania

Obiady

Kolacje

2

1

30

20

15

3

2

9

8

7

4

3

6

9

12

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ń

Polecenie 1

Wykonaj zadania 2.1 i 2.2 w wybranym przez siebie programie do obsługi baz danych.

Polecenie 2

Porównaj swoje rozwiązanie z przedstawionym w prezentacji.

Rozwiązanie dla programu MS Access

- Nagranie audio

play_circle_outlineOdtwórz/Zatrzymaj  00:00

Szukaj

00:10

volume_upWycisz dźwięk

Głośność

Materiał audio dostępny pod adresem:

file:///tmp/puppeteersoQM05.html

Na początku tworzymy bazę danych i importujemy dane z plików tekstowych do programu MS Access, aby utworzyć tabele.

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

- Nagranie audio

play_circle_outlineOdtwórz/Zatrzymaj  00:00

Szukaj

00:06

volume_upWycisz dźwięk

Głośność

Materiał audio dostępny pod adresem:

file:///tmp/puppeteersoQM05.html

Gdy posiadamy już dane z plików, możemy przejść do widoku relacji.

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

- Nagranie audio

play_circle_outlineOdtwórz/Zatrzymaj  00:00

Szukaj

00:05

volume_upWycisz dźwięk

Głośność

Materiał audio dostępny pod adresem:

file:///tmp/puppeteersoQM05.html

Stwórzmy następujące relacje jeden do wielu:

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

- Nagranie audio

play_circle_outlineOdtwórz/Zatrzymaj  00:00

Szukaj

00:11

volume_upWycisz dźwięk

Głośność

Materiał audio dostępny pod adresem:

file:///tmp/puppeteersoQM05.html

Po wczytaniu danych oraz stworzeniu relacji możemy przejść do stworzenia kwerendy. Tworzymy nową kwerendę i przechodzimy do widoku SQL.

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

- Nagranie audio

play_circle_outlineOdtwórz/Zatrzymaj  00:00

Szukaj

00:07

volume_upWycisz dźwięk

Głośność

Materiał audio dostępny pod adresem:

file:///tmp/puppeteersoQM05.html

Następnie tworzymy zapytanie, które zwróci nazwy firm dostarczających produkty:

Linia 1. SELECT NazwaFirmy Linia 2. FROM otwórz nawias okrągły Produkty Linia 3. INNER JOIN Kategorie ON Kategorie kropka IdKategorii znak równości Produkty kropka IdKategorii zamknij nawias okrągły Linia 4. INNER JOIN Dostawcy ON Dostawcy kropka IdDostawcy znak równości Produkty kropka IdDostawcy

1

SELECT NazwaFirmy

2

FROM (Produkty

3

INNER JOIN Kategorie ON Kategorie.IdKategorii = Produkty.IdKategorii)

4

INNER JOIN Dostawcy ON Dostawcy.IdDostawcy = Produkty.IdDostawcy

- Nagranie audio

play_circle_outlineOdtwórz/Zatrzymaj  00:00

Szukaj

00:08

volume_upWycisz dźwięk

Głośność

Materiał audio dostępny pod adresem:

file:///tmp/puppeteersoQM05.html

Dodajemy operację grupowania po nazwach firm, która przyda się do stworzenia kwerendy krzyżowej:

Linia 1. SELECT NazwaFirmy Linia 2. FROM otwórz nawias okrągły Produkty Linia 3. INNER JOIN Kategorie ON Kategorie kropka IdKategorii znak równości Produkty kropka IdKategorii zamknij nawias okrągły Linia 4. INNER JOIN Dostawcy ON Dostawcy kropka IdDostawcy znak równości Produkty kropka IdDostawcy Linia 5. GROUP BY NazwaFirmy

1

SELECT NazwaFirmy

2

FROM (Produkty

3

INNER JOIN Kategorie ON Kategorie.IdKategorii = Produkty.IdKategorii)

4

INNER JOIN Dostawcy ON Dostawcy.IdDostawcy = Produkty.IdDostawcy

5

GROUP BY NazwaFirmy

- Nagranie audio

play_circle_outlineOdtwórz/Zatrzymaj  00:00

Szukaj

00:09

volume_upWycisz dźwięk

Głośność

Materiał audio dostępny pod adresem:

file:///tmp/puppeteersoQM05.html

Na koniec za pomocą poleceń TRANSFORM oraz PIVOT tworzymy kwerendę krzyżową, która zwraca wynik w pożądanej formie:

Linia 1. TRANSFORM COUNT otwórz nawias okrągły IdProduktu zamknij nawias okrągły Linia 2. SELECT NazwaFirmy Linia 3. FROM otwórz nawias okrągły Produkty Linia 4. INNER JOIN Kategorie ON Kategorie kropka IdKategorii znak równości Produkty kropka IdKategorii zamknij nawias okrągły Linia 5. INNER JOIN Dostawcy ON Dostawcy kropka IdDostawcy znak równości Produkty kropka IdDostawcy Linia 6. GROUP BY NazwaFirmy Linia 7. PIVOT NazwaKategorii

1

TRANSFORM COUNT(IdProduktu)

2

SELECT NazwaFirmy

3

FROM (Produkty

4

INNER JOIN Kategorie ON Kategorie.IdKategorii = Produkty.IdKategorii)

5

INNER JOIN Dostawcy ON Dostawcy.IdDostawcy = Produkty.IdDostawcy

6

GROUP BY NazwaFirmy

7

PIVOT NazwaKategorii

- Nagranie audio

play_circle_outlineOdtwórz/Zatrzymaj  00:00

Szukaj

00:04

volume_upWycisz dźwięk

Głośność

Materiał audio dostępny pod adresem:

file:///tmp/puppeteersoQM05.html

W ten sposób otrzymujemy gotowe zestawienie.

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

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

Rozwiązanie dla programu LibreOffice Base

- Nagranie audio

play_circle_outlineOdtwórz/Zatrzymaj  00:00

Szukaj

00:09

volume_upWycisz dźwięk

Głośność

Materiał audio dostępny pod adresem:

file:///tmp/puppeteersoQM05.html

W programie LibreOffice Base tworzymy nową bazę danych, którą zapisujemy pod nazwą lista_zamowien.odb.

- Nagranie audio

play_circle_outlineOdtwórz/Zatrzymaj  00:00

Szukaj

00:11

volume_upWycisz dźwięk

Głośność

Materiał audio dostępny pod adresem:

file:///tmp/puppeteersoQM05.html

Kolejne pliki z danymi otwieramy w arkuszu kalkulacyjnym LibreOffice Calc. Podczas importu w opcjach separatora wybieramy średnik.

- Nagranie audio

play_circle_outlineOdtwórz/Zatrzymaj  00:00

Szukaj

00:29

volume_upWycisz dźwięk

Głośność

Materiał audio dostępny pod adresem:

file:///tmp/puppeteersoQM05.html

W programie LibreOffice Calc zaznaczamy tabelę z danymi, kopiujemy i wklejamy za pomocą polecenia Edycja | Wklej w programie LibreOffice Base. Podczas tworzenia tabel dla każdego pola określamy typ importowanych danych i odmawiamy tworzenia kluczy podstawowych. Po utworzeniu i zaimportowaniu danych odpowiednie pola przekształcamy w trybie edycji tabeli na klucze podstawowe.

- Nagranie audio

play_circle_outlineOdtwórz/Zatrzymaj  00:00

Szukaj

00:14

volume_upWycisz dźwięk

Głośność

Materiał audio dostępny pod adresem:

file:///tmp/puppeteersoQM05.html

Po utworzeniu wszystkich tabel, zdefiniowaniu kluczy podstawowych i dołączeniu danych przechodzimy do okna Projekt relacji i tworzymy pokazane na zrzucie relacje jeden do wielu.

- Nagranie audio

play_circle_outlineOdtwórz/Zatrzymaj  00:00

Szukaj

00:11

volume_upWycisz dźwięk

Głośność

Materiał audio dostępny pod adresem:

file:///tmp/puppeteersoQM05.html

W kolejnym kroku możemy rozpocząć tworzenie zapytań. Klikamy ikonę Kwerendy i wybieramy polecenie Utwórz kwerendę SQL...

- Nagranie audio

play_circle_outlineOdtwórz/Zatrzymaj  00:00

Szukaj

00:06

volume_upWycisz dźwięk

Głośność

Materiał audio dostępny pod adresem:

file:///tmp/puppeteersoQM05.html

Następnie tworzymy zapytanie, które zwróci nazwy firm dostarczających produkty:

Linia 1. SELECT NazwaFirmy Linia 2. FROM Produkty Linia 3. INNER JOIN Kategorie ON Kategorie kropka IdKategorii znak równości Produkty kropka IdKategorii Linia 4. INNER JOIN Dostawcy ON Dostawcy kropka IdDostawcy znak równości Produkty kropka IdDostawcy

1

SELECT NazwaFirmy

2

FROM Produkty

3

INNER JOIN Kategorie ON Kategorie.IdKategorii = Produkty.IdKategorii

4

INNER JOIN Dostawcy ON Dostawcy.IdDostawcy = Produkty.IdDostawcy

- Nagranie audio

play_circle_outlineOdtwórz/Zatrzymaj  00:00

Szukaj

00:07

volume_upWycisz dźwięk

Głośność

Materiał audio dostępny pod adresem:

file:///tmp/puppeteersoQM05.html

Dodajemy operację grupowania po nazwach firm, która przyda się do stworzenia kwerendy krzyżowej:

Linia 1. SELECT NazwaFirmy Linia 2. FROM Produkty Linia 3. INNER JOIN Kategorie ON Kategorie kropka IdKategorii znak równości Produkty kropka IdKategorii Linia 4. INNER JOIN Dostawcy ON Dostawcy kropka IdDostawcy znak równości Produkty kropka IdDostawcy Linia 5. GROUP BY NazwaFirmy

1

SELECT NazwaFirmy

2

FROM Produkty

3

INNER JOIN Kategorie ON Kategorie.IdKategorii = Produkty.IdKategorii

4

INNER JOIN Dostawcy ON Dostawcy.IdDostawcy = Produkty.IdDostawcy

5

GROUP BY NazwaFirmy

- Nagranie audio

play_circle_outlineOdtwórz/Zatrzymaj  00:00

Szukaj

00:11

volume_upWycisz dźwięk

Głośność

Materiał audio dostępny pod adresem:

file:///tmp/puppeteersoQM05.html

Na koniec za pomocą klauzuli CASE WHEN warunek THEN wartość END tworzymy kwerendę krzyżową, która zwraca wynik w pożądanej formie:

Linia 1. SELECT NazwaFirmy przecinek Linia 2. COUNT otwórz nawias okrągły CASE WHEN NazwaKategorii znak równości apostrof Napoje apostrof THEN IdProduktu END zamknij nawias okrągły AS cudzysłów Napoje cudzysłów przecinek Linia 3. COUNT otwórz nawias okrągły CASE WHEN NazwaKategorii znak równości apostrof Desery apostrof THEN IdProduktu END zamknij nawias okrągły AS cudzysłów Desery cudzysłów przecinek Linia 4. COUNT otwórz nawias okrągły CASE WHEN NazwaKategorii znak równości apostrof Zupy apostrof THEN IdProduktu END zamknij nawias okrągły AS cudzysłów Zupy cudzysłów przecinek Linia 5. COUNT otwórz nawias okrągły CASE WHEN NazwaKategorii znak równości apostrof Dania Glowne apostrof THEN IdProduktu END zamknij nawias okrągły AS cudzysłów Dania Glowne cudzysłów przecinek Linia 6. COUNT otwórz nawias okrągły CASE WHEN NazwaKategorii znak równości apostrof Ryby apostrof THEN IdProduktu END zamknij nawias okrągły AS cudzysłów Ryby cudzysłów przecinek Linia 7. COUNT otwórz nawias okrągły CASE WHEN NazwaKategorii znak równości apostrof Alkohole apostrof THEN IdProduktu END zamknij nawias okrągły AS cudzysłów Alkohole cudzysłów przecinek Linia 8. COUNT otwórz nawias okrągły CASE WHEN NazwaKategorii znak równości apostrof Sniadania apostrof THEN IdProduktu END zamknij nawias okrągły AS cudzysłów Sniadania cudzysłów przecinek Linia 9. COUNT otwórz nawias okrągły CASE WHEN NazwaKategorii znak równości apostrof Sery apostrof THEN IdProduktu END zamknij nawias okrągły AS cudzysłów Sery cudzysłów przecinek Linia 10. COUNT otwórz nawias okrągły CASE WHEN NazwaKategorii znak równości apostrof Wedliny apostrof THEN IdProduktu END zamknij nawias okrągły AS cudzysłów Wedliny cudzysłów Linia 11. FROM Produkty Linia 12. INNER JOIN Kategorie ON Kategorie kropka IdKategorii znak równości Produkty kropka IdKategorii Linia 13. INNER JOIN Dostawcy ON Dostawcy kropka IdDostawcy znak równości Produkty kropka IdDostawcy Linia 14. GROUP BY NazwaFirmy

1

SELECT NazwaFirmy,

2

COUNT( CASE WHEN NazwaKategorii = 'Napoje' THEN IdProduktu END ) AS "Napoje",

3

COUNT( CASE WHEN NazwaKategorii = 'Desery' THEN IdProduktu END ) AS "Desery",

4

COUNT( CASE WHEN NazwaKategorii = 'Zupy' THEN IdProduktu END ) AS "Zupy",

5

COUNT( CASE WHEN NazwaKategorii = 'Dania Glowne' THEN IdProduktu END ) AS "Dania Glowne",

6

COUNT( CASE WHEN NazwaKategorii = 'Ryby' THEN IdProduktu END ) AS "Ryby",

7

COUNT( CASE WHEN NazwaKategorii = 'Alkohole' THEN IdProduktu END ) AS "Alkohole",

8

COUNT( CASE WHEN NazwaKategorii = 'Sniadania' THEN IdProduktu END ) AS "Sniadania",

9

COUNT( CASE WHEN NazwaKategorii = 'Sery' THEN IdProduktu END ) AS "Sery",

10

COUNT( CASE WHEN NazwaKategorii = 'Wedliny' THEN IdProduktu END ) AS "Wedliny"

11

FROM Produkty

12

INNER JOIN Kategorie ON Kategorie.IdKategorii = Produkty.IdKategorii

13

INNER JOIN Dostawcy ON Dostawcy.IdDostawcy = Produkty.IdDostawcy

14

GROUP BY NazwaFirmy

- Nagranie audio

play_circle_outlineOdtwórz/Zatrzymaj  00:00

Szukaj

00:04

volume_upWycisz dźwięk

Głośność

Materiał audio dostępny pod adresem:

file:///tmp/puppeteersoQM05.html

W ten sposób otrzymujemy gotowe zestawienie.

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

- Nagranie audio

play_circle_outlineOdtwórz/Zatrzymaj  00:00

Szukaj

00:34

volume_upWycisz dźwięk

Głośność

Materiał audio dostępny pod adresem:

file:///tmp/puppeteersoQM05.html

Wadą przedstawionego rozwiązania jest konieczność wpisywania w kwerendzie konkretnych wartości, według których grupowane są agregowane dane w kolumnach. Dlatego można użyć innego, wygodniejszego rozwiązania, które zostało opisane w materiale „Kwerendy krzyżowe, etap II”. Polega ono na przygotowaniu kwerendy pośredniej, zarejestrowaniu bazy jako źródła danych dla arkusza kalkulacyjnego LibreOffice Calc, a następnie wykorzystaniu narzędzia tabeli przestawnej.

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

Rozwiązanie, o którym mowa w slajdzie 11 znajdziesz w e-materiale Kwerendy krzyżowe, etap IIOtwiera się w nowym oknie.

Odpowiedź do zadania

Microsoft Access:

Plik o rozmiarze 23.53 KB w języku polskim

LibreOffice Base:

Plik o rozmiarze 6.63 KB w języku polskim

Sprawdź się

Zadanie 3. E-learning

Szkoła postanowiła wprowadzić dla uczniów dodatkowe zajęcia z informatyki z wykorzystaniem systemu e-learning. W plikach: Osoby.txt, Listy.txt, Punktacja.txt znajdują się informacje na temat: uczniów szkoły, którzy uczyli się w  systemie e-learning w okresie od 1.09.2014 do 15.02.2015, list zadań oraz uzyskanych przez uczniów wyników. Pierwszy wiersz każdego z plików jest wierszem nagłówkowym, a dane w wierszach rozdzielone są znakami tabulacji.

Plik o rozmiarze 4.65 KB w języku polskim

Plik o nazwie Osoby.txt zawiera 60 wierszy z informacjami na temat osób, które wysyłały rozwiązania zadań poprzez system. Są to: identyfikator danej osoby (id_osoby), jej nazwisko (nazwisko), imię (imie) oraz nazwa grupy, do której osoba ta została przydzielona (grupa).

Przykład:

1

id_osoby

imie

nazwisko

grupa

2

35

Joanna

Matura

G3

3

36

Anna

Piasecka

G3

4

37

Katarzyna

Zienowicz

G3

W pliku Listy.txt znajduje się 11 wierszy z  informacjami na temat list zadań zamieszczonych w systemie: numerem listy (id_listy), nazwą listy zadań (nazwa) oraz terminem oddania (termin_oddania).

Przykład:

1

id_listy

nazwa

termin_oddania

2

7

C7

2015-01-26

3

8

P1

2014-11-10

Plik o nazwie Punktacja.txt zawiera 653 wiersze z informacjami o wynikach uczniów. Są to: liczba porządkowa (lp), identyfikator danej osoby (id_osoby), identyfikator listy zadań (id_listy), liczba punktów zdobytych przez daną osobę (punkty) oraz data przesłania rozwiązania listy zadań (data).

Przykład:

1

lp

id_osoby

id_listy

punkty

data

2

1

1

1

12

2014-10-20

3

2

1

2

12

2014-11-03

4

3

1

8

22

2014-11-10

Wykorzystując dane zawarte w tych plikach oraz dostępne narzędzia informatyczne, wykonaj poniższe zadania, a wyniki zapisz w pliku o nazwie Wyniki_elearning.txt. Wyniki do każdego zadania poprzedź numerem oznaczającym to zadanie.

Do oceny oddajesz:

- plik Wyniki_elearning.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 100.

Zadanie 3.1

Utwórz czytelne zestawienie tabelaryczne, w którym dla każdej grupy podasz, ile osób otrzymało liczbę punktów równą 10, 11, 12. W swoich obliczeniach weź pod uwagę wszystkie listy zadań.

Ważne!

Zestawienie tabelaryczne oznacza, że konieczne jest użycie kwerendy krzyżowej!

Pokaż rozwiązanie

Zadanie 3.2

Utwórz zestawienie, które dla każdej daty przedstawi, ile osób z nazwiskami kończącymi się na „-icz” brało udział w zajęciach w każdej z grup.

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

Pokaż rozwiązanie

Odpowiedzi do zadania

Zadanie 3.1:

Plik o rozmiarze 77.00 B w języku polskim

Zadanie 3.2:

Plik o rozmiarze 274.00 B w języku polskim

Dla nauczyciela

Autor: Maurycy Gast

Przedmiot: Informatyka

Temat: Kwerendy krzyżowe – zadania maturalne

Grupa docelowa:

Szkoła ponadpodstawowa, liceum ogólnokształcące, technikum, zakres podstawowy i rozszerzony

Podstawa programowa:

Cele kształcenia – wymagania ogólne

II. Programowanie i rozwiązywanie problemów z wykorzystaniem komputera oraz innych urządzeń cyfrowych: układanie i programowanie algorytmów, organizowanie, wyszukiwanie i udostępnianie informacji, posługiwanie się aplikacjami komputerowymi.

Treści nauczania – wymagania szczegółowe

II. Programowanie i rozwiązywanie problemów z wykorzystaniem komputera i innych urządzeń cyfrowych.

Zakres rozszerzony. Uczeń spełnia wymagania określone dla zakresu podstawowego, a ponadto:

4) przygotowując opracowania rozwiązań złożonych problemów, posługuje się wybranymi aplikacjami w stopniu zaawansowanym:

d) projektuje i tworzy relacyjną bazę złożoną z wielu tabel oraz sieciową aplikację bazodanową dla danych związanych z rozwiązywanym problemem, formułuje kwerendy, tworzy i modyfikuje formularze oraz raporty, stosuje język SQL do wyszukiwania informacji w bazie i do jej modyfikacji, uwzględnia kwestie integralności danych, bezpieczeństwa i ochrony danych w bazie,

Kształtowane kompetencje kluczowe:

- kompetencje cyfrowe;

- kompetencje osobiste, społeczne i w zakresie umiejętności uczenia się;

- kompetencje matematyczne oraz kompetencje w zakresie nauk przyrodniczych, technologii i inżynierii.

Cele operacyjne (językiem ucznia):

- Przeanalizujesz rozwiązania przykładowych zadań typu maturalnego dotyczących kwerend krzyżowych.

- Rozwiążesz przykładowe zadania maturalne, wymagające znajomości kwerend krzyżowych.

- Prześledzisz sposoby rozwiązywania zadań bazujących na złożonych zestawieniach danych.

Strategie nauczania:

- konstruktywizm;

- konektywizm.

Metody i techniki nauczania:

- dyskusja;

- rozmowa nauczająca z wykorzystaniem multimedium i ćwiczeń interaktywnych;

- ćwiczenia praktyczne.

Formy pracy:

- praca indywidualna;

- praca w parach;

- praca całego zespołu klasowego.

Środki dydaktyczne:

- komputery z głośnikami, słuchawkami i dostępem do internetu;

- zasoby multimedialne zawarte w e-materiale;

- tablica interaktywna/tablica, pisak/kreda;

- oprogramowanie Microsoft Access 2010 lub wybrany odpowiednik;

- oprogramowanie LibreOffice Base 5.3 lub wybrany odpowiednik.

Przebieg lekcji

Przed lekcją:

1. Przygotowanie do zajęć. Uczniowie powtarzają najważniejsze informacje dotyczące kwerend krzyżowych.

Faza wstępna:

1. Rozpoznanie wiedzy uczniów. Nauczyciel w ramach sprawdzenia przygotowania uczniów do lekcji prosi wskazane osoby o wymienienie najważniejszych informacji dotyczących kwerend krzyżowych.

2. Nauczyciel wprowadza uczniów szczegółowo w temat lekcji i jej cele. Może posłużyć się wyświetloną na tablicy zawartością sekcji „Wprowadzenie”.

Faza realizacyjna:

1. Praca z tekstem. Nauczyciel wyświetla zawartość sekcji „Przeczytaj”. Uczniowie w parach analizują rozwiązanie Zadania 1, a następnie powtarzają je indywidualnie na swoich komputerach.

2. Praca z multimedium. Nauczyciel wyświetla zawartość sekcji „Prezentacja multimedialna”. Uczniowie w parach rozwiązują Zadanie 2 w wybranym programie do obsługi bazy danych. Następnie indywidualnie porównują swoje rozwiązania z przedstawionymi w prezentacjach multimedialnych.

3. Ćwiczenie umiejętności. Uczniowie wykonują indywidualnie zadanie 3.1 z sekcji „Sprawdź się”. Ich zadaniem jest utworzenie czytelnego zestawienia tabelarycznego, w którym dla każdej grupy podadzą, ile osób otrzymało liczbę punktów równą 10, 11, 12. W swoich obliczeniach mają wziąć pod uwagę wszystkie listy zadań. Chętna lub wybrana osoba prezentuje swoje rozwiązanie na forum klasy.

Faza podsumowująca:

1. Nauczyciel wyświetla na tablicy temat lekcji i cele zawarte w sekcji „Wprowadzenie”. W kontekście ich realizacji podsumowuje przebieg zajęć, a także wskazuje mocne i słabe strony pracy uczniów.

Praca domowa:

1. Uczniowie wykonują zadanie 3.2 z sekcji „Sprawdź się”.

Wskazówki metodyczne:

- Uczniowie mogą wykorzystać treści w sekcjach: „Przeczytaj”, „Prezentacja multimedialna”, „Sprawdź się” jako materiał do lekcji powtórkowej.

Table of Contents

Wprowadzenie

Przeczytaj

Prezentacja multimedialna

Sprawdź się

Dla nauczyciela