Bazy Danych, laboratorium, 2008.10.09

0001-01-01

Plan zajęć:

przygotuj bazę zdefiniowaną w skrypcie lab02_create.sql
dodaj własne dane,
wykonaj podane niżej ćwiczenia.

Przydatne informacje:

funkcje dt. dat
sysdate -- zmienna zwracająca bieżąca date,
NVL(x,y) -- zwraca x jeśli x!=NULL, wpp. y,

Ćwiczenie. Wypisz dokładne daty wypożyczeń (łącznie z godziną) książek które jeszcze nie zostały zwrócone.

Rozwiązanie:

SELECT pesel,nr_ew,TO_CHAR(data_wypozyczenia,'YYYY-MM-DD HH24:MI:SS') 
  FROM wypozyczenia
 WHERE data_zwrotu IS NULL
 ORDER BY data_wypozyczenia DESC;

Ćwiczenie. Sprawdź poprawność wprowadzonych dat wypożyczeń i zwrotów książek, w szczególności:

czy data_zwrotu < data_wypożyczenia
czy data_wypożyczenia i data_zwrotu < aktualna data

Rozwiązanie:

SELECT * 
  FROM wypozyczenia
 WHERE data_wypozyczenia >= data_zwrotu
    OR data_wypozyczenia > sysdate;

Ćwiczenie. Dla każdego wypożyczenia wypisz jego czas (również dla tych, które są jeszcze wypożyczone).

Rozwiązanie (1):

SELECT pesel,nr_ew,data_wypozyczenia,data_zwrotu,
       NVL(data_zwrotu,sysdate)-data_wypozyczenia AS roznica_w_dniach
  FROM wypozyczenia 
 ORDER BY pesel,nr_ew,data_wypozyczenia;

Rozwiązanie (2):

SELECT pesel,nr_ew,data_wypozyczenia,data_zwrotu,
       TRUNC((NVL(data_zwrotu,sysdate)-data_wypozyczenia)*24) AS roznica_w_godzinach
  FROM wypozyczenia 
 ORDER BY pesel,nr_ew,data_wypozyczenia;

Rozwiązanie (3):

column roznica_dokladna format a30;
SELECT pesel,nr_ew,data_wypozyczenia,data_zwrotu,
       NUMTODSINTERVAL(NVL(data_zwrotu,sysdate)-data_wypozyczenia,'day') AS roznica_dokladna
  FROM wypozyczenia 
 ORDER BY pesel,nr_ew,data_wypozyczenia;

Ćwiczenie. Wyznacz najdłużej wypożyczoną książkę (w tym te które są jeszcze wypożyczone).

Rozwiązanie:

COLUMN tytul a25;
SELECT k.tytul,w.*
 FROM wypozyczenia w,ksiazki k
  WHERE w.nr_ew=k.nr_ew
    AND (NVL(data_zwrotu,sysdate)-data_wypozyczenia) = 
        (SELECT MAX(NVL(data_zwrotu,sysdate)-data_wypozyczenia) FROM wypozyczenia);

Ćwiczenie. Dla każdej książki wyznacz jest najkrótszy i najdłuższy czas wypożyczenia.

Rozwiązanie:

COLUMN tytul FORMAT a25;
SELECT nr_ew,tytul,
       (SELECT MIN(NVL(data_zwrotu,sysdate)-data_wypozyczenia) 
        FROM wypozyczenia w WHERE w.nr_ew=k.nr_ew) AS min,
       (SELECT MAX(NVL(data_zwrotu,sysdate)-data_wypozyczenia) 
        FROM wypozyczenia w WHERE w.nr_ew=k.nr_ew) AS max
  FROM ksiazki k;

Ćwiczenie. Dla każdej osoby wyznacz dla niej średni czas wypożyczenia książek (pomiń trwające wypożyczenia), posortuje wg. malejącej średniej.

Rozwiązanie:

SELECT pesel,
       (SELECT AVG(data_zwrotu-data_wypozyczenia) FROM wypozyczenia w 
         WHERE w.pesel=c.pesel AND data_zwrotu IS NOT NULL)
       as sredni_czas
  FROM czytelnicy c
 ORDER BY sredni_czas DESC;

Ćwiczenie. Dla każdej książki wyznacz ile różnych osób ją wypożyczyło.

Rozwiązanie:

SELECT k.nr_ew,k.tytul,COUNT(DISTINCT w.pesel)
  FROM ksiazki k LEFT JOIN wypozyczenia w ON (k.nr_ew=w.nr_ew)
 GROUP BY k.nr_ew,k.tytul;

Ćwiczenie. Wypisz tytuł i autora najczęściej wypożyczonej książki (lub książek jeśli jest wiele spełniających ten warunek).

Rozwiązanie:

SELECT k.tytul,k.autor
  FROM ksiazki k
 WHERE 
  (SELECT COUNT(*) FROM wypozyczenia w WHERE w.nr_ew=k.nr_ew) 
  &gt;= ALL
  (SELECT COUNT(*) FROM wypozyczenia GROUP BY nr_ew);

Ćwiczenie. Podaj książki, które były u czytelników w momencie 2008-09-15 12:00.

Rozwiązanie:

SELECT k.tytul,w.data_wypozyczenia,w.data_zwrotu
  FROM wypozyczenia w,ksiazki k
 WHERE w.nr_ew=k.nr_ew
   AND w.data_wypozyczenia < TO_DATE('2008-09-15 12:00','YYYY-MM-DD HH24:MI:SS')
   AND NVL(w.data_zwrotu,sysdate) > TO_DATE('2008-09-15 12:00','YYYY-MM-DD HH24:MI:SS');

Ćwiczenie. Podaj datę (najlepiej z dokładną godziną) podczas której było wypożyczonych najwięcej książek.

Rozwiązanie (lista dat z ilością wypożyczonych książek):

SELECT TO_CHAR(w.data_wypozyczenia,'yyyy-mm-dd hh24:mi') as data,
  (SELECT COUNT(*) 
     FROM wypozyczenia ww 
    WHERE ww.data_wypozyczenia <= w.data_wypozyczenia 
      AND (ww.data_zwrotu IS NULL OR ww.data_zwrotu &gt w.data_wypozyczenia)
      ) as liczba_wypozyczonych_ksiazek
   FROM wypozyczenia w;

Ćwiczenie. Sprawdź czy jakaś książka nie była wypożyczona dwa razy w tym samym momencie czasu.

Rozwiązanie:

SELECT w.nr_ew,w.pesel,w.data_wypozyczenia,w.data_zwrotu,ww.pesel,ww.data_wypozyczenia,ww.data_zwrotu
  FROM wypozyczenia w,wypozyczenia ww
 WHERE ww.nr_ew=w.nr_ew 
   AND ww.data_wypozyczenia > w.data_wypozyczenia
   AND ww.data_wypozyczenia < NVL(w.data_zwrotu,sysdate);

Bazy Danych, laboratorium, 2008.10.09

Tomasz Waleń

Assistant Professor