DROP TABLE opiekun_zwierzecia;
DROP TABLE opiekunowie;
DROP TABLE zwierzeta;
DROP TABLE klatki;
DROP TABLE gatunki;
CREATE TABLE gatunki
(
    id NUMBER(6) PRIMARY KEY,
    nazwa VARCHAR2(20)
);
INSERT INTO gatunki VALUES(1, 'Ptaki');
INSERT INTO gatunki VALUES(2, 'Ssaki');
INSERT INTO gatunki VALUES(3, 'Ryby');
CREATE TABLE klatki
(
    id NUMBER(6) PRIMARY KEY,
    nazwa VARCHAR2(20),
    powierzchnia NUMBER(3)
);
INSERT INTO klatki VALUES(1, 'Nowa1', 20);
INSERT INTO klatki VALUES(2, 'Nowa2', 30);
INSERT INTO klatki VALUES(3, 'Nowa3', 40);
CREATE TABLE zwierzeta
(
    id NUMBER(6) PRIMARY KEY,
    nazwa VARCHAR2(20),
    data_urodzenia DATE,
    waga NUMBER(5), -- POWINNO być w nawiasie 3 ale jest to błąd wykładowcy poniewaz wtedy nie można wykonać zadania 4!
    stan VARCHAR2(20),
    id_gatunek NUMBER(6) REFERENCES gatunki(id),
    id_klatki NUMBER(6) REFERENCES klatki(id)
);
INSERT INTO zwierzeta VALUES(1, 'Krowa', TO_DATE('2017/09/02', 'yyyy/mm/dd'), 30, 'Zdrowa', 2, 1);
INSERT INTO zwierzeta VALUES(2, 'Szczupak', TO_DATE('2017/03/02', 'yyyy/mm/dd'), 1300, 'Zdrowa', 3, 2);
INSERT INTO zwierzeta VALUES(3, 'Andrzej', TO_DATE('2017/02/03', 'yyyy/mm/dd'), 1, 'Zdrowa', 1, 3);
INSERT INTO zwierzeta VALUES(4, 'Krowa', TO_DATE('2017/09/02', 'yyyy/mm/dd'), 3000, 'Zdrowa', 2, 1);
INSERT INTO zwierzeta VALUES(5, 'Krowa', TO_DATE('2017/09/02', 'yyyy/mm/dd'), 30, 'Zdrowa', 2, 1);
INSERT INTO zwierzeta VALUES(6, 'Krowa', TO_DATE('2017/09/02', 'yyyy/mm/dd'), 30, 'Zdrowa', 2, 1);
CREATE TABLE opiekunowie
(
    id NUMBER(6) PRIMARY KEY,
    imie VARCHAR2(20),
    nazwisko VARCHAR2(20),
    pensja NUMBER(6)
);
INSERT INTO opiekunowie VALUES(1, 'Darek', 'Babiarz', 2000);
INSERT INTO opiekunowie VALUES(2, 'Darek2', 'Babiarz2', 1000);
INSERT INTO opiekunowie VALUES(3, 'Darek3', 'Babiarz3', 3000);
CREATE TABLE opiekun_zwierzecia
(
    id NUMBER(6) PRIMARY KEY,
    id_zwierze NUMBER(6) REFERENCES zwierzeta(id),
    id_opiekun NUMBER(6) REFERENCES opiekunowie(id),
    poczatek_opieki DATE
);
INSERT INTO opiekun_zwierzecia VALUES(1, 1, 1, TO_DATE('2017/02/03', 'yyyy/mm/dd'));
INSERT INTO opiekun_zwierzecia VALUES(2, 2, 2, TO_DATE('2013/02/03', 'yyyy/mm/dd'));
--Zadanie 1
/*
Instrukcje wyswietlajaca imie, nazwisko i date urodzenia najstarszego zwierzecia
*/
SELECT * FROM (SELECT nazwa, data_urodzenia FROM zwierzeta ORDER BY data_urodzenia) WHERE ROWNUM = 1;



/*
Wyswietlanie nastepujacych informacji o opiekunach zwierzat (nazwa zwierzecia,
nazwa gatunku, imie i nazwisko opiekuna data urodzenia dla zwierzat
majcych co najmniej 3 miesiace
*/

SELECT zwierzeta.nazwa, gatunki.nazwa, opiekunowie.imie, opiekunowie.nazwisko, zwierzeta.data_urodzenia
FROM zwierzeta, gatunki, opiekunowie, opiekun_zwierzecia
WHERE zwierzeta.id_gatunek = gatunki.id AND opiekun_zwierzecia.id_zwierze = zwierzeta.id AND opiekunowie.id = opiekun_zwierzecia.id_opiekun
AND ADD_MONTHS(SYSDATE, -3)>= zwierzeta.data_urodzenia;

/*
instrukcje ktora wyswietli informacje o klatce nazwa powierzchnia
oraz liczbie zwierzat ktore sie w niej znajduja
jesli liczba ta jest wieksza od 3
*/

SELECT klatki.nazwa, klatki.powierzchnia, COUNT(zwierzeta.id) AS ilosc
FROM klatki, zwierzeta
WHERE zwierzeta.id_klatki = klatki.id
GROUP BY klatki.nazwa, klatki.powierzchnia
HAVING COUNT(zwierzeta.id)>3;
/*
funkcje wyliczajaca ile procent stanowia ziwerzeta ktore waza powyzej 1
tony w stosunku do wszystkich zwierzat
*/
CREATE OR REPLACE FUNCTION tony
RETURN NUMBER IS
ile_wszystkich  NUMBER;
ile_pustych NUMBER;
BEGIN
    ile_wszystkich := 0;
    ile_pustych :=0;
    SELECT COUNT(zwierzeta.id) INTO ile_wszystkich FROM zwierzeta;
    SELECT COUNT(zwierzeta.id) INTO ile_pustych FROM zwierzeta WHERE zwierzeta.waga > 1000;
    RETURN ile_pustych/ile_wszystkich*100;
END;
/
SELECT tony FROM dual;