Zadanie 9 - Praktyczne zastosowanie SQL

Analogia: Zadawanie pytań asystentowi

Wyobraź sobie, że Twoja baza danych to niezwykle zorganizowany asystent, który przechowuje wszystkie informacje o bibliotece. Język SQL (Structured Query Language) to język, w którym się z nim komunikujesz. Do tej pory uczyliśmy się głównie, jak organizować mu pracę (tworzyć "segregatory" - tabele). Teraz nauczymy się zadawać mu pytania i wydawać polecenia.

Pytania (zapytania) zaczynają się od SELECT (np. "Pokaż mi wszystkich czytelników").
Polecenia to INSERT (dodaj nowy wpis), UPDATE (zmień istniejący wpis) i DELETE (usuń wpis).

W tym zadaniu opanujesz sztukę precyzyjnego formułowania tych "rozmów", aby uzyskać dokładnie te informacje, których potrzebujesz.

Cel zadania

Celem jest praktyczne zastosowanie języka SQL do interakcji ze znormalizowaną bazą danych. Nauczysz się pisać zapytania wyciągające dane (SELECT) – od prostych list, po skomplikowane analizy wymagające łączenia wielu tabel (JOIN). Opanujesz również polecenia służące do manipulacji danymi (INSERT, UPDATE, DELETE).

Część A: Zapytania SELECT - zadawanie pytań bazie danych

Zadanie 1: Lista wszystkich książek wraz z autorami

Problem: Tytuł książki jest w tabeli ksiazki, a nazwisko autora w tabeli autorzy. Jak je połączyć?


SELECT
  k.tytul,
  a.imie_nazwisko AS autor
FROM
  ksiazki AS k
JOIN
  autorzy AS a ON k.id_autora = a.id_autora
ORDER BY
  k.tytul ASC;

Wyjaśnienie krok po kroku:

SELECT k.tytul, a.imie_nazwisko AS autor: "Pokaż mi kolumnę tytul z tabeli ksiazki oraz kolumnę imie_nazwisko z tabeli autorzy, ale w wyniku nazwij ją autor".
FROM ksiazki AS k: "Informacje bierz z tabeli ksiazki, którą na potrzeby tego zapytania będę nazywał w skrócie k". Używanie aliasów (skrótów) jest dobrą praktyką.
JOIN autorzy AS a ON k.id_autora = a.id_autora: To jest serce zapytania! "Dołącz do tego informacje z tabeli autorzy (w skrócie a). Połącz wiersze w taki sposób, aby wartość w kolumnie id_autora z tabeli k była taka sama jak wartość w kolumnie id_autora z tabeli a".
ORDER BY k.tytul ASC: "Na koniec posortuj wyniki alfabetycznie (ASC - rosnąco) według tytułu książki".

Zadanie 2: Kto i co ma aktualnie wypożyczone?

Problem: Potrzebujemy informacji z trzech tabel: nazwiska z czytelnicy, tytułu z ksiazki i faktu wypożyczenia z wypozyczenia.


SELECT
  c.imie,
  c.nazwisko,
  k.tytul,
  w.termin_zwrotu
FROM
  wypozyczenia AS w
JOIN
  czytelnicy AS c ON w.id_czytelnika = c.id_czytelnika
JOIN
  ksiazki AS k ON w.id_ksiazki = k.id_ksiazki
WHERE
  w.data_faktycznego_zwrotu IS NULL;

Wyjaśnienie krok po kroku:

FROM wypozyczenia AS w: Zaczynamy od tabeli centralnej, która łączy wszystko.
JOIN czytelnicy AS c ON ...: Do każdego wypożyczenia dołączamy dane czytelnika, pasującego po id_czytelnika.
JOIN ksiazki AS k ON ...: Do każdego wypożyczenia dołączamy też dane książki, pasującej po id_ksiazki.
WHERE w.data_faktycznego_zwrotu IS NULL: To kluczowy filtr. IS NULL oznacza "jest puste". Wybieramy tylko te wypożyczenia, które nie mają jeszcze daty zwrotu, czyli są aktywne.

Zadanie 3: Kto ma przeterminowane książki?

Problem: Musimy znaleźć aktywne wypożyczenia, których termin zwrotu już minął.


SELECT
  c.imie, c.nazwisko, k.tytul, w.termin_zwrotu,
  DATEDIFF(CURDATE(), w.termin_zwrotu) AS dni_po_terminie
FROM wypozyczenia AS w
JOIN czytelnicy AS c ON w.id_czytelnika = c.id_czytelnika
JOIN ksiazki AS k ON w.id_ksiazki = k.id_ksiazki
WHERE
  w.data_faktycznego_zwrotu IS NULL
  AND w.termin_zwrotu < CURDATE();

Co nowego?

DATEDIFF(CURDATE(), w.termin_zwrotu) AS dni_po_terminie: Używamy nowej funkcji. DATEDIFF() oblicza różnicę w dniach między dwiema datami. CURDATE() zwraca dzisiejszą datę. W ten sposób dla każdego przetrzymanego egzemplarza od razu obliczamy, ile dni minęło od terminu zwrotu.
AND w.termin_zwrotu < CURDATE(): Do naszego filtra dodajemy drugi warunek połączony operatorem AND (i). Szukamy wierszy, które spełniają oba warunki: książka nie jest zwrócona ORAZ jej termin zwrotu jest w przeszłości.

Część B: Manipulacja danymi (INSERT, UPDATE, DELETE)

Zadanie 4: Dodanie nowego czytelnika


INSERT INTO czytelnicy (imie, nazwisko, email)
VALUES ('Juliusz', 'Słowacki', 'j.slowacki@wieszcze.pl');

Zwróć uwagę, że nie podajemy id_czytelnika (bo jest AUTO_INCREMENT) ani data_rejestracji (bo ma wartość domyślną DEFAULT). Serwer zajmie się tym za nas!

Zadanie 5: Aktualizacja danych (UPDATE)

Załóżmy, że czytelniczka Anna Nowak zmieniła e-mail.


UPDATE czytelnicy
SET email = 'anna.nowak.nowy@example.com'
WHERE id_czytelnika = 1;

Bardzo ważna klauzula WHERE!

Gdybyś zapomniał o klauzuli WHERE, polecenie UPDATE zmieniłoby adres e-mail WSZYSTKIM czytelnikom w bazie! Zawsze upewnij się, że precyzyjnie wskazujesz, który wiersz chcesz zmodyfikować.

Zadanie 6: Usuwanie danych (DELETE)

Załóżmy, że chcemy usunąć czytelnika o ID = 50.


DELETE FROM czytelnicy WHERE id_czytelnika = 50;

Co jeśli czytelnik ma wypożyczone książki?

Polecenie się nie powiedzie! Baza danych, dzięki zdefiniowanym kluczom obcym, chroni integralność danych. Nie pozwoli usunąć czytelnika, jeśli istnieją w tabeli wypozyczenia wpisy, które się do niego odnoszą. Najpierw należałoby usunąć (lub zarchiwizować) jego wypożyczenia.