INF.03 · Bazy danych i SQL

Szkoła językowa: zestawienia z wielu tabel

Szkoła językowa zarządza kursami, lektorami i zapisami uczniów, a zapis ma też informację, czy kurs został opłacony. To dobre ćwiczenie na zestawienia z kilku tabel i na rozróżnienie wszystkich zapisów od opłaconych.

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

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

1 · Sytuacja

Scenariusz i przygotowanie

Zaimportuj bazę szkola_jezykowa (Lektorzy, Kursy, Uczniowie, Zapisy). Kolumna oplacone w tabeli Zapisy przyjmuje wartości 0 (nie) i 1 (tak).

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ż wszystkie kursy razem z imieniem i nazwiskiem prowadzącego lektora.
    Oczekiwany wynik5 wierszy
  2. Pokaż uczniów, którzy zapisali się na kurs, ale jeszcze nie zapłacili (imię, nazwisko, kurs).
    Oczekiwany wynik2 wiersze
  3. Policz, ilu uczniów zapisało się na każdy kurs.
    Oczekiwany wynik5 wierszy
  4. Oblicz łączny przychód szkoły z kursów już opłaconych (suma cen kursów po opłaconych zapisach).
    Oczekiwany wynik1 wierszpierwszy wiersz: 3130.00
  5. Znajdź lektora, którego kursy mają największą łączną liczbę zapisów (jeden wiersz).
    Oczekiwany wynik1 wierszpierwszy wiersz: John · Smith
  6. Pokaż uczniów zapisanych na więcej niż jeden kurs (GROUP BY i HAVING).
    Oczekiwany wynik3 wiersze

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.

Przychód ze wszystkich zapisów
Tak nie
SELECT SUM(k.cena) FROM Zapisy z JOIN Kursy k ...

Wliczasz też kursy nieopłacone, więc przychód jest zawyżony.

Tak
... WHERE z.oplacone = 1

Przychód to tylko to, co zostało opłacone.

oplacone = FALSE
Tak nie
WHERE z.oplacone = FALSE

Działa w MySQL i MariaDB (FALSE to 0), ale lepiej pisać wprost = 0, żeby polecenie było czytelne.

Tak
WHERE z.oplacone = 0

W tej bazie wartość logiczna jest zapisana jako 0 lub 1.

COUNT(*) bez grupowania po lektorze
Tak nie
SELECT l.nazwisko, COUNT(*) FROM ... ;

Brak GROUP BY: jedna suma dla wszystkich lektorów albo błąd.

Tak
GROUP BY l.id_lektora, l.imie, l.nazwisko

Liczysz zapisy w grupie lektora.

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