Laboratorium 1: podstawy 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)
);

delete from wypozyczenia;
delete from ksiazki;
delete from czytelnicy;
 
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 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','Lidia','Montecchi');
 
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('2007-09-02','yyyy-mm-dd'),TO_DATE('2007-09-15','yyyy-mm-dd'));
insert into wypozyczenia (nr_ew,pesel,data_wypozyczenia,data_zwrotu) values 
    (3,'006',TO_DATE('2007-10-03','yyyy-mm-dd'),NULL);
insert into wypozyczenia (nr_ew,pesel,data_wypozyczenia,data_zwrotu) values 
    (3,'007',TO_DATE('2007-09-04','yyyy-mm-dd'),TO_DATE('2007-09-15','yyyy-mm-dd'));
insert into wypozyczenia (nr_ew,pesel,data_wypozyczenia,data_zwrotu) values 
    (3,'001',TO_DATE('2007-09-20','yyyy-mm-dd'),TO_DATE('2007-09-25','yyyy-mm-dd'));
insert into wypozyczenia (nr_ew,pesel,data_wypozyczenia,data_zwrotu) values 
    (3,'002',TO_DATE('2007-10-01','yyyy-mm-dd'),TO_DATE('2007-10-02','yyyy-mm-dd'));
insert into wypozyczenia (nr_ew,pesel,data_wypozyczenia,data_zwrotu) values 
    (4,'008',TO_DATE('2007-10-02','yyyy-mm-dd'),NULL);
insert into wypozyczenia (nr_ew,pesel,data_wypozyczenia,data_zwrotu) values 
    (4,'005',TO_DATE('2007-09-07','yyyy-mm-dd'),TO_DATE('2007-09-12','yyyy-mm-dd'));
insert into wypozyczenia (nr_ew,pesel,data_wypozyczenia,data_zwrotu) values 
    (4,'006',TO_DATE('2007-09-02','yyyy-mm-dd'),TO_DATE('2007-09-05','yyyy-mm-dd'));
insert into wypozyczenia (nr_ew,pesel,data_wypozyczenia,data_zwrotu) values 
    (5,'006',TO_DATE('2007-09-05','yyyy-mm-dd'),TO_DATE('2007-09-10','yyyy-mm-dd'));
insert into wypozyczenia (nr_ew,pesel,data_wypozyczenia,data_zwrotu) values 
    (5,'002',TO_DATE('2007-09-19 12:30','yyyy-mm-dd hh24:mi'),TO_DATE('2007-09-20','yyyy-mm-dd'));

set pagesize 100;
set trimspool on;
set trimout on;
set echo on; -- wyświetlanie poleceń SQL
set linesize 100;

column tytul format a30;
column autor format a20;

SELECT nr_ew,tytul FROM ksiazki;

SELECT * FROM ksiazki WHERE autor LIKE 'J. Dziam%';

SELECT k.tytul,count(*) as ile
  FROM wypozyczenia w,ksiazki k 
 WHERE w.nr_ew=k.nr_ew
 GROUP BY w.nr_ew,k.tytul
 ORDER BY ile DESC;

SELECT TO_CHAR(w.data_wypozyczenia,'yyyy-mm-dd hh24:mi') as data_wypozyczenia,w.nr_ew,k.tytul,c.imie||' '||c.nazwisko as wypozyczajacy 
  FROM wypozyczenia w,ksiazki k,czytelnicy c
 WHERE w.nr_ew=k.nr_ew AND w.pesel=c.pesel
   AND w.data_zwrotu IS NULL
 ORDER BY w.data_wypozyczenia ASC;

set autotrace on;
select k.tytul,w.pesel,w.data_wypozyczenia,w.data_zwrotu from wypozyczenia w,ksiazki k WHERE w.nr_ew=k.nr_ew;
set autotrace off;