Raport przetrzymanych wypożyczeń sprzętu
W szkolnej wypożyczalni sprzętu informatycznego istnieją tabele: uczniowie(id, imie, nazwisko), sprzet(id, nazwa, kategoria) oraz wypozyczenia(id, uczen_id, sprzet_id, data_wyp, data_zwrotu). Wartość data_zwrotu równa NULL oznacza, że sprzęt nie został jeszcze zwrócony. Na dzień 20 maja 2025 trzeba przygotować listę uczniów, którzy przetrzymują sprzęt ponad 14 dni, wraz z liczbą takich pozycji. Wicedyrektor zauważył, że zapytanie z NOT IN (SELECT data_zwrotu FROM wypozyczenia) zwraca zero wierszy, mimo że w tabeli są niezwrócone rekordy. Inne zapytanie z LEFT JOIN dubluje uczniów, gdy mają kilka wypożyczeń. W bazie jest 900 uczniów, 350 egzemplarzy sprzętu i 12 000 historycznych wypożyczeń; najczęstsze zapytanie dotyczy niezwróconych pozycji po dacie. Zespół chce poprawić raport, dodać indeks i przygotować transakcję zwrotu, która jednocześnie oznacza rekord jako zwrócony i zapisuje wpis w tabeli historia_zwrotow.
1. Napisz zapytanie SQL zwracające uczniów przetrzymujących sprzęt ponad 14 dni na 2025-05-20, z liczbą pozycji.
Read the worked answer
SELECT u.id, u.imie, u.nazwisko, COUNT(*) AS liczba_pozycji
FROM wypozyczenia w
JOIN uczniowie u ON u.id = w.uczen_id
WHERE w.data_zwrotu IS NULL
AND w.data_wyp < DATE '2025-05-06'
GROUP BY u.id, u.imie, u.nazwisko
ORDER BY liczba_pozycji DESC, u.nazwisko;
Warunek data_wyp < DATE '2025-05-06' oznacza wypożyczenie przed 6 maja, czyli ponad 14 dni przed 20 maja. Data 6 maja dawałaby dokładnie 14 dni, a warunek jest „ponad 14”, więc nie może być <=. COUNT(*) liczy pozycje, a nie różnych uczniów, bo jeden uczeń może przetrzymywać kilka egzemplarzy. Grupowanie po id wystarcza w dobrze zaprojektowanej bazie, ale dodanie imienia i nazwiska jest bezpieczne, jeśli id nie jest kluczem głównym lub baza ma słabsze ograniczenia.
2. Wyjaśnij, dlaczego `NOT IN (SELECT data_zwrotu FROM wypozyczenia)` zwraca zero wierszy, i popraw warunek.
Read the worked answer
Jeśli podzapytanie zwróci choć jeden NULL, to porównanie x NOT IN (..., NULL) nie daje prawdy. Dla każdego x wynikiem jest UNKNOWN, a klauzula WHERE przepuszcza tylko TRUE. W tabeli są niezwrócone rekordy, więc NULL na pewno występuje i cały warunek degeneruje się do braku wyników. To nie jest błąd wydajności, lecz semantyka trójwartościowej logiki SQL. Poprawne podejście to NOT EXISTS albo jawne wykluczenie NULL:
SELECT u.*
FROM uczniowie u
WHERE NOT EXISTS (
SELECT 1 FROM wypozyczenia w
WHERE w.uczen_id = u.id AND w.data_zwrotu IS NULL
);
Jeśli celem jest lista niezwróconych pozycji, lepiej w ogóle nie używać NOT IN, tylko WHERE w.data_zwrotu IS NULL. Test powinien sprawdzać przypadek z NULL i bez NULL, aby potwierdzić różnicę.
3. Zaproponuj indeks i transakcję zwrotu.
Read the worked answer
Dla raportu przydatny jest indeks złożony wypozyczenia(data_zwrotu, data_wyp, uczen_id) albo indeks częściowy WHERE data_zwrotu IS NULL, jeśli baza go wspiera. Pierwsza kolumna data_zwrotu pozwala szybko odfiltrować niezwrócone, kolejne zawężają datę i ułatwiają złączenie z uczniem. Transakcja powinna być atomowa:
BEGIN;
UPDATE wypozyczenia
SET data_zwrotu = CURRENT_DATE
WHERE id = :id AND data_zwrotu IS NULL;
-- sprawdzić, czy zaktualizowano dokładnie 1 wiersz
INSERT INTO historia_zwrotow(wypozyczenie_id, data_zwrotu)
VALUES (:id, CURRENT_DATE);
COMMIT;
Jeśli liczba zaktualizowanych wierszy wynosi 0, trzeba wykonać ROLLBACK, bo pozycja mogła już zostać zwrócona lub ktoś podał zły identyfikator. Warunek data_zwrotu IS NULL chroni przed podwójnym zwrotem przy dwóch równoczesnych żądaniach.