Laboratorium 2: pytania SQL

Plan zajęć:

przygotuj bazę zdefiniowaną w skrypcie

lab02_create.sql

drop table wypozyczenia;
drop table czytelnicy;
drop table ksiazki;
 
create table ksiazki (
  nr_ew integer primary key,
  tytul varchar(128),
  autor varchar(64)
);
 
create table czytelnicy (
  pesel char(11) primary key,
  imie varchar(64),
  nazwisko varchar(64)
);
 
create table wypozyczenia (
  nr_ew integer references ksiazki,
  pesel char(11) references czytelnicy,
  data_wypozyczenia date,
  data_zwrotu date,
  primary key (nr_ew,pesel,data_wypozyczenia)
);
 
insert into ksiazki (nr_ew,tytul,autor) values (1,'Teoria wszystkiego','J. Dziamdziak');
insert into ksiazki (nr_ew,tytul,autor) values (2,'Teoria prawie wszystkiego','J. Dziamdziak');
insert into ksiazki (nr_ew,tytul,autor) values (3,'Teoria całkowicie wszystkiego','J. Dziamdziak');
insert into ksiazki (nr_ew,tytul,autor) values (4,'Pan Tadeusz','A. Mickiewicz');
insert into ksiazki (nr_ew,tytul,autor) values (5,'Balladyna','J. Słowacki');
insert into ksiazki (nr_ew,tytul,autor) values (6,'Nieznana książka','Nieznany autor');
insert into ksiazki (nr_ew,tytul,autor) values (7,'Sztuka Programowania t.I','D. Knuth');
insert into ksiazki (nr_ew,tytul,autor) values (8,'Sztuka Programowania t.II','D. Knuth');
insert into ksiazki (nr_ew,tytul,autor) values (9,'Hobbit','J.R.R. Tolkien');
 
insert into czytelnicy (pesel,imie,nazwisko) values ('001','Jan','Kowalski');
insert into czytelnicy (pesel,imie,nazwisko) values ('002','Adam','Nowak');
insert into czytelnicy (pesel,imie,nazwisko) values ('003','Alojzy','Buczkowski');
insert into czytelnicy (pesel,imie,nazwisko) values ('004','Witold','Iksiński');
insert into czytelnicy (pesel,imie,nazwisko) values ('005','Anna','Ygregkowska');
insert into czytelnicy (pesel,imie,nazwisko) values ('006','Julia','Capuletti');
insert into czytelnicy (pesel,imie,nazwisko) values ('007','James','Bond');
insert into czytelnicy (pesel,imie,nazwisko) values ('008','A','Spiderman');
insert into czytelnicy (pesel,imie,nazwisko) values ('009','B','Spiderwoman');
insert into czytelnicy (pesel,imie,nazwisko) values ('010','C','Superman');
 
insert into wypozyczenia (nr_ew,pesel,data_wypozyczenia,data_zwrotu) values 
    (1,'001','01-SEP-2007','15-SEP-2007');
insert into wypozyczenia (nr_ew,pesel,data_wypozyczenia,data_zwrotu) values 
    (1,'002','01-SEP-2006','15-SEP-2006');
insert into wypozyczenia (nr_ew,pesel,data_wypozyczenia,data_zwrotu) values 
    (1,'003','01-SEP-2004','15-SEP-2004');
insert into wypozyczenia (nr_ew,pesel,data_wypozyczenia,data_zwrotu) values 
    (1,'002','01-OCT-2007',NULL);
insert into wypozyczenia (nr_ew,pesel,data_wypozyczenia,data_zwrotu) values 
    (2,'005','01-SEP-2007','15-SEP-2007');
insert into wypozyczenia (nr_ew,pesel,data_wypozyczenia,data_zwrotu) values 
    (2,'006','04-OCT-2007',NULL);
insert into wypozyczenia (nr_ew,pesel,data_wypozyczenia,data_zwrotu) values 
    (2,'002',TO_DATE('2008-09-02','yyyy-mm-dd'),TO_DATE('2008-09-15','yyyy-mm-dd'));
insert into wypozyczenia (nr_ew,pesel,data_wypozyczenia,data_zwrotu) values 
    (3,'006',TO_DATE('2008-10-03','yyyy-mm-dd'),NULL);
insert into wypozyczenia (nr_ew,pesel,data_wypozyczenia,data_zwrotu) values 
    (3,'007',TO_DATE('2008-09-04','yyyy-mm-dd'),TO_DATE('2008-09-15','yyyy-mm-dd'));
insert into wypozyczenia (nr_ew,pesel,data_wypozyczenia,data_zwrotu) values 
    (3,'001',TO_DATE('2008-09-20','yyyy-mm-dd'),TO_DATE('2008-09-25','yyyy-mm-dd'));
insert into wypozyczenia (nr_ew,pesel,data_wypozyczenia,data_zwrotu) values 
    (3,'002',TO_DATE('2008-10-01','yyyy-mm-dd'),TO_DATE('2008-10-02','yyyy-mm-dd'));
insert into wypozyczenia (nr_ew,pesel,data_wypozyczenia,data_zwrotu) values 
    (4,'008',TO_DATE('2008-10-02','yyyy-mm-dd'),NULL);
insert into wypozyczenia (nr_ew,pesel,data_wypozyczenia,data_zwrotu) values 
    (4,'005',TO_DATE('2008-09-07','yyyy-mm-dd'),TO_DATE('2008-09-12','yyyy-mm-dd'));
insert into wypozyczenia (nr_ew,pesel,data_wypozyczenia,data_zwrotu) values 
    (4,'006',TO_DATE('2008-09-02','yyyy-mm-dd'),TO_DATE('2008-09-05','yyyy-mm-dd'));
insert into wypozyczenia (nr_ew,pesel,data_wypozyczenia,data_zwrotu) values 
    (5,'006',TO_DATE('2008-09-05','yyyy-mm-dd'),TO_DATE('2008-09-10','yyyy-mm-dd'));
insert into wypozyczenia (nr_ew,pesel,data_wypozyczenia,data_zwrotu) values 
    (5,'002',TO_DATE('2008-09-19 12:30','yyyy-mm-dd hh24:mi'),TO_DATE('2008-09-20','yyyy-mm-dd'));
insert into wypozyczenia (nr_ew,pesel,data_wypozyczenia,data_zwrotu) values 
    (9,'005',TO_DATE('2008-09-07','yyyy-mm-dd'),NULL);
insert into wypozyczenia (nr_ew,pesel,data_wypozyczenia,data_zwrotu) values 
    (9,'006',TO_DATE('2008-09-02 12:30','yyyy-mm-dd hh24:mi'),TO_DATE('2008-09-10 14:56','yyyy-mm-dd hh24:mi'));
insert into wypozyczenia (nr_ew,pesel,data_wypozyczenia,data_zwrotu) values 
    (7,'006',TO_DATE('2008-09-09','yyyy-mm-dd'),TO_DATE('2008-09-12','yyyy-mm-dd'));
insert into wypozyczenia (nr_ew,pesel,data_wypozyczenia,data_zwrotu) values 
    (7,'002',TO_DATE('2008-09-16 17:30','yyyy-mm-dd hh24:mi'),TO_DATE('2008-09-24','yyyy-mm-dd'));

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) 
  >= 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 > 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);