Zaawansowane zapytania SQL, relacje miedzy tabelami, zlaczenia
ð Podstawa programowa: II.3dRelacyjna baza danych przechowuje dane w tabelach (relacjach), ktore sa ze soba powiazane poprzez klucze. Kazda tabela ma:
CREATE TABLE uczniowie (
id INTEGER PRIMARY KEY AUTOINCREMENT,
imie TEXT NOT NULL,
nazwisko TEXT NOT NULL,
klasa TEXT,
data_urodzenia DATE
);
CREATE TABLE oceny (
id INTEGER PRIMARY KEY AUTOINCREMENT,
uczen_id INTEGER,
przedmiot TEXT NOT NULL,
ocena INTEGER CHECK(ocena BETWEEN 1 AND 6),
data_wystawienia DATE,
FOREIGN KEY (uczen_id) REFERENCES uczniowie(id)
);-- Sortowanie i ograniczanie wynikow
SELECT * FROM uczniowie ORDER BY nazwisko ASC LIMIT 10;
-- Grupowanie i funkcje agregujace
SELECT przedmiot, AVG(ocena) AS srednia, COUNT(*) AS ile
FROM oceny
GROUP BY przedmiot
HAVING AVG(ocena) > 3.5;
-- Podzapytania
SELECT imie, nazwisko FROM uczniowie
WHERE id IN (
SELECT uczen_id FROM oceny
WHERE ocena = 6
);JOIN laczy dane z wielu tabel na podstawie relacji miedzy nimi:
-- INNER JOIN - tylko pasujace rekordy z obu tabel
SELECT u.imie, u.nazwisko, o.przedmiot, o.ocena
FROM uczniowie u
INNER JOIN oceny o ON u.id = o.uczen_id;
-- LEFT JOIN - wszystkie rekordy z lewej tabeli
SELECT u.imie, u.nazwisko, COUNT(o.id) AS liczba_ocen
FROM uczniowie u
LEFT JOIN oceny o ON u.id = o.uczen_id
GROUP BY u.id;
-- Zlaczenie wielu tabel
SELECT u.imie, u.nazwisko, p.nazwa, o.ocena
FROM uczniowie u
JOIN oceny o ON u.id = o.uczen_id
JOIN przedmioty p ON o.przedmiot_id = p.id;-- Wstawianie danych
INSERT INTO uczniowie (imie, nazwisko, klasa)
VALUES ('Jan', 'Kowalski', '2TI');
-- Aktualizacja
UPDATE uczniowie SET klasa = '2TP' WHERE id = 5;
-- Usuwanie
DELETE FROM oceny WHERE ocena = 1 AND przedmiot = 'Informatyka';Zaprojektuj baze danych biblioteki szkolnej z tabelami: ksiazki (id, tytul, autor, rok_wydania, gatunek), czytelnicy (id, imie, nazwisko, klasa), wypozyczenia (id, ksiazka_id, czytelnik_id, data_wypozyczenia, data_zwrotu). Napisz zapytania SQL tworzace te tabele z odpowiednimi kluczami i ograniczeniami.
CREATE TABLE ksiazki (
id INTEGER PRIMARY KEY AUTOINCREMENT,
tytul TEXT NOT NULL,
autor TEXT NOT NULL,
rok_wydania INTEGER,
gatunek TEXT
);
CREATE TABLE czytelnicy (
id INTEGER PRIMARY KEY AUTOINCREMENT,
imie TEXT NOT NULL,
nazwisko TEXT NOT NULL,
klasa TEXT
);
CREATE TABLE wypozyczenia (
id INTEGER PRIMARY KEY AUTOINCREMENT,
ksiazka_id INTEGER,
czytelnik_id INTEGER,
data_wypozyczenia DATE NOT NULL,
data_zwrotu DATE,
FOREIGN KEY (ksiazka_id) REFERENCES ksiazki(id),
FOREIGN KEY (czytelnik_id) REFERENCES czytelnicy(id)
);Na podstawie bazy biblioteki napisz zapytania: a) Wyswietl liste aktualnie wypozyczonych ksiazek z imionami czytelnikow (data_zwrotu IS NULL), b) Znajdz 5 najpopularniejszych ksiazek (najczesciej wypozyczanych), c) Wyswietl czytelnikow, ktorzy nie oddali zadnej ksiazki na czas (ponad 30 dni).
-- a) Aktualnie wypozyczone
SELECT k.tytul, c.imie, c.nazwisko, w.data_wypozyczenia
FROM wypozyczenia w
JOIN ksiazki k ON w.ksiazka_id = k.id
JOIN czytelnicy c ON w.czytelnik_id = c.id
WHERE w.data_zwrotu IS NULL;
-- b) 5 najpopularniejszych
SELECT k.tytul, COUNT(*) AS ile_razy
FROM wypozyczenia w
JOIN ksiazki k ON w.ksiazka_id = k.id
GROUP BY k.id
ORDER BY ile_razy DESC
LIMIT 5;
-- c) Przeterminowane
SELECT DISTINCT c.imie, c.nazwisko
FROM wypozyczenia w
JOIN czytelnicy c ON w.czytelnik_id = c.id
WHERE w.data_zwrotu IS NULL
AND julianday('now') - julianday(w.data_wypozyczenia) > 30;Napisz zapytania SQL generujace raport: a) Liczba ksiazek wg gatunkow, b) Srednia liczba wypozyczen na czytelnika, c) Miesiac z najwieksza liczba wypozyczen, d) Autorzy z wiecej niz 3 ksiazkami w bibliotece.
Dodaj do bazy tabele "rezerwacje" (uczen moze zarezerwowac niedostepna ksiazke). Napisz zapytanie, ktore dla kazdej ksiazki wyswietla jej status: "Dostepna", "Wypozyczona" (z imieniem czytelnika) lub "Zarezerwowana" (z liczba oczekujacych). Uzyj LEFT JOIN i CASE WHEN.