DROP TABLE wypozyczenie;
DROP TABLE czytelnicy;
DROP TABLE ksiazka;
DROP TABLE autor;
DROP TABLE wydawnictwo;

CREATE TABLE wydawnictwo
(
  id_wydawnictwo NUMBER(10) CONSTRAINT wyd_pk PRIMARY KEY,
  nazwa VARCHAR2(20)
);
CREATE TABLE autor
(
  id_autor NUMBER(6) CONSTRAINT aut_pk PRIMARY KEY,
  nazwisko VARCHAR2(20),
  imie VARCHAR2(20)
);
CREATE TABLE ksiazka
(
  id_ksiazka NUMBER(6) PRIMARY KEY,
  tytul VARCHAR(30),
  id_autor NUMBER(6) REFERENCES autor(id_autor),
  id_wydawnictwo NUMBER(10) REFERENCES wydawnictwo(id_wydawnictwo),
  stan VARCHAR2(20)
);
CREATE TABLE czytelnicy
(
  id_czytelnik NUMBER(10) PRIMARY KEY,
  nazwisko VARCHAR2(30),
  imie VARCHAR2(30),
  data_urodzenia DATE
);
CREATE TABLE wypozyczenie
(
  id_wypozyczenie NUMBER(10) CONSTRAINT wyp_pk PRIMARY KEY,
  id_czytelnik NUMBER(10) REFERENCES czytelnicy(id_czytelnik),
  id_ksiazka NUMBER(6) REFERENCES ksiazka(id_ksiazka),
  data_wypozyczenia DATE DEFAULT(SYSDATE),
  data_zwrotu DATE DEFAULT(NULL)
);

INSERT INTO czytelnicy VALUES (1,'Haraszo','Andrzej',TO_DATE('2003/07/09', 'yyyy/mm/dd'));
INSERT INTO czytelnicy VALUES (2,'Haraszoo','Andrzejj',TO_DATE('2001/07/09', 'yyyy/mm/dd'));
INSERT INTO czytelnicy VALUES (3,'Datek','matek',TO_DATE('1927/07/09', 'yyyy/mm/dd'));
INSERT INTO wydawnictwo VALUES(1, 'Nowa Era');
INSERT INTO wydawnictwo VALUES(2, 'Oxford');
INSERT INTO autor VALUES(1, 'Andrzej', 'Rak');
INSERT INTO autor VALUES(2, 'Paweł', 'Dul');
INSERT INTO autor VALUES(3, 'Paweł3', 'Dul3');
INSERT INTO autor VALUES(4, 'Paweł4', 'Dul4');
INSERT INTO ksiazka VALUES(1, 'W dupie', 1, 2, 'nowa' );
INSERT INTO ksiazka VALUES(3, 'W dupie2', 1, 2, 'nowa2' );
INSERT INTO ksiazka VALUES(4, 'W dupie3', 1, 2, 'nowa3' );
INSERT INTO ksiazka VALUES(5, 'W dupie4', 1, 2, 'nowa4' );
INSERT INTO ksiazka VALUES(2, 'Gdzies', 2, 1, 'uzywana' );
INSERT INTO ksiazka VALUES(6, 'W dupie44', 1, 2, 'NIEZNANY' );
INSERT INTO wypozyczenie VALUES(1, 1, 1,TO_DATE('2017/02/03','yyyy/mm/dd') ,TO_DATE('2017/02/07','yyyy/mm/dd') );
INSERT INTO wypozyczenie VALUES(2,2,2,TO_DATE('2017/02/04','yyyy/mm/dd'),TO_DATE('2017/02/13','yyyy/mm/dd'));
INSERT INTO wypozyczenie VALUES(3,2,2,TO_DATE('2017/02/04','yyyy/mm/dd'),NULL);
INSERT INTO wypozyczenie VALUES(4,2,2,TO_DATE('2017/02/04','yyyy/mm/dd'),NULL);

--zadanie 1
SELECT * FROM (SELECT * FROM czytelnicy ORDER BY data_urodzenia ) WHERE ROWNUM = 1;
--zadanie 2
/*
Instrukcja wyświetlająca nastepujace informacje o wypozyczeniach (tytuł ksiązki
imie nazwisko autora, imie i nazwisko czytelnika oraz date wypozczenia
dla ksiaze wypozyczonych co najmniej 3 razy w miesiacu*/

SELECT ksiazka.tytul, autor.imie, autor.nazwisko, czytelnicy.imie, czytelnicy.nazwisko, wypozyczenie.data_wypozyczenia
FROM ksiazka, autor, czytelnicy, wypozyczenie WHERE wypozyczenie.id_czytelnik = czytelnicy.id_czytelnik AND
wypozyczenie.id_ksiazka = ksiazka.id_ksiazka AND ksiazka.id_autor = autor.id_autor;
--dorobic 3 razy w miesiacy

--Zadanie 3
/*
Istrukcja ktora wyswietli informacje o autorze (imie, nazwisko) oraz liczbie
napisanych przez niego ksiazek jesli byla wieksza niz 3
*/
SELECT autor.imie, autor.nazwisko, COUNT(ksiazka.id_ksiazka) AS Ilosc FROM ksiazka, autor WHERE ksiazka.id_autor = autor.id_autor GROUP BY autor.id_autor, autor.imie, autor.nazwisko
HAVING COUNT(ksiazka.id_ksiazka)>3;
--Zadanie 4
/*
Funkcje wyliczajaca ile procent stanowia wypozyczenia ktore nie maja podanej
daty zwrotu w sotsunku do wszystkich wypozyczen
*/

CREATE OR REPLACE FUNCTION zwroty
RETURN NUMBER IS
ile_wszystkich  NUMBER;
ile_pustych NUMBER;
BEGIN
    ile_wszystkich := 0;
    ile_pustych :=0;
    SELECT COUNT(wypozyczenie.id_wypozyczenie) INTO ile_wszystkich FROM wypozyczenie;
    SELECT COUNT(wypozyczenie.id_wypozyczenie) INTO ile_pustych FROM wypozyczenie WHERE data_zwrotu IS NULL;
    RETURN (ile_pustych/ile_wszystkich)*100;
END;
/
SELECT zwroty FROM dual;

--Zadanie 5
/*
Procedure ktora sprawdzi wszystkie wypozyczenia jezeli data zwrotu jest wczesniejsza niz
data wypozyczenia to powinien zostac zgloszony wyjatek o nazwie WYJĄTEK jezeli natomiast
stan ksiazki jest ustawiony na NIEZNANY to nalezy wyswietlic komunikat
informujacy o tym
*/
INSERT INTO wypozyczenie VALUES(5,2,2, TO_DATE('2016\02\03','yyyy\mm\dd'), TO_DATE('2016\02\01','yyyy\mm\dd'));
INSERT INTO wypozyczenie VALUES(6,2,6, TO_DATE('2016\02\03','yyyy\mm\dd'), TO_DATE('2016\02\01','yyyy\mm\dd'));
CREATE OR REPLACE PROCEDURE sprawdz
IS
--DEKLARACJA ZMIENNYCH
    TYPE Te IS RECORD
        (wypozyczenie DATE,zwrot DATE,stan VARCHAR2(20));
    dane Te;
    CURSOR spr IS
    SELECT wypozyczenie.data_wypozyczenia, wypozyczenie.data_zwrotu, ksiazka.stan
        FROM wypozyczenie, ksiazka WHERE wypozyczenie.id_ksiazka = ksiazka.id_ksiazka;
BEGIN
    OPEN spr;
    LOOP
        FETCH spr INTO dane;
        EXIT WHEN spr%NOTFOUND;
        IF dane.wypozyczenie > dane.zwrot THEN
            DBMS_OUTPUT.put_line('Jest'); -- na kolosie powinno byc: RAISE WYJĄTEK;
        ELSE --dla celów nauki nie miałem wyjątku stworzonego więc zrobiłem na dbms
            DBMS_OUTPUT.put_line('Brak');
        END IF;
        IF dane.stan = 'NIEZNANY' THEN
            DBMS_OUTPUT.put_line('Nieznany stan książki UWAGA!');
        END IF;
    END LOOP;
    CLOSE spr;
END;
/
EXEC sprawdz;