INF.03 · Bazy danych i SQL

Sklep zoologiczny: zamówienia z wielu pozycji

W tym sklepie jedno zamówienie może mieć kilka pozycji, więc dane są rozbite na dwie tabele: Zamowienia (nagłówek) i PozycjeZamowienia (produkty). To klasyczny wzór z normalizacji w praktyce. Zadanie łączy złączenia, sumy, widok i zbiorczą aktualizację stanów.

  • około 45 minut
  • zadanie w stylu egzaminacyjnym
  • baza: sklep_zoologiczny

Po tej lekcji: wykonasz komplet zapytań w warunkach egzaminacyjnych i udokumentujesz każde zrzutem ekranu.

1 · Sytuacja

Scenariusz i przygotowanie

Zaimportuj bazę sklep_zoologiczny (Klienci, KategorieProduktow, Produkty, Zamowienia, PozycjeZamowienia).

Uwaga: istnieje też starsza baza sklep_zoo z innego zadania (o innej strukturze). Tu pracujemy na sklep_zoologiczny.

2 · Do wykonania

Zapytania do wykonania

Dla każdego zapytania zrób zrzut ekranu. Oczekiwane liczby wierszy podano przy zadaniach, a pełne rozwiązania znajdziesz dopiero po wykonaniu pracy samodzielnie.

  1. Pokaż każdą pozycję zamówień z numerem zamówienia, nazwą produktu i ilością (trzy tabele).
    Oczekiwany wynik12 wierszy
  2. Oblicz wartość każdego zamówienia osobno (cena razy ilość, zsumowane w zamówieniu).
    Oczekiwany wynik6 wierszypierwszy wiersz: 1 · 188.80
  3. Znajdź klienta o największej łącznej wartości wszystkich zamówień (jeden wiersz).
    Oczekiwany wynik1 wierszpierwszy wiersz: Bartłomiej · Sadowski · 452.00
  4. Pokaż produkty, które nigdy nie znalazły się w żadnym zamówieniu.W tych danych wynik jest pusty.
    Oczekiwany wynik0 wierszy
  5. Znajdź kategorię produktów generującą największy przychód (jeden wiersz).
    Oczekiwany wynik1 wierszpierwszy wiersz: Akcesoria · 388.00
  6. Znajdź klientów, którzy złożyli więcej niż jedno zamówienie (GROUP BY i HAVING).
    Oczekiwany wynik2 wiersze
  7. Utwórz widok RaportSprzedazyKategorii (nazwa kategorii i łączna wartość sprzedaży). Odczytaj go przez SELECT *.
    Oczekiwany wynik4 wiersze
  8. Zaktualizuj stan magazynowy każdego sprzedanego produktu, odejmując sprzedane ilości (jedno polecenie z JOIN do podzapytania, wykonane tylko raz).
    Oczekiwany wynikzmienionych wierszy: 10

3 · Sprawdź się

Czy Twoje wyniki się zgadzają?

To szybka kontrola, a nie ocena. Liczba wierszy jest pierwszym sygnałem, że warunek albo złączenie działa tak, jak trzeba.

Ta interakcja wymaga włączonego JavaScriptu.

4 · Zanim się pomylisz

Trzy pułapki w tym zadaniu

Rozwiń każdą z nich.

Wartość zamówienia z samych cen
Tak nie
SUM(p.cena)

Pomijasz ilość, więc zamówienie 5 sztuk karmy liczy się jak jedna sztuka.

Tak
SUM(p.cena * pz.ilosc)

Wartość pozycji to cena razy ilość.

Pominięcie tabeli pozycji
Tak nie
FROM Zamowienia z JOIN Produkty p ...

Tabele nie mają bezpośredniej relacji: zamówienie i produkt łączy dopiero tabela pozycji.

Tak
FROM PozycjeZamowienia pz JOIN Zamowienia z ... JOIN Produkty p ...

Relacja N:M zawsze przechodzi przez tabelę pośredniczącą.

Aktualizacja stanów dwa razy
Tak nie
Powtórne uruchomienie UPDATE stanów.

Sprzedaż zostanie odjęta jeszcze raz i stany zejdą poniżej prawdziwych.

Tak
Zapisz stany przed zmianą i wykonaj `UPDATE` raz. Przy pomyłce zaimportuj bazę ponownie.

Zbiorcze modyfikacje wykonuj raz i z kontrolą.

5 · Sprawdź sam siebie

Zanim oddasz pracę

Ta lista odpowiada temu, co sprawdza komisja egzaminacyjna: poprawną bazę, poprawne wyniki i dokumentację.

Podsumowanie

Co warto zapamiętać

  1. Czytaj polecenie jak opis wyniku: kolumny, tabele, warunek, kolejność.
  2. Złączenia buduj po jednym i kontroluj liczbę wierszy.
  3. Pusty wynik też może być poprawny, więc sprawdź go kontrolnym zapytaniem.
  4. Dokumentuj pracę zrzutami i zapisanym plikiem.

Dalej na swoim koncie

Rozgrzewki, quiz i zadania są w biurku ucznia.

Teoria jest otwarta dla wszystkich. Żeby rozwiązywać zadania, zapisywać szkice i widzieć swój postęp, zaloguj się.

Zaloguj się →
Źródła i dalsza nauka