Zadanie: Zamówienia w restauracji

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.

Pliki te można pobrać z załącznika:

Plik Klienci.txt zawiera informacje o klientach restauracji w postaci pól: Identyfikator, Imie i Nazwisko. Oto fragment tego pliku:

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.

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.

Identyfikator	Nr_klienta	Data_wizyty
1	1	02.03.2021
2	3	04.03.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.

Nr_zamowienia	Danie	Liczba
1	Margherita	2
2	Chleb Czosnkowy	1

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.

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.

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.

SELECT Danie, Sum(Liczba) AS [Sumaryczna_liczba]
FROM [Sklad zamowienia]
GROUP BY Danie

Teraz gdzieś w pliku znajduje się poszukiwana przez nas maksymalna wartość. Możną ją znaleźć przez posortowanie wyniku za pomocą polecenia ORDER BY.

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

Uruchom kwerendę. Przeczytaj zawartość najwyższego rekordu.

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.

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

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.

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.

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.

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