INF.03 · Bazy danych i SQL

Apteka Zdrowie: podzapytania i aktualizacja stanów

Apteka prowadzi rejestr leków i sprzedaży i chce wiedzieć, które leki się kończą. Zadanie łączy proste filtry z podzapytaniem i aktualizacją stanów magazynowych po sprzedaży. Aktualizacja jest tu najważniejsza i najbardziej ryzykowna, więc wykonaj ją według schematu: sprawdź, zmień, sprawdź.

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

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

1 · Sytuacja

Scenariusz i przygotowanie

Zaimportuj bazę apteka_zdrowie (Producenci, KategorieLekow, Leki, Sprzedaz). Pobierzesz ją z sekcji „Pliki do pracy”.

Uwaga: jeśli ta baza istnieje już z wcześniejszej lekcji, usuń ją przed importem (DROP DATABASE apteka_zdrowie;).

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ż leki, których stan magazynowy jest niższy niż 20 sztuk (nazwa i stan).
    Oczekiwany wynik2 wiersze
  2. Policz łączną liczbę sprzedanych sztuk dla każdego leku (nazwa i suma).
    Oczekiwany wynik8 wierszy
  3. Znajdź lek, który sprzedał się najlepiej pod względem liczby sztuk (jeden wiersz).
    Oczekiwany wynik1 wierszpierwszy wiersz: Vitaminum C · 5
  4. Znajdź kategorię leków o najwyższej średniej cenie (podzapytanie z AVG i MAX albo sortowanie z LIMIT 1).
    Oczekiwany wynik1 wierszpierwszy wiersz: Na alergię · 30.00
  5. Zaktualizuj stan magazynowy każdego leku, odejmując sumę sprzedanych sztuk. Najpierw SELECT, który pokazuje stan po zmianie, potem jeden UPDATE z JOIN do podzapytania.Wykonaj tę zmianę tylko raz. Drugie uruchomienie odejmie sprzedaż jeszcze raz.
    Oczekiwany wynikzmienionych wierszy: 8
  6. Znajdź leki, które nigdy nie zostały sprzedane (LEFT JOIN i IS NULL).Pusty wynik jest tu poprawny, bo każdy lek został sprzedany.
    Oczekiwany wynik0 wierszy

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.

Powtórzenie UPDATE
Tak nie
Aktualizacja stanów uruchomiona dwa razy.

Sprzedaż jest odejmowana dwukrotnie, więc stany są błędne, a niektóre mogą zejść poniżej zera. Bez kopii nie wrócisz do stanu początkowego.

Tak
Przed zmianą zapisz stany (`SELECT nazwa, ilosc_magazyn FROM Leki;`), a po zmianie porównaj. Przy pomyłce zaimportuj bazę ponownie.

Zmiany zbiorcze wykonuje się raz i z kontrolą.

UPDATE odejmujący przy braku sprzedaży
Tak nie
UPDATE Leki SET ilosc_magazyn = ilosc_magazyn - (SELECT SUM(ilosc) FROM Sprzedaz WHERE id_leku = Leki.id_leku);

Dla leku bez sprzedaży podzapytanie zwraca NULL, a stan - NULL daje NULL: stan magazynowy zostaje wyzerowany.

Tak
SET ilosc_magazyn = ilosc_magazyn - COALESCE((SELECT SUM(...)), 0)

Przy podzapytaniu w UPDATE pilnuj przypadku braku wiersza.

Średnia z zaokrągleniem w ORDER BY
Tak nie
ORDER BY srednia  -- sortuje po zaokrąglonej wartości tekstowo?

Zaokrąglenie nie zmieni kolejności, ale błędny typ albo alias mogą. Sortuj po wyrażeniu AVG(l.cena).

Tak
ORDER BY AVG(l.cena) DESC

Sortuj po wartości, a nie po jej sformatowanej wersji.

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