Kolokwium SQL — Wypożyczalnia rowerów

Bazy danych · PostgreSQL · Autor pierwotnego kolokwium: Krzysztof Stencel.

Kolokwium z 7 listopada 2025 r. o miejskiej wypożyczalni rowerów: klienci odbierają i zwracają rowery na stacjach, a system rejestruje czas i koszt przejazdów.

Zadania podstawowe 1–10 są warte po 1 punkcie. Zadania 11–12 z rekurencji są dla chętnych — po 1 punkcie dodatkowym.

Pliki do pobrania

Uruchomienie

W jednym katalogu proszę zapisać skrypty lub rozpakować paczkę studencką. Po uruchomieniu w tym katalogu programu psql i połączeniu z własną bazą proszę sprawdzić schemat i wczytać dane:

SELECT current_user, current_schema();
\i rowery.create.sql
\i rowery.insert.sql

W nazwie rozwiazania.imie.nazwisko.sql proszę wstawić swoje imię i nazwisko, np. rozwiazania.Jan.Kowalski.sql. Wewnątrz pliku proszę zastąpić kolejne SELECT n AS zadanie; własnymi zapytaniami.

\i rozwiazania.Jan.Kowalski.sql

Po zakończeniu ćwiczenia \i rowery.drop.sql usuwa tabele zestawu wraz z danymi.

Schemat w skrócie

W pierwotnej wersji kolokwium przykładowe rozwiązania opracował Jan Kowalczyk; autorem zadania o najdłuższym ciągu przesiadek jest Krzysztof Stencel.

Każde zadanie należy rozwiązać jednym niezależnym zapytaniem. Powinno ono działać także dla innych danych zgodnych ze schematem i założeniami zadania. Jeśli nie wskazano porządku wyników, kolejność wierszy jest dowolna.

Zadania podstawowe 1–10

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

Zadania dodatkowe 11–12: rekurencja

Oba zadania są nieobowiązkowe. Każde jest warte 1 punkt dodatkowy. Proszę użyć WITH RECURSIVE.

11. 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.

12. 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.