-- Kolokwium SQL — Wypożyczalnia rowerów (PostgreSQL, psql). -- Wzorcowe rozwiązania. -- Zadania 1–10: po 1 punkcie. Zadania 11–12 z rekurencji są NIEOBOWIĄZKOWE, po 1 punkcie dodatkowym. \encoding UTF8 \set ON_ERROR_STOP on \pset pager off \pset null '(NULL)' \echo 'Zadanie 1. Kto wypożyczył który rower?. Dla każdego wypożyczenia podać imię i nazwisko klienta, numer seryjny roweru i datę z godziną wypożyczenia. Zachować wiersz również wtedy, gdy nie ma danych klienta lub roweru. Kolumny: imie, nazwisko, numer_seryjny, data_wypozyczenia.' SELECT k.imie, k.nazwisko, r.numer_seryjny, w.data_wypozyczenia FROM wypozyczenia w LEFT JOIN klienci k USING (id_klienta) LEFT JOIN rowery r USING (id_roweru) ORDER BY w.id_wypozyczenia; \echo 'Zadanie 2. Początek i koniec trasy. Dla każdego wypożyczenia podać jego identyfikator oraz nazwy stacji początkowej i końcowej. Brakującą stację pokazać jako NULL. Kolumny: id_wypozyczenia, stacja_poczatkowa, stacja_koncowa.' SELECT w.id_wypozyczenia, s.nazwa AS stacja_poczatkowa, t.nazwa AS stacja_koncowa FROM wypozyczenia w LEFT JOIN stacje s ON s.id_stacji = w.id_stacji_start LEFT JOIN stacje t ON t.id_stacji = w.id_stacji_koniec ORDER BY w.id_wypozyczenia; \echo 'Zadanie 3. Rowery i obecni użytkownicy. Wypisać wszystkie rowery (identyfikator, numer seryjny). Jeżeli rower ma otwarte wypożyczenie, dołączyć imię i nazwisko klienta; w pozostałych przypadkach podać NULL. Kolumny: id_roweru, numer_seryjny, imie, nazwisko.' SELECT r.id_roweru, r.numer_seryjny, k.imie, k.nazwisko FROM rowery r LEFT JOIN wypozyczenia w ON w.id_roweru = r.id_roweru AND w.data_zwrotu IS NULL LEFT JOIN klienci k ON k.id_klienta = w.id_klienta ORDER BY r.id_roweru, w.id_wypozyczenia; \echo 'Zadanie 4. Liczba wypożyczeń klienta. Dla każdego klienta podać identyfikator, imię, nazwisko oraz liczbę jego wypożyczeń. Uwzględnić klientów, którzy niczego nie wypożyczyli. Kolumny: id_klienta, imie, nazwisko, liczba_wypozyczen.' SELECT k.id_klienta, k.imie, k.nazwisko, COUNT(w.id_wypozyczenia) AS liczba_wypozyczen FROM klienci k LEFT JOIN wypozyczenia w USING (id_klienta) GROUP BY k.id_klienta ORDER BY k.id_klienta; \echo 'Zadanie 5. Zawsze z tej samej stacji. Wypisać identyfikator, imię i nazwisko klientów, którzy co najmniej raz wypożyczyli rower, a wszystkie ich wypożyczenia zaczynały się na jednej i tej samej znanej stacji. Kolumny: id_klienta, imie, nazwisko.' SELECT k.id_klienta, k.imie, k.nazwisko FROM klienci k JOIN wypozyczenia w USING (id_klienta) GROUP BY k.id_klienta HAVING COUNT(DISTINCT w.id_stacji_start) = 1 AND COUNT(w.id_stacji_start) = COUNT(*) ORDER BY k.id_klienta; \echo 'Zadanie 6. Średni koszt według typu roweru. Dla każdego typu reprezentowanego w tabeli rowery podać średni koszt zakończonego wypożyczenia. Brak kosztów o znanej wartości oznaczyć przez NULL; wypożyczenia bezpłatne liczyć z kosztem 0. Kolumny: typ, sredni_koszt.' SELECT r.typ, AVG(w.koszt) AS sredni_koszt FROM rowery r LEFT JOIN wypozyczenia w ON w.id_roweru = r.id_roweru AND w.data_zwrotu IS NOT NULL GROUP BY r.typ ORDER BY r.typ; \echo 'Zadanie 7. Droższe od średniej. Wypisać identyfikatory wypożyczeń, których koszt jest wyższy niż średni koszt wszystkich wypożyczeń o znanym koszcie. Kolumny: id_wypozyczenia.' SELECT w.id_wypozyczenia FROM wypozyczenia w WHERE w.koszt > (SELECT AVG(koszt) FROM wypozyczenia) ORDER BY w.id_wypozyczenia; \echo 'Zadanie 8. Klienci aktywniejsi od średniej. Wypisać identyfikator, imię i nazwisko klientów, którzy mają więcej wypożyczeń niż wynosi średnia liczba wypożyczeń na klienta. Do średniej wliczyć również klientów bez wypożyczeń. Kolumny: id_klienta, imie, nazwisko.' WITH liczby AS ( SELECT k.id_klienta, k.imie, k.nazwisko, COUNT(w.id_wypozyczenia) AS ile FROM klienci k LEFT JOIN wypozyczenia w USING (id_klienta) GROUP BY k.id_klienta ) SELECT id_klienta, imie, nazwisko FROM liczby WHERE ile > (SELECT AVG(ile) FROM liczby) ORDER BY id_klienta; \echo 'Zadanie 9. Średni czas jazdy. Dla każdego typu reprezentowanego w tabeli rowery podać średni czas zakończonego wypożyczenia wyrażony w minutach (z częścią ułamkową). Jeżeli nie ma zakończonych wypożyczeń, podać NULL. Kolumny: typ, srednia_minut.' SELECT r.typ, EXTRACT(EPOCH FROM AVG(w.data_zwrotu - w.data_wypozyczenia)) / 60 AS srednia_minut FROM rowery r LEFT JOIN wypozyczenia w ON w.id_roweru = r.id_roweru AND w.data_zwrotu IS NOT NULL GROUP BY r.typ ORDER BY r.typ; \echo 'Zadanie 10. Rowery Anny Nowak. Podać identyfikator i numer seryjny każdego roweru, który wypożyczyła przynajmniej raz osoba o imieniu Anna i nazwisku Nowak. Nie powtarzać rowerów. Kolumny: id_roweru, numer_seryjny.' SELECT r.id_roweru, r.numer_seryjny FROM rowery r WHERE EXISTS ( SELECT 1 FROM wypozyczenia w JOIN klienci k USING (id_klienta) WHERE w.id_roweru = r.id_roweru AND k.imie = 'Anna' AND k.nazwisko = 'Nowak' ) ORDER BY r.id_roweru; \echo 'Zadanie 11. Dla chętnych (rekurencja, nieobowiązkowe). Dokąd prowadzą przesiadki?. Przesiadka z A do B oznacza, że ten sam klient bierze inny rower na stacji zwrotu A, od 0 do 3 minut po zwrocie (łącznie z obydwoma krańcami), a początek B jest późniejszy od początku A. Zaczynając od wypożyczenia nr 4, znaleźć wszystkie osiągalne bezpośrednie przesiadki: pierwszą i te, do których można dotrzeć po dalszych przesiadkach. Dla każdej pary A, B podać oba identyfikatory, imię i nazwisko klienta, nazwę stacji, oba numery seryjne oraz chwilę w połowie przerwy. Każdą parę wypisać raz. Kolumny: a, b, imie, nazwisko, nazwa, rower_a, rower_b, srodek_przerwy.' WITH RECURSIVE przesiadki(a, b) AS ( SELECT x.id_wypozyczenia, y.id_wypozyczenia FROM wypozyczenia x JOIN wypozyczenia y ON y.id_klienta = x.id_klienta AND y.id_stacji_start = x.id_stacji_koniec AND y.id_roweru <> x.id_roweru AND y.data_wypozyczenia > x.data_wypozyczenia AND y.data_wypozyczenia BETWEEN x.data_zwrotu AND x.data_zwrotu + INTERVAL '3 minutes' ), etapy(a, b) AS ( SELECT a, b FROM przesiadki WHERE a = 4 UNION SELECT p.a, p.b FROM etapy e JOIN przesiadki p ON p.a = e.b ) SELECT e.a, e.b, k.imie, k.nazwisko, s.nazwa, ra.numer_seryjny AS rower_a, rb.numer_seryjny AS rower_b, x.data_zwrotu + (y.data_wypozyczenia - x.data_zwrotu) / 2 AS srodek_przerwy FROM etapy e JOIN wypozyczenia x ON x.id_wypozyczenia = e.a JOIN wypozyczenia y ON y.id_wypozyczenia = e.b JOIN klienci k ON k.id_klienta = x.id_klienta JOIN stacje s ON s.id_stacji = x.id_stacji_koniec JOIN rowery ra ON ra.id_roweru = x.id_roweru JOIN rowery rb ON rb.id_roweru = y.id_roweru ORDER BY e.a, e.b; \echo 'Zadanie 12. Dla chętnych (rekurencja, nieobowiązkowe). Najdłuższy ciąg przesiadek. Przesiadka z A do B oznacza, że ten sam klient bierze inny rower na stacji zwrotu A, od 0 do 3 minut po zwrocie (łącznie z obydwoma krańcami), a początek B jest późniejszy od początku A. Znaleźć największą liczbę przesiadek w jednym ciągu, uwzględniając wszystkie wypożyczenia jako możliwe początki. Gdy nie było żadnej przesiadki, wynik ma wynosić 0. Kolumny: najwiecej_przesiadek.' WITH RECURSIVE przesiadki(a, b) AS ( SELECT x.id_wypozyczenia, y.id_wypozyczenia FROM wypozyczenia x JOIN wypozyczenia y ON y.id_klienta = x.id_klienta AND y.id_stacji_start = x.id_stacji_koniec AND y.id_roweru <> x.id_roweru AND y.data_wypozyczenia > x.data_wypozyczenia AND y.data_wypozyczenia BETWEEN x.data_zwrotu AND x.data_zwrotu + INTERVAL '3 minutes' ), lancuchy(koniec, liczba) AS ( SELECT b, 1 FROM przesiadki UNION SELECT p.b, l.liczba + 1 FROM lancuchy l JOIN przesiadki p ON p.a = l.koniec ) SELECT COALESCE(MAX(liczba), 0) AS najwiecej_przesiadek FROM lancuchy;