Pewna włoska restauracja ma bazę danych przechowującą informację o daniach, zamówieniach oraz klientach. Każde zamówienie przypisane jest do jednego klienta, ale może składać się z wielu dań.
Dane są cztery pliki: Zamowienia.txt, Klienci.txt, Sklad_zamowienia.txt i Dania.txt zawierające informacje znajdujące się w tej bazie. Każdy z plików posiada nagłówek z zawartymi w nim nazwami kolumn. Kolumny rozdzielone są tabulatorem, a kolejne wiersze – znakiem nowej linii.
Plik Klienci.txt zawiera informacje o klientach restauracji w postaci pól: Identyfikator, Imie i Nazwisko. Oto fragment tego pliku:
Linia 1. Identyfikator Imie Nazwisko.
Linia 2. 1 Albert Czerwiński.
Linia 3. 2 Emilia Ziółkowska.
Identyfikator Imie Nazwisko
1 Albert Czerwiński
2 Emilia Ziółkowska
Plik Dania.txt przechowuje informacje o daniach w postaci pól: Nazwy oraz Ceny.
Linia 1. Nazwy Ceny.
Linia 2. Calzone 6 przecinek 00 zł.
Linia 3. Capriciosa 28 przecinek 00 zł.
Nazwy Ceny
Calzone 6,00 zł
Capriciosa 28,00 zł
Plik Zamowienia.txt zawiera informacje o dokonanych przez klientów zamówieniach. Dane przechowywane są w kolumnach: Identyfikator, Nr_klienta, Data_wizyty.
Linia 1. Identyfikator Nr podkreślnik klienta Data podkreślnik wizyty.
Linia 2. 1 1 02 kropka 03 kropka 2021.
Linia 3. 2 3 04 kropka 03 kropka 2021.
Plik Sklad_zamowienia.txt przechowuje informacje o tym, jakie dania wchodzą w skład poszczególnych zamówień dokonywanych przez klientów. Służy jako łącznik pomiędzy tabelą Dania a tabelą Zamowienia, umożliwiając każdemu polu z tabeli Dania wystąpienie w wielu relacjach z polami tabeli Zamowienia oraz każdemu polu z tabeli Zamowienia relację z wieloma polami z tabeli Dania.
Plik ten zawiera tabele: Nr_zamowienia, Danie, Liczba.
Dla każdego z pól z tabeli Zamowienia istnieje tyle pól z tabeli Sklad_zamowienia, ile różnych dań było w jego składzie. Liczbę poszczególnych dań opisuje kolumna Liczba.
Linia 1. Nr podkreślnik zamowienia Danie Liczba.
Linia 2. 1 Margherita 2.
Linia 3. 2 Chleb Czosnkowy 1.
Nr_zamowienia Danie Liczba
1 Margherita 2
2 Chleb Czosnkowy 1
Przykład 1
Zamówienie nr 1 zawierało dwie Margherity, ale inny rekord tego pliku może dodać do zamówienia nr 1 kolejne dania.
Import danych
Pierwszym krokiem przy rozwiązywaniu tego zadania jest import danych oraz poprawne ustawienie relacji. Jego realizacja zależy od używanego środowiska, ale we wszystkich przypadkach należy utworzyć odpowiednio nazwane tabele oraz wstawić do nich dane z plików.
Następnie ustalamy relację w tej bazie danych. Powinna ona odpowiadać następującemu schematowi:
RFujiWZ6m2axK
Zrzut ekranu przedstawia relację czterech tabel. Tabela Klienci z polami: Indentyfikator z ikoną klucza, Imie, Nazwisko. Tabela Zamowienia z polami: Indentyfikator z ikoną klucza, Nr_klienta, Data_wizyty. Tabela Sklad_zamowienia: Nr_zamowienia z ikoną klucza, Danie z ikoną klucza, Liczba. Tabela Dania z polami: Nazwy z ikoną klucza, Ceny. Relacje w postaci czarnej linii łączącej pola z dwóch tabel: Identyfikator z tabeli Klienci w relacji z polem Nr_klienta z tabeli Zamowienia, identyfikator z tabeli Zamowienia w relacji z polem Nr_zamowienia z tabeli Sklad_zamowienia, Danie z tabeli Sklad_zamowienia z polem Nazwy z tabeli Dania.
Źródło: Contentplus.pl Sp. z o.o., licencja: CC BY-SA 3.0.
Pole Nr_klienta w tabeli Zamowienia musi być połączone z polem Identyfikator w tabeli Klienci.
Pole Nr_zamowienia w tabeli Sklad_zamowienia musi być połączone z polem Identyfikator w tabeli Zamowienia.
Pole Danie w tabeli Sklad_zamowienia musi być połączone z polem Nazwy w tabeli Dania.
Polecenie 1.
Podaj liczbę zamówień i nazwę dania najczęściej zamawianego w restauracji.
Rozwiązanie:
Pierwszym krokiem jest zidentyfikowanie tabeli/tabel, z których będziemy pobierać informacje. W tym przypadku jest to Sklad_zamowienia, z którego musimy wziąć nazwy dań oraz ich liczbę.
Zacznijmy od wypisania koniecznych danych. W kolejnych krokach będziemy rozbudowywać tę kwerendę. Taki sposób działania ułatwia wyszukiwanie błędów na kolejnych etapach tworzenia kwerendy.
Linia 1. SELECT Danie przecinek Liczba.
Linia 2. FROM otwórz nawias kwadratowy Sklad podkreślnik zamowienia zamknij nawias kwadratowy.
SELECT Danie, Liczba
FROM [Sklad_zamowienia]
Zwróć uwagę, że w pliku Sklad_zamowienia.txt nazwy dań powtarzają się w ramach różnych zamówień. Dlatego należy pogrupować wiersze zgodnie z nazwami dań, a ich liczbę zsumować.
Dodatkowo kolumnę zawierającą sumę można nazwać za pomocą polecenia AS.
Linia 1. SELECT Danie przecinek Sum otwórz nawias okrągły Liczba zamknij nawias okrągły AS otwórz nawias kwadratowy Sumaryczna podkreślnik liczba zamknij nawias kwadratowy.
Linia 2. FROM otwórz nawias kwadratowy Sklad zamowienia zamknij nawias kwadratowy.
Linia 3. GROUP BY Danie.
SELECT Danie, Sum(Liczba) AS [Sumaryczna_liczba]
FROM [Sklad zamowienia]
GROUP BY Danie
Ważne!
Uruchamiaj kwerendę przed jej skończeniem, aby znaleźć ewentualne błędy. Dużo prościej jest analizować krótki kod.
Teraz gdzieś w pliku znajduje się poszukiwana przez nas maksymalna wartość. Możną ją znaleźć przez posortowanie wyniku za pomocą polecenia ORDER BY.
Linia 1. SELECT Danie przecinek Sum otwórz nawias okrągły Liczba zamknij nawias okrągły AS otwórz nawias kwadratowy Sumaryczna podkreślnik liczba zamknij nawias kwadratowy.
Linia 2. FROM otwórz nawias kwadratowy Sklad podkreślnik zamowienia zamknij nawias kwadratowy.
Linia 3. GROUP BY Danie.
Linia 4. ORDER BY 2 DESC.
SELECT Danie, Sum(Liczba) AS [Sumaryczna_liczba]
FROM [Sklad_zamowienia]
GROUP BY Danie
ORDER BY 2 DESC
ORDER BY 2 oznacza sortowanie zgodnie z wartościami drugiej kolumny ([Sumaryczna_liczba]).
Natomiast wyraz DESC jest skrótem od DESCENDING i oznacza sortowanie malejące. Dzięki temu największa wartość w drugiej kolumnie znajdzie się u góry (domyślnie sortowanie jest rosnące).
Jeśli w kwerendzie pojawi się polecenie GROUP BY, to każda kolumna zawarta w SELECT musi znaleźć się albo w poleceniu GROUP BY, albo wewnątrz funkcji agregującejfunkcja agregującafunkcji agregującej. Inaczej kwerenda nie zadziała.
W tym przypadku pole Danie jest w GROUP BY, a pole Liczba jest agregowane przez SUM.
Poprawna odpowiedź:
Lasagne - 7
Schemat oceniania:
podanie poprawnej liczby i nazwy najczęściej zamawianego dania – 2 punkty
podanie poprawnej liczby lub nazwy najczęściej zamawianego dania – 1 punkt
niepoprawna odpowiedź – 0 punktów.
Polecenie 2.
Podaj średnią wartość zamówień dokonanych pomiędzy 01.03.2021 a 07.03.2021.
Rozwiązanie:
Ponownie zaczynamy od identyfikacji tabel, z których potrzebne są nam dane.
Niezbędne będzie tu pole Data z tabeli Zamowienia, aby wiedzieć, czy zawiera się ona w podanym przedziale oraz pole Identyfikator z tabeli Zamowienia, aby było wiadomo, do jakiego zamówienia przypisany jest dany rekord.
Potrzebne jest także pole Cena z tabeli Danie oraz pole Liczba z tabeli Sklad_zamowienia, ponieważ są one niezbędne do obliczenia wartości zamówienia.
Zacznijmy pisanie kwerendy od wybrania danych z tabeli Sklad_zamowienia i dołączenia do nich pola Ceny z tabeli Danie za pomocą polecenia INNER JOIN.
Linia 1. SELECT Dania kropka Ceny przecinek otwórz nawias kwadratowy Sklad podkreślnik zamowienia zamknij nawias kwadratowy kropka Danie przecinek otwórz nawias kwadratowy Sklad podkreślnik zamowienia zamknij nawias kwadratowy kropka Liczba.
Linia 2. FROM otwórz nawias kwadratowy Sklad podkreślnik zamowienia zamknij nawias kwadratowy.
Linia 3. INNER JOIN Dania on Dania kropka Nazwy znak równości otwórz nawias kwadratowy Sklad podkreślnik zamowienia zamknij nawias kwadratowy kropka Danie.
SELECT Dania.Ceny, [Sklad_zamowienia].Danie, [Sklad_zamowienia].Liczba
FROM [Sklad_zamowienia]
INNER JOIN Dania on Dania.Nazwy = [Sklad_zamowienia].Danie
Teraz dodajmy kolejne informacje, tym razem z tabeli Zamowienia.
W przypadku programu Access konieczne jest zastosowanie dodatkowego nawiasu, jeśli łączymy więcej niż jedną tabelę. Alternatywnie można wykorzystać kreator kwerend do utworzenia złączenia tabel, a następnie napisać kwerendę wykorzystującą wynik tej z kreatora jako tabelę.
Linia 1. SELECT Zamowienia kropka Identyfikator przecinek Dania kropka Ceny przecinek otwórz nawias kwadratowy Sklad podkreślnik zamowienia zamknij nawias kwadratowy kropka Danie przecinek.
Linia 2. otwórz nawias kwadratowy Sklad podkreślnik zamowienia zamknij nawias kwadratowy kropka Liczba przecinek Zamowienia kropka otwórz nawias kwadratowy Data podkreślnik wizyty zamknij nawias kwadratowy.
Linia 3. FROM.
Linia 4. otwórz nawias okrągły.
Linia 5. otwórz nawias kwadratowy Sklad podkreślnik zamowienia zamknij nawias kwadratowy.
Linia 6. INNER JOIN Zamowienia ON Zamowienia kropka Identyfikator znak równości otwórz nawias kwadratowy Sklad podkreślnik zamowienia zamknij nawias kwadratowy kropka otwórz nawias kwadratowy Nr podkreślnik zamowienia zamknij nawias kwadratowy.
Linia 7. zamknij nawias okrągły.
Linia 8. INNER JOIN Dania on Dania kropka Nazwy znak równości otwórz nawias kwadratowy Sklad podkreślnik zamowienia zamknij nawias kwadratowy kropka Danie.
SELECT Zamowienia.Identyfikator, Dania.Ceny, [Sklad_zamowienia].Danie,
[Sklad_zamowienia].Liczba, Zamowienia.[Data_wizyty]
FROM
(
[Sklad_zamowienia]
INNER JOIN Zamowienia ON Zamowienia.Identyfikator = [Sklad_zamowienia].[Nr_zamowienia]
)
INNER JOIN Dania on Dania.Nazwy = [Sklad_zamowienia].Danie
Pora na dodanie warunku z datami zawartymi w poleceniu. W tym celu można wykorzystać polecenie BETWEENpolecenie BETWEENBETWEEN. Pamiętaj, że prawy przedział BETWEEN należy zwiększyć o 1 – wynika to z definicji funkcji. Uwaga: został tu zastosowany amerykański format daty.
Linia 1. SELECT Zamowienia kropka Identyfikator przecinek Dania kropka Ceny przecinek otwórz nawias kwadratowy Sklad podkreślnik zamowienia zamknij nawias kwadratowy kropka Danie przecinek.
Linia 2. otwórz nawias kwadratowy Sklad podkreślnik zamowienia zamknij nawias kwadratowy kropka Liczba przecinek Zamowienia kropka otwórz nawias kwadratowy Data podkreślnik wizyty zamknij nawias kwadratowy.
Linia 3. FROM.
Linia 4. otwórz nawias okrągły.
Linia 5. otwórz nawias kwadratowy Sklad podkreślnik zamowienia zamknij nawias kwadratowy.
Linia 6. INNER JOIN Zamowienia ON Zamowienia kropka Identyfikator znak równości otwórz nawias kwadratowy Sklad podkreślnik zamowienia zamknij nawias kwadratowy kropka otwórz nawias kwadratowy Nr podkreślnik zamowienia zamknij nawias kwadratowy.
Linia 7. zamknij nawias okrągły.
Linia 8. INNER JOIN Dania on Dania kropka Nazwy znak równości otwórz nawias kwadratowy Sklad podkreślnik zamowienia zamknij nawias kwadratowy kropka Danie.
Linia 9. WHERE Zamowienia kropka otwórz nawias kwadratowy Data podkreślnik wizyty zamknij nawias kwadratowy BETWEEN kratka 03 prawy ukośnik 01 prawy ukośnik 2021 kratka AND kratka 03 prawy ukośnik 08 prawy ukośnik 2021 kratka.
SELECT Zamowienia.Identyfikator, Dania.Ceny, [Sklad_zamowienia].Danie,
[Sklad_zamowienia].Liczba, Zamowienia.[Data_wizyty]
FROM
(
[Sklad_zamowienia]
INNER JOIN Zamowienia ON Zamowienia.Identyfikator = [Sklad_zamowienia].[Nr_zamowienia]
)
INNER JOIN Dania on Dania.Nazwy = [Sklad_zamowienia].Danie
WHERE Zamowienia.[Data_wizyty] BETWEEN #03/01/2021# AND #03/08/2021#
W kolejnym kroku dokonujemy grupowania zgodnie z identyfikatorem zamówienia za pomocą polecenia GROUP BY. Pozwala to na obliczenie sumy (polecenie SUM) wartości dla każdego zamówienia.
Ważne jest, aby w tym kroku usunąć z polecenia SELECT kolumny, które ani nie są agregowane, ani nie występują w GROUP BY (czyli należy usunąć np. kolumnę Data). Inaczej kwerenda nie zadziała.
Linia 1. SELECT Zamowienia kropka Identyfikator przecinek.
Linia 2. SUM otwórz nawias okrągły otwórz nawias kwadratowy Sklad podkreślnik zamowienia zamknij nawias kwadratowy kropka Liczba asterysk Dania kropka Ceny zamknij nawias okrągły AS Wartosc.
Linia 3. FROM.
Linia 4. otwórz nawias okrągły.
Linia 5. otwórz nawias kwadratowy Sklad podkreślnik zamowienia zamknij nawias kwadratowy.
Linia 6. INNER JOIN Zamowienia ON Zamowienia kropka Identyfikator znak równości otwórz nawias kwadratowy Sklad podkreślnik zamowienia zamknij nawias kwadratowy kropka otwórz nawias kwadratowy Nr podkreślnik zamowienia zamknij nawias kwadratowy.
Linia 7. zamknij nawias okrągły.
Linia 8. INNER JOIN Dania on Dania kropka Nazwy znak równości otwórz nawias kwadratowy Sklad podkreślnik zamowienia zamknij nawias kwadratowy kropka Danie.
Linia 9. WHERE Zamowienia kropka otwórz nawias kwadratowy Data podkreślnik wizyty zamknij nawias kwadratowy BETWEEN kratka 03 prawy ukośnik 01 prawy ukośnik 2021 kratka AND kratka 03 prawy ukośnik 08 prawy ukośnik 2021 kratka.
Linia 10. GROUP BY Zamowienia kropka Identyfikator.
SELECT Zamowienia.Identyfikator,
SUM([Sklad_zamowienia].Liczba* Dania.Ceny) AS Wartosc
FROM
(
[Sklad_zamowienia]
INNER JOIN Zamowienia ON Zamowienia.Identyfikator = [Sklad_zamowienia].[Nr_zamowienia]
)
INNER JOIN Dania on Dania.Nazwy = [Sklad_zamowienia].Danie
WHERE Zamowienia.[Data_wizyty] BETWEEN #03/01/2021# AND #03/08/2021#
GROUP BY Zamowienia.Identyfikator
Właśnie utworzyliśmy kwerendę, która zawiera sumaryczne wartości wszystkich zamówień utworzonych pomiędzy 01.03.2021 a 07.03.2021.
Teraz wystarczy tylko obliczyć średnią pola Wartosc dla kwerendy. W tym celu najwygodniej będzie zapisać aktualną kwerendę pod konkretną nazwą, np. Wartość_zamowienia, a następnie utworzyć krótką kwerendę liczącą średnią i wywołać ją na Wartosc_zamowienia.
Linia 1. SELECT AVG otwórz nawias okrągły Wartosc zamknij nawias okrągły AS Srednia.
Linia 2. FROM otwórz nawias kwadratowy Wartosci podkreślnik zamowienia zamknij nawias kwadratowy.
SELECT AVG(Wartosc) AS Srednia
FROM [Wartosci_zamowienia]
Po wywołaniu tej kwerendy, powinniśmy uzyskać wynik.
Poprawna odpowiedź:
48,00 zł
Schemat oceniania:
poprawne obliczenie średniej wartości zamówień w podanym przedziale – 3 punkty,
obliczenie wartości zamówień w podanym przedziale – 2 punkty,
obliczenie wartości wszystkich zamówień – 1 punkt,
brak obliczenia średniej lub wartości zamówień lub obliczenia niepoprawne – 0 punktów.
Słownik
funkcja agregująca
funkcja agregująca
funkcja, która przyjmuje zbiór danych jako argument i zwraca pojedynczą wartość; może to być np.: Suma, Liczba, Minimalna wartość, Średnia
polecenie BETWEEN
polecenie BETWEEN
sprawdza, czy wartość mieści się w danym przedziale; formuła to: Wartosc BETWEEN LewyPrzedzial AND PrawyPrzedzial
odpowiada warunkowi logicznemu:
LewyPrzedzial <= Wartosc AND Wartosc <= PrawyPrzedzial