Zadanie 9 — Tworzenie zapytań SQL

Cel zadania

Celem tego zadania jest nauczenie Cię, jak formułować zapytania SQL w celu odpytywania i manipulowania danymi w naszej znormalizowanej bazie danych biblioteka. Przećwiczysz pisanie zapytań od najprostszych, wybierających dane z jednej tabeli, po bardziej złożone, które wykorzystują złączenia (JOIN) do łączenia danych z wielu tabel oraz funkcje agregujące do tworzenia podsumowań i raportów. Zdobędziesz praktyczne umiejętności, które są esencją pracy z relacyjnymi bazami danych.

Scenariusz / opis zadania

Twoja baza danych biblioteki jest już dobrze zorganizowana i wypełniona danymi. Teraz nadszedł czas, aby tchnąć w nią życie i zacząć wydobywać z niej cenne informacje. Kierownictwo biblioteki potrzebuje serii raportów: chcą znać pełną listę dostępnych książek, sprawdzić, kto aktualnie przetrzymuje jakie pozycje, a także zidentyfikować czytelników, którzy mają przeterminowane zwroty. Twoim zadaniem jest przygotowanie zestawu gotowych do użycia zapytań SQL, które odpowiedzą na te i inne pytania. Zaczniesz od prostych zapytań SELECT, aby wyświetlić zawartość poszczególnych tabel. Następnie przejdziesz do użycia klauzuli JOIN, aby połączyć informacje o czytelnikach z wypożyczonymi przez nich książkami. Nauczysz się filtrować wyniki za pomocą WHERE, grupować je z GROUP BY i HAVING, aby znaleźć np. czytelników z największą liczbą wypożyczonych książek. Oprócz zapytań odczytujących dane (DQL), przećwiczysz również podstawowe operacje modyfikacji danych (DML) takie jak INSERT i DELETE, a także zmiany w strukturze tabeli (DDL) za pomocą ALTER TABLE. To zadanie to esencja pracy analityka danych i programisty – zamienianie surowych danych w użyteczną wiedzę.

Pomocnik wykonania: Podstawowe zapytania

Poniżej znajduje się lista kroków i zapytań, które odpowiadają na podstawowe potrzeby informacyjne biblioteki. Testuj każde zapytanie w swoim kliencie SQL (np. HeidiSQL).

  1. Lista wszystkich książek: Wyświetlmy podstawowe informacje o wszystkich książkach w naszej kolekcji.
    SELECT tytul, rok_wydania FROM ksiazki;
  2. Lista wszystkich czytelników: Wygeneruj listę kontaktową czytelników.
    SELECT imie, nazwisko, email FROM czytelnicy;
  3. Kto i co ma aktualnie wypożyczone (JOIN): To kluczowe zapytanie łączy trzy tabele, aby uzyskać czytelny raport.
    SELECT c.imie, c.nazwisko, k.tytul FROM wypozyczenia w JOIN czytelnicy c ON w.id_czytelnika = c.id_czytelnika JOIN ksiazki k ON w.id_ksiazki = k.id_ksiazki WHERE w.data_zwrotu IS NULL;
  4. Kiedy jest termin zwrotu danej książki: Sprawdź termin zwrotu dla konkretnej, aktualnie wypożyczonej książki (np. o ID 25).
    SELECT termin_zwrotu FROM wypozyczenia WHERE id_ksiazki = 25 AND data_zwrotu IS NULL;
  5. Kto ma wypożyczone więcej niż 3 książki: Użyjemy grupowania i funkcji agregującej COUNT.
    SELECT c.imie, c.nazwisko, COUNT(w.id_ksiazki) AS liczba_ksiazek FROM wypozyczenia w JOIN czytelnicy c ON w.id_czytelnika = c.id_czytelnika WHERE w.data_zwrotu IS NULL GROUP BY c.id_czytelnika HAVING liczba_ksiazek > 3;
  6. Kto ma przeterminowane książki: Porównujemy termin zwrotu z dzisiejszą datą (CURDATE()).
    SELECT c.imie, c.nazwisko, k.tytul, w.termin_zwrotu FROM wypozyczenia w JOIN czytelnicy c ON w.id_czytelnika = c.id_czytelnika JOIN ksiazki k ON w.id_ksiazki = k.id_ksiazki WHERE w.data_zwrotu IS NULL AND w.termin_zwrotu < CURDATE();
  7. Wstawianie nowych danych (INSERT): Dodaj nowego pracownika.
    INSERT INTO pracownicy (imie, nazwisko, stanowisko, data_zatrudnienia) VALUES ('Barbara', 'Nowacka', 'Archiwista', '2023-05-10');
  8. Usuwanie danych (DELETE): Usuń dodanego pracownika.
    DELETE FROM pracownicy WHERE imie = 'Barbara' AND nazwisko = 'Nowacka';
  9. Dodanie nowej kolumny (ALTER TABLE): Dodajmy pole na notatki o czytelniku.
    ALTER TABLE czytelnicy ADD COLUMN uwagi TEXT NULL;
  10. Modyfikacja typu kolumny (ALTER TABLE): Zmieńmy typ kolumny uwagi na bardziej ograniczony.
    ALTER TABLE czytelnicy MODIFY COLUMN uwagi VARCHAR(500);

Dodatkowe 20 zapytań do przećwiczenia

Poniżej znajdziesz więcej przykładów, które pomogą Ci utrwalić wiedzę.

-- 1. Lista książek posortowana alfabetycznie wg tytułu.
SELECT tytul, rok_wydania FROM ksiazki ORDER BY tytul ASC;

-- 2. Znajdź książki wydane po 2000 roku.
SELECT * FROM ksiazki WHERE rok_wydania > 2000;

-- 3. Policz wszystkie książki w bazie.
SELECT COUNT(*) AS ilosc_ksiazek FROM ksiazki;

-- 4. Policz książki w każdej kategorii.
SELECT kat.nazwa, COUNT(k.id_ksiazki) AS liczba FROM ksiazki k JOIN kategorie kat ON k.id_kategorii = kat.id_kategorii GROUP BY kat.nazwa;

-- 5. Znajdź czytelników z adresem email w domenie example.com.
SELECT * FROM czytelnicy WHERE email LIKE '%@example.com';

-- 6. Pokaż 5 ostatnio zatrudnionych pracowników.
SELECT imie, nazwisko FROM pracownicy ORDER BY data_zatrudnienia DESC LIMIT 5;

-- 7. Znajdź wszystkie książki Andrzeja Sapkowskiego.
SELECT k.tytul FROM ksiazki k JOIN autorzy a ON k.id_autora = a.id_autora WHERE a.nazwisko_imie = 'Andrzej Sapkowski';

-- 8. Pokaż historię (zwrócone) wypożyczeń.
SELECT * FROM wypozyczenia WHERE data_zwrotu IS NOT NULL;

-- 9. Oblicz średni wiek książek w bibliotece (w latach).
SELECT AVG(YEAR(CURDATE()) - rok_wydania) AS sredni_wiek FROM ksiazki;

-- 10. Pokaż unikalną listę miast, z których pochodzą czytelnicy.
SELECT DISTINCT miasto FROM czytelnicy WHERE miasto IS NOT NULL ORDER BY miasto;

-- 11. Znajdź książki bez przypisanego numeru ISBN.
SELECT * FROM ksiazki WHERE isbn IS NULL;

-- 12. Zmień miasto dla czytelnika o ID 1 na 'Gdańsk'.
UPDATE czytelnicy SET miasto = 'Gdańsk' WHERE id_czytelnika = 1;

-- 13. Znajdź czytelnika, który wypożyczył najwięcej książek w całej historii.
SELECT id_czytelnika, COUNT(*) AS ilosc_wypozyczen FROM wypozyczenia GROUP BY id_czytelnika ORDER BY ilosc_wypozyczen DESC LIMIT 1;

-- 14. Pokaż wszystkich pracowników na stanowisku 'Bibliotekarz'.
SELECT * FROM pracownicy WHERE stanowisko = 'Bibliotekarz';

-- 15. Znajdź wszystkie książki, których tytuł zaczyna się od 'Wiedźmin'.
SELECT tytul FROM ksiazki WHERE tytul LIKE 'Wiedźmin%';

-- 16. Pokaż wszystkich czytelników i ich numery telefonów (nawet jeśli ich nie mają).
SELECT c.imie, c.nazwisko, t.numer FROM czytelnicy c LEFT JOIN telefony t ON c.id_czytelnika = t.id_czytelnika;

-- 17. Pokaż kategorie, w których jest więcej niż 5 książek.
SELECT kat.nazwa, COUNT(k.id_ksiazki) AS liczba
FROM ksiazki k JOIN kategorie kat ON k.id_kategorii = kat.id_kategorii
GROUP BY kat.nazwa
HAVING liczba > 5;

-- 18. Znajdź wypożyczenia dokonane w styczniu 2023.
SELECT * FROM wypozyczenia WHERE data_wypozyczenia BETWEEN '2023-01-01' AND '2023-01-31';

-- 19. Znajdź pierwszego zarejestrowanego czytelnika.
SELECT imie, nazwisko, data_rejestracji FROM czytelnicy ORDER BY data_rejestracji ASC LIMIT 1;

-- 20. Znajdź książki, które nigdy nie były wypożyczone.
SELECT k.tytul FROM ksiazki k LEFT JOIN wypozyczenia w ON k.id_ksiazki = w.id_ksiazki WHERE w.id_wypozyczenia IS NULL;

Ilustracja konsoli

+------------------------------------------------------------------+
|                                                                  |
| MariaDB [biblioteka]> SELECT imie, nazwisko FROM czytelnicy LIMIT 3; |
| +---------+-------------+                                        |
| | imie    | nazwisko    |                                        |
| +---------+-------------+                                        |
| | Jan     | Kowalski    |                                        |
| | Anna    | Nowak       |                                        |
| | Piotr   | Wiśniewski  |                                        |
| +---------+-------------+                                        |
| 3 rows in set (0.001 sec)                                        |
|                                                                  |
+------------------------------------------------------------------+

Informacje dodatkowe / uwagi