Spis treści

Wprowadzenie

Przeczytaj

Audiobook

Sprawdź się

Dla nauczyciela

Zapytania do baz danych, etap II

Źródło: Michael Dziedzic, domena publiczna.

Zapytania do baz danych nie muszą dotyczyć tylko jednej tabeli. Często ich zadaniem jest wyszukanie informacji znajdujących się w dwóch lub więcej powiązanych ze sobą tabelach.

W tym e-materiale przećwiczysz, na praktycznym przykładzie, tworzenie zapytań odwołujących się do wzajemnie powiązanych relacjami tabel w bazie danych.

Więcej informacji o zapytaniach do baz danych znajdziesz w e-materiale Zapytania do baz danych, etap I.

Twoje cele

- Utworzysz zapytania do połączonych relacjami tabel w bazie danych w programie Microsoft Access lub LibreOffice Base.

- Użyjesz w praktyce wybrane kryteria filtrowania danych.

- Przeanalizujesz zastosowanie funkcji agregujących.

Przeczytaj

Praca z danymi

W poprzednim e-materiale (Zapytania do baz danych, etap I) projektowaliśmy kwerendy wykorzystujące dane pochodzące z pojedynczej tabeli. Korzystaliśmy z możliwości jednego narzędzia do tworzenia kwerend, jakim jest tzw. kreator kwerend. W tym e-materiale popracujemy z danymi umieszczonymi w kilku tabelach oraz wykorzystamy możliwości tworzenia kwerend w tzw. widoku projektu.

Przypomnijmy sobie strukturę bazy danych:

Zapytania do bazy danych

Kto, ile i jakich produktów zamówił w czerwcu?

Aby dowiedzieć się, jakich zakupów dokonywali poszczególni klienci w określonym czasie, potrzebujemy zaprojektować kwerendę odwołującą do: tabeli klienci, tabeli zamowienia, tabeli pozycje_zamowienia oraz tabeli produkty. Są to wszystkie tabele powiązane relacjami w naszej bazie danych.

Opisaną kwerendę w programie Microsoft Access utworzymy, korzystając z tzw. Projektu kwerendy dostępnego w zakładce Tworzenie:

Aby zaprojektować kwerendę, musimy wskazać tabele, z których będą pochodziły pola do tworzonego widoku arkusza danych. Skorzystamy z funkcjonalności dostępnych w okienku Dodawanie tabel:

Za pomocą okienka Dodawanie tabel dodajemy wszystkie potrzebne do zaprojektowania kwerendy tabele bazy danych:

Z poszczególnych tabel wybieramy pola, które mają utworzyć widok finalnego arkusza danych:

Uruchamiamy zaprojektowaną kwerendę za pomocą przycisku Uruchom:

Otrzymujemy następujący arkusz danych:

W programie LibreOffice Base kwerendę utworzymy również poprzez tzw. projekt:

Dodajemy tabele:

Wybieramy pola, na podstawie których zostanie wygenerowany wynikowy arkusz danych:

Uruchamiamy zaprojektowaną kwerendę:

W rezultacie otrzymujemy następujący arkusz danych:

Ważne!

Mogłoby się zdarzyć, że dwóch lub więcej klientów posiada identyczne imię i nazwisko:

Dlatego projektując kwerendę, powinniśmy (w sytuacjach, w których nam na tym zależy) uwzględnić również unikalny identyfikator, czyli pole IdKlienta:

Na zdjęciu przedstawiono zestawienie sprzedaży w czerwcu. Tak się składa, że wśród klientów, którzy dokonali zakupów w analizowanym miesiącu, nie było dwóch lub więcej o tym samym imieniu i nazwisku. Nie powinniśmy jednak zakładać, że taka sytuacja będzie zawsze. Z umieszczonego poniżej zestawienia uwzględniającego – oprócz czerwca – również maj wynika, że w bazie danych występują oraz dokonują zakupów dwie klientki o imieniu Agnieszka i nazwisku Nowakowska, na co wskazują różne wartości w polu IdKlienta:

Top 5 najaktywniejszych klientów

Dla najaktywniejszych klientów właściciele sklepu przygotowują specjalne kupony rabatowe. W celu wyselekcjonowania klientów, którzy zakupili najwięcej słoików miodu, projektujemy następującą kwerendę (Microsoft Access):

Po uruchomieniu kwerendy otrzymujemy następujący wynik:

Taki wynik jest jednak niewystarczający. Ci sami klienci występują w wielu miejscach, natomiast potrzebujemy zbiorczego zestawienia, w którym każdy klient wystąpiłby tylko raz, a obok wyświetlona zostałaby liczba oznaczająca sumę zakupionych sztuk słoików z miodem przez danego klienta.

Taki efekt możemy uzyskać poprzez zastosowanie grupowania oraz funkcji agregujących. W widoku projektu uruchamiamy przycisk Sumy:

Następnie odsłaniamy rekord Suma:

W kolumnie LiczbaSztuk wybieramy funkcję Suma:

Otrzymujemy następujące podsumowanie:

Dodatkowo, możemy ograniczyć liczbę wyświetlanych rekordów do określonej wartości, np. 5:

Ostatecznie uzyskujemy listę sześciu zamiast pięciu najaktywniejszych klientów, ponieważ piątą pozycję w rankingu zajmuje ex aequo dwoje klientów:

Podobny efekt w programie LibreOffice Base uzyskamy, rozpoczynając od projektu kwerendy:

Następnie za pomocą przycisku Funkcje odsłaniamy wiersz Funkcja, w którym dla kolumny Imie, kolumny Nazwisko oraz kolumny LiczbaSztuk wybieramy odpowiednio: funkcję Grupuj, funkcję Grupuj oraz funkcję Suma:

Ustalając ograniczenie do pięciu rekordów wynikowych oraz sortowanie malejące w kolumnie LiczbaSztuk, uzyskujemy następujący rezultat:

Słownik

funkcja agregująca

funkcja (np. suma, średnia, zlicz, maksimum, minimum) umożliwiająca podsumowanie wartości dla kolumn zgrupowanych

grupowanie

rodzaj operacji wykonywanej w bazie danych, która umożliwia konsolidowanie rekordów w tabeli wg wartości występujących w określonej kolumnie (lub kolumnach) i uzyskiwanie podsumowań do tych grup rekordów

Audiobook

Audiobook można wysłuchać pod adresem: file:///tmp/puppeteerKhZMPq.html

Przed nowym sezonem miodobrania, w sklepie rodziców Tomka ustalono, że to dobry moment na spotkanie, na które każdy miał przygotować swoje pomysły marketingowe na najbliższy czas.

- Moim zdaniem musimy ulepszyć nasz schemat funkcjonowania i działać systemowo w celu zwiększenia efektywności sprzedaży – zaproponował Tomek.

- To prawda, tu można jeszcze wiele poprawić. Żeby móc lepiej planować, potrzebujemy skorzystać z naszych danych na temat dotychczasowej sprzedaży i różnych jej aspektów – dodała Dorota.

- Dobrze, co w takim razie warto wziąć pod uwagę? - zapytała mama.

- Przeglądałem nasze zestawienia sprzedażowe za ostatnie lata i mam już pomysł, co warto przeanalizować gruntownie i wdrożyć w praktykę w tym sezonie. Obserwując zebrane dane, doszedłem do wniosku, że tegoroczną kampanię reklamową i promocyjną będziemy prowadzić w oparciu o rodzaj wybieranego miodu i jego ilość zamawianą przez odbiorców. Okazuje się, że nie bez znaczenia jest pojemność naszych produktów – stwierdził Tomek.

- Zauważyłam, że moglibyśmy też wykorzystać czas dokonywania zakupów przez naszych klientów. W niektórych okresach roku jest to bardzo nasilony proces, na przykład przed Bożym Narodzeniem oraz w okresach świąt takich jak Dzień Kobiet, Dzień Babci czy Dzień Ojca. Mniejsze natężenie widać w okresie urodzin, rocznic, a są i okresy prawie bez sprzedaży – zauważyła Dorota.

- W takim razie zrobię rozeznanie, jakiej pojemności słoikami dysponujemy w tej chwili, a jakie warto byłoby zamówić, żeby lepiej rozlewać miód po zbiorze. Przygotuję też inwentaryzację istniejących zapasów do porównania z poprzednimi sezonami – ogłosił tato.

- Zaznaczę w kalendarzu terminy różnych świąt i uroczystych okazji oraz daty urodzin naszych zamawiających. Będziemy mogli przesyłać nowe oferty z odpowiednim wyprzedzeniem, by miód trafiał na czas do kupujących – zadeklarowała mama.

- Dobrze, mamy uzgodnione działania wstępne, każdy zadba o szybką realizację swojej części planu, żeby z wdrożeniem nowych pomysłów być gotowym przed zbiorami – poprosił Tomek.

Tym sposobem pierwsze kroki do zoptymalizowania procesu tegorocznej sprzedaży miodu zostały poczynione.

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

Ćwiczenie 1

Istotnym wskaźnikiem sprzedaży jest lista przedstawiająca najlepiej i najsłabiej sprzedające się rodzaje miodów z uwzględnieniem pojemności słoików. Zaprojektuj kwerendę pozwalającą na uzyskanie takiego zestawienia.

Plik bazy danych utworzony w programie Microsoft Access:

Plik o rozmiarze 78.92 KB w języku polskim

Plik bazy danych utworzony w programie LibreOffice Base:

Plik o rozmiarze 8.70 KB w języku polskim

Pokaż rozwiązanie

Sprawdź się

Pokaż ćwiczenia:

Ćwiczenie 1

Rozwiąż krzyżówkę, a dowiesz się, jak brzmi popularny skrótowiec oznaczający podstawowe operacje implementowane w aplikacjach bazodanowych.

1.

Pytanie Po angielsku znaczy: „wybierz”. Często używane słowo w zapytaniach tworzonych w języku SQL.Znak 1 z 6

Pytanie Po angielsku znaczy: „wybierz”. Często używane słowo w zapytaniach tworzonych w języku SQL.Znak 2 z 6

Pytanie Po angielsku znaczy: „wybierz”. Często używane słowo w zapytaniach tworzonych w języku SQL.Znak 3 z 6

Pytanie Po angielsku znaczy: „wybierz”. Często używane słowo w zapytaniach tworzonych w języku SQL.Znak 4 z 6

Pytanie Po angielsku znaczy: „wybierz”. Często używane słowo w zapytaniach tworzonych w języku SQL.Znak 5 z 6

Fragment hasła

Pytanie Po angielsku znaczy: „wybierz”. Często używane słowo w zapytaniach tworzonych w języku SQL.Znak 6 z 6

2.

Pytanie Nazwisko niemieckiego filozofa (bez jednego „m”) lub słowo występujące w zapytaniu w języku SQL poprzedzające listę tabel, których dotyczy zapytanie.Znak 1 z 4

Pytanie Nazwisko niemieckiego filozofa (bez jednego „m”) lub słowo występujące w zapytaniu w języku SQL poprzedzające listę tabel, których dotyczy zapytanie.Znak 2 z 4

Fragment hasła

Pytanie Nazwisko niemieckiego filozofa (bez jednego „m”) lub słowo występujące w zapytaniu w języku SQL poprzedzające listę tabel, których dotyczy zapytanie.Znak 3 z 4

Pytanie Nazwisko niemieckiego filozofa (bez jednego „m”) lub słowo występujące w zapytaniu w języku SQL poprzedzające listę tabel, których dotyczy zapytanie.Znak 4 z 4

3.

Pytanie Rodzaj operacji wykonywanej w bazie danych, która umożliwia konsolidowanie rekordów w tabeli wg wartości występujących w określonej kolumnie (lub kolumnach) i uzyskiwanie podsumowań do tych grup rekordów.Znak 1 z 10

Pytanie Rodzaj operacji wykonywanej w bazie danych, która umożliwia konsolidowanie rekordów w tabeli wg wartości występujących w określonej kolumnie (lub kolumnach) i uzyskiwanie podsumowań do tych grup rekordów.Znak 2 z 10

Pytanie Rodzaj operacji wykonywanej w bazie danych, która umożliwia konsolidowanie rekordów w tabeli wg wartości występujących w określonej kolumnie (lub kolumnach) i uzyskiwanie podsumowań do tych grup rekordów.Znak 3 z 10

Fragment hasła

Pytanie Rodzaj operacji wykonywanej w bazie danych, która umożliwia konsolidowanie rekordów w tabeli wg wartości występujących w określonej kolumnie (lub kolumnach) i uzyskiwanie podsumowań do tych grup rekordów.Znak 4 z 10

Pytanie Rodzaj operacji wykonywanej w bazie danych, która umożliwia konsolidowanie rekordów w tabeli wg wartości występujących w określonej kolumnie (lub kolumnach) i uzyskiwanie podsumowań do tych grup rekordów.Znak 5 z 10

Pytanie Rodzaj operacji wykonywanej w bazie danych, która umożliwia konsolidowanie rekordów w tabeli wg wartości występujących w określonej kolumnie (lub kolumnach) i uzyskiwanie podsumowań do tych grup rekordów.Znak 6 z 10

Pytanie Rodzaj operacji wykonywanej w bazie danych, która umożliwia konsolidowanie rekordów w tabeli wg wartości występujących w określonej kolumnie (lub kolumnach) i uzyskiwanie podsumowań do tych grup rekordów.Znak 7 z 10

Pytanie Rodzaj operacji wykonywanej w bazie danych, która umożliwia konsolidowanie rekordów w tabeli wg wartości występujących w określonej kolumnie (lub kolumnach) i uzyskiwanie podsumowań do tych grup rekordów.Znak 8 z 10

Pytanie Rodzaj operacji wykonywanej w bazie danych, która umożliwia konsolidowanie rekordów w tabeli wg wartości występujących w określonej kolumnie (lub kolumnach) i uzyskiwanie podsumowań do tych grup rekordów.Znak 9 z 10

Pytanie Rodzaj operacji wykonywanej w bazie danych, która umożliwia konsolidowanie rekordów w tabeli wg wartości występujących w określonej kolumnie (lub kolumnach) i uzyskiwanie podsumowań do tych grup rekordów.Znak 10 z 10

4.

Pytanie Inaczej: zapytanie do bazy danych.Znak 1 z 8

Pytanie Inaczej: zapytanie do bazy danych.Znak 2 z 8

Pytanie Inaczej: zapytanie do bazy danych.Znak 3 z 8

Pytanie Inaczej: zapytanie do bazy danych.Znak 4 z 8

Pytanie Inaczej: zapytanie do bazy danych.Znak 5 z 8

Pytanie Inaczej: zapytanie do bazy danych.Znak 6 z 8

Pytanie Inaczej: zapytanie do bazy danych.Znak 7 z 8

Fragment hasła

Pytanie Inaczej: zapytanie do bazy danych.Znak 8 z 8

1. 1.  Po angielsku znaczy: „wybierz”. Często używane słowo w zapytaniach tworzonych w języku SQL.

2. 2.  Nazwisko niemieckiego filozofa (bez jednego „m”) lub słowo występujące w zapytaniu w języku SQL poprzedzające listę tabel, których dotyczy zapytanie.

3. 3.  Rodzaj operacji wykonywanej w bazie danych, która umożliwia konsolidowanie rekordów w tabeli wg wartości występujących w określonej kolumnie (lub kolumnach) i uzyskiwanie podsumowań do tych grup rekordów.

4. 4.  Inaczej: zapytanie do bazy danych.

Ćwiczenie 2

Wymyśl pytanie na kartkówkę związane z tematem materiału.

Uzupełnij projekt kwerendy wyświetlającej imiona i nazwiska klientów – mężczyzn, którzy zamówili miód rzepakowy w słojach o pojemności nie większej niż 0,7 litra.

Usun"miód rzepakowy"     Usun<=0,7     UsunNot Like "*a"

Ćwiczenie 3

W bazie danych, której schemat relacji przedstawiono poniżej:

po uruchomieniu pewnej kwerendy otrzymano następujący rezultat:

Wybierz jedno nowe słowo poznane podczas dzisiejszej lekcji i ułóż z nim zdanie.

Uzupełnij projekt kwerendy, aby możliwy był do uzyskania wynik przedstawiony powyżej.

UsunSuma     UsunRosnąco     Usun0,9     UsunGrupuj według

Ćwiczenie 4

Utwórz kwerendę wyświetlającą imiona i nazwiska klientów (kobiet i mężczyzn), którzy w okresie od 10 do 25 czerwca 2021 r. zamawiali produkty w cenie jednostkowej co najmniej 40 zł. Wiersze w tabeli wynikowej powinny być uporządkowane malejąco według kolumny CenaZaSztuke.

Plik bazy danych utworzony w programie Microsoft Access:

Plik o rozmiarze 79.41 KB w języku polskim

Plik bazy danych utworzony w programie LibreOffice Base:

Plik o rozmiarze 8.25 KB w języku polskim

Pokaż rozwiązanie

Ćwiczenie 5

Utwórz kwerendę wyświetlającą daty zamówień złożonych na miód akacjowy lub lipowy o pojemności 0,9 litra w liczbie co najmniej dwóch sztuk.

Plik bazy danych utworzony w programie Microsoft Access:

Plik o rozmiarze 99.43 KB w języku polskim

Plik bazy danych utworzony w programie LibreOffice Base:

Plik o rozmiarze 9.11 KB w języku polskim

Pokaż rozwiązanie

Ćwiczenie 6

Utwórz kwerendę wyświetlającą imiona i nazwiska klientek (płeć rozpoznaj po imieniu), które dokonały największej liczby zamówień na miód akacjowy (dowolna pojemność pojedynczego słoika). Wiersze w tabeli wynikowej powinny być zgrupowane i uporządkowane malejąco według kolumny podsumowującej liczbę zamówień.

Plik bazy danych utworzony w programie Microsoft Access:

Plik o rozmiarze 103.24 KB w języku polskim

Plik bazy danych utworzony w programie LibreOffice Base:

Plik o rozmiarze 9.11 KB w języku polskim

Pokaż rozwiązanie

Ćwiczenie 7

Utwórz kwerendę wyświetlającą imię i nazwisko klienta – mężczyzny (płeć rozpoznaj po imieniu), który dokonał największej liczby zamówień na jakiekolwiek artykuły w sklepie z produktami pszczelimi.

Plik bazy danych utworzony w programie Microsoft Access:

Plik o rozmiarze 104.04 KB w języku polskim

Plik bazy danych utworzony w programie LibreOffice Base:

Plik o rozmiarze 8.18 KB w języku polskim

Pokaż rozwiązanie

Ćwiczenie 8

Utwórz kwerendę wyświetlającą zestawienie wartości sprzedaży poszczególnych rodzajów miodów bez uwzględniania pojemności słoików. Wiersze w tabeli wynikowej powinny być zgrupowane i uporządkowane malejąco według kolumny podsumowującej wartość sprzedaży.

Plik bazy danych utworzony w programie Microsoft Access:

Plik o rozmiarze 105.98 KB w języku polskim

Plik bazy danych utworzony w programie LibreOffice Base:

Plik o rozmiarze 9.21 KB w języku polskim

Dla nauczyciela

Autor: Tomasz Jarosz

Przedmiot: Informatyka

Temat: Zapytania do baz danych, etap II

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 podstawowy. Uczeń:

3) przygotowuje opracowania rozwiązań problemów, posługując się wybranymi aplikacjami:

d) wyszukuje informacje, korzystając z bazy danych opartej na co najmniej dwóch tabelach, definiuje relacje, stosuje filtrowanie, formułuje kwerendy, tworzy i modyfikuje formularze, drukuje raporty,

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

- Utworzysz zapytania do połączonych relacjami tabel w bazie danych w programie Microsoft Access lub LibreOffice Base.

- Użyjesz w praktyce wybrane kryteria filtrowania danych.

- Przeanalizujesz zastosowanie funkcji agregujących.

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 w grupach;

- praca całego zespołu klasowego.

Środki dydaktyczne:

- zasoby multimedialne zawarte w e-materiale;

- komputery z dostępem do internetu dla uczniów;

- LibreOffice w wersji 5.3 lub nowszej (w tym: Calc, Base);

- Microsoft Office 2003 lub nowszy (w tym: Excel i Access).

Przebieg lekcji

Przed lekcją:

1. Przygotowanie do zajęć. Nauczyciel loguje się na platformie i udostępnia e-materiał: „Zapytania do baz danych, etap II”. Uczniowie zapoznają się z treściami w sekcji „Przeczytaj”.

Faza wstępna:

1. Nauczyciel wyświetla uczniom temat, wskazuje cele zajęć oraz ustala z uczestnikami zajęć kryteria sukcesu.

2. Rozpoznanie wiedzy uczniów. Uczniowie tworzą pytania dotyczące tematu zajęć, na które odpowiedzą w trakcie lekcji.

Faza realizacyjna:

1. Uczniowie analizują przykład z sekcji „Przeczytaj” i powtarzają zaprezentowane rozwiązanie na swoim komputerze.

2. Praca z multimedium. Uczniowie zapoznają się indywidualnie z treścią sekcji „Audiobook” i poleceniem nr 1, zapisują problemy i pytania z nią związane. Po czym następuje dyskusja, w trakcie której nauczyciel wyjaśnia niezrozumiałe kroki procedury.

3. Ćwiczenie umiejętności. Uczniowie wykonują ćwiczenia nr 1-3 z sekcji „Sprawdź się”. Nauczyciel sprawdza poprawność wykonanych zadań, omawiając je wraz z uczniami.

4. W kolejnym etapie uczniowie dobierają się w pary i wykonują ćwiczenia nr 4-6. Następnie konsultują swoje rozwiązania z inną parą uczniów i ustalają jedną wersję odpowiedzi.

Faza podsumowująca:

1. Nauczyciel ponownie wyświetla na tablicy temat lekcji zawarty w sekcji „Wprowadzenie” i inicjuje krótką rozmowę na temat zrealizowanych celów (czego uczniowie się nauczyli).

2. Wybrany uczeń podsumowuje zajęcia, zwracając uwagę na nabyte umiejętności.

Praca domowa:

1. Uczniowie wykonują ćwiczenia 7 i 8 z sekcji „Sprawdź się”.

Wskazówki metodyczne:

- Treści w sekcji „Audiobook” można wykorzystać na lekcji jako podsumowanie i utrwalenie wiedzy uczniów.

- Warto zachęcić uczniów do przedstawienia własnych pomysłów na usprawnienie działania firmy poprzez zaprojektowanie odpowiednich kwerend umożliwiających uzyskanie przydatnych zestawień i podsumowań.

Table of Contents

Wprowadzenie

Przeczytaj

Audiobook

Sprawdź się

Dla nauczyciela