CREATE TABLE Wrogowie (
imie VARCHAR(15) CONSTRAINT i_pk PRIMARY KEY,
gatunek VARCHAR(15)
);

CREATE TABLE Lapowki (
lapowka VARCHAR2(40) CONSTRAINT l_pk PRIMARY KEY
);


CREATE TABLE Funkcje (
funkcja VARCHAR2(15) CONSTRAINT f_pk PRIMARY KEY,
min_myszy NUMBER(3),
max_myszy NUMBER(3)
);


CREATE TABLE Bandy
(nazwa VARCHAR2(15) CONSTRAINT nazw NOT NULL,
numer_b NUMBER(3) CONSTRAINT nr_b_pk PRIMARY KEY,
teren VARCHAR2(15) CONSTRAINT ter NOT NULL,
);

CREATE TABLE Koty
(pseudo VARCHAR2(15) CONSTRAINT koty_pk PRIMARY KEY,
data_dolaczenia DATE CONSTRAINT kot_w_rodz NOT NULL,
plec CHAR(1) CONSTRAINT pl_nn NOT NULL CONSTRAINT pl_mk CHECK (plec IN ('K','M')),
przydzial_m NUMBER(3) CONSTRAINT prz_m CHECK (przydzial_m>-1),
pseudo_szefa VARCHAR2(15), CONSTRAINT ps_fk FOREIGN KEY(pseudo) REFERENCES Koty(pseudo),
funkcja VARCHAR2(15), CONSTRAINT fun FOREIGN KEY(funkcja) REFERENCES Funkcje(funkcja)
);

ALTER TABLE Bandy ADD pseudo_szefa VARCHAR2(15), CONSTRAINT ps_sz FOREIGN KEY(pseudo_szefa) REFERENCES Koty(pseudo);


ALTER TABLE Bandy


CREATE TABLE Myszy (
numer_m NUMBER(6) CONSTRAINT numm_m PRIMARY KEY CONSTRAINT wiecej CHECK(numer_m>0),
data_u DATE CONSTRAINT dlu NOT NULL,
data_w DATE,
waga NUMBER(3) CONSTRAINT w_nn NOT NULL CONSTRAINT w_t CHECK (waga>10),
dlugosc NUMBER(3) CONSTRAINT d_nn NOT NULL CONSTRAINT d_t CHECK (dlugosc>5),
upolowana_przez VARCHAR2(15) CONSTRAINT u_nn NOT NULL, CONSTRAINT ps_kj FOREIGN KEY(upolowana_przez) REFERENCES Koty(pseudo),
zjedzona_przez VARCHAR2(15), CONSTRAINT pjs_j FOREIGN KEY(zjedzona_przez) REFERENCES Koty(pseudo),
CONSTRAINT d_w_cph CHECK (data_w>=data_u)
);


CREATE TABLE Incydenty (
data_i DATE CONSTRAINT dnn NOT NULL,
opis_i VARCHAR2(60) CONSTRAINT onn NOT NULL,
pseudo VARCHAR2(15), CONSTRAINT ps_fik FOREIGN KEY(pseudo) REFERENCES Koty(pseudo),
imie VARCHAR2(15), CONSTRAINT iow_fk FOREIGN KEY(imie) REFERENCES Wrogowie(imie),
CONSTRAINT daon_pk PRIMARY KEY(imie, pseudo)
);



CREATE TABLE Walczacy (
pseudo VARCHAR2(15), CONSTRAINT hps_fk FOREIGN KEY(pseudo) REFERENCES Koty(pseudo),
imie VARCHAR2(15), CONSTRAINT ihm_fk FOREIGN KEY(imie) REFERENCES Wrogowie(imie),
stopien_wrogosci NUMBER(2) CONSTRAINT w_pz CHECK ((stopien_wrogosci<11) AND (stopien_wrogosci>0))
);

CREATE TABLE Lapowki_wrogow (
lapowka VARCHAR2(40) CONSTRAINT l_nn NOT NULL, CONSTRAINT lw FOREIGN KEY(lapowka) REFERENCES Lapowki(lapowka),
imie VARCHAR2(15) CONSTRAINT i_nn NOT NULL, CONSTRAINT iw FOREIGN KEY(imie) REFERENCES Wrogowie(imie),
CONSTRAINT l_pk PRIMARY KEY(lapowka, imie)
);


------------------------------------------------------------------------- ZAD2

ALTER TABLE Bandy
DISABLE CONSTRAINT ps_sz;

INSERT INTO Koty(pseudo,data_dolaczenia,plec,przydzial_m,pseudo_szefa,numer_b,funkcja) VALUES('Mistrzunio','23.01.2004','M','25',NULL,NULL,'szef szefow');
INSERT INTO Koty(pseudo,data_dolaczenia,plec,przydzial_m,pseudo_szefa,numer_b,funkcja) VALUES('Cheetah','24.02.2005','M','19','Mistrzunio','1','drapieznik');
INSERT INTO Koty(pseudo,data_dolaczenia,plec,przydzial_m,pseudo_szefa,numer_b,funkcja) VALUES('Harnold','03.05.2004','M','17','Mistrzunio','1','czarodziej');
INSERT INTO Koty(pseudo,data_dolaczenia,plec,przydzial_m,pseudo_szefa,numer_b,funkcja) VALUES('Roslinozerca','16.03.2010','M','0','Mistrzunio','1','wege');
INSERT INTO Koty(pseudo,data_dolaczenia,plec,przydzial_m,pseudo_szefa,numer_b,funkcja) VALUES('Hermiona','15.03.2008','K','10','Cheetah','2','czarodziejka');
INSERT INTO Koty(pseudo,data_dolaczenia,plec,przydzial_m,pseudo_szefa,numer_b,funkcja) VALUES('Vladimir','24.03.2006','M','6','Cheetah','2','rozrywka');
INSERT INTO Koty(pseudo,data_dolaczenia,plec,przydzial_m,pseudo_szefa,numer_b,funkcja) VALUES('Gazownik','30.04.2009','M','6','Cheetah','2','gazujacy');
INSERT INTO Koty(pseudo,data_dolaczenia,plec,przydzial_m,pseudo_szefa,numer_b,funkcja) VALUES('Aleksandra','18.07.2010','K','4','Cheetah','2','czarodziejka');
INSERT INTO Koty(pseudo,data_dolaczenia,plec,przydzial_m,pseudo_szefa,numer_b,funkcja) VALUES('Karol','28.11.2008','M','7','Harnold','3','rozrywka');
INSERT INTO Koty(pseudo,data_dolaczenia,plec,przydzial_m,pseudo_szefa,numer_b,funkcja) VALUES('Hitman','15.12.2009','M','30','Harnold','3','zabijaka');
INSERT INTO Koty(pseudo,data_dolaczenia,plec,przydzial_m,pseudo_szefa,numer_b,funkcja) VALUES('Maly Cpun','21.08.2007','M','5','Harnold','3','rozrywka');
INSERT INTO Koty(pseudo,data_dolaczenia,plec,przydzial_m,pseudo_szefa,numer_b,funkcja) VALUES('Michalowa','12.05.2008','K','6','Roslinozerca','4','rozrywka');
INSERT INTO Koty(pseudo,data_dolaczenia,plec,przydzial_m,pseudo_szefa,numer_b,funkcja) VALUES('Silny','09.12.2012','M','7','Roslinozerca','4','zabijaka');
INSERT INTO Koty(pseudo,data_dolaczenia,plec,przydzial_m,pseudo_szefa,numer_b,funkcja) VALUES('Mieczak','11.02.2013','M','4','Roslinozerca','4','listonosz');
INSERT INTO Koty(pseudo,data_dolaczenia,plec,przydzial_m,pseudo_szefa,numer_b,funkcja) VALUES('Rudy','15.04.2010','M','4','Roslinozerca','4','rudy');


INSERT INTO Funkcje(funkcja,min_myszy,max_myszy) VALUES('szef szefow','20','30');
INSERT INTO Funkcje(funkcja,min_myszy,max_myszy) VALUES('drapieznik','15','20');
INSERT INTO Funkcje(funkcja,min_myszy,max_myszy) VALUES('czarodziej','14','21');
INSERT INTO Funkcje(funkcja,min_myszy,max_myszy) VALUES('wege','0','2');
INSERT INTO Funkcje(funkcja,min_myszy,max_myszy) VALUES('czarodziejka','7','18');
INSERT INTO Funkcje(funkcja,min_myszy,max_myszy) VALUES('rozrywka','5','9');
INSERT INTO Funkcje(funkcja,min_myszy,max_myszy) VALUES('zabijaka','10','50');
INSERT INTO Funkcje(funkcja,min_myszy,max_myszy) VALUES('gazujacy','3','8');
INSERT INTO Funkcje(funkcja,min_myszy,max_myszy) VALUES('listonosz','3','5');
INSERT INTO Funkcje(funkcja,min_myszy,max_myszy) VALUES('rudy','2','10');




INSERT INTO Bandy(nazwa,numer_b,teren,pseudo_szefa) VALUES('Naczelne Koty','1','Centrum','Mistrzunio');
INSERT INTO Bandy(nazwa,numer_b,teren,pseudo_szefa) VALUES('Lamparty','2','Manhattan','Cheetah');
INSERT INTO Bandy(nazwa,numer_b,teren,pseudo_szefa) VALUES('Czarodzieje','3','Podziemia','Harnold');
INSERT INTO Bandy(nazwa,numer_b,teren,pseudo_szefa) VALUES('Wege Koty','4','Ogrod','Roslinozerca');
INSERT INTO Bandy(nazwa,numer_b,teren,pseudo_szefa) VALUES('Jaskiniowcy','5','Jaskinie',NULL);
INSERT INTO Bandy(nazwa,numer_b,teren,pseudo_szefa) VALUES('Drogowka','6','Autostrady',NULL);

ALTER TABLE Bandy;
ENABLE CONSTRAINT ps_sz;

INSERT INTO Myszy(numer_m,data_u,data_w,waga,dlugosc,upolowana_przez,zjedzona_przez) VALUES ('756','21.07.2016','22.07.2016','53','12','Vladimir','Vladimir');
INSERT INTO Myszy(numer_m,data_u,data_w,waga,dlugosc,upolowana_przez,zjedzona_przez) VALUES ('757','22.03.2016','24.03.2016','42','9','Hermiona','Cheetah');
INSERT INTO Myszy(numer_m,data_u,data_w,waga,dlugosc,upolowana_przez,zjedzona_przez) VALUES ('766','20.03.2016','21.03.2016','33','13','Karol','Hitman');
INSERT INTO Myszy(numer_m,data_u,data_w,waga,dlugosc,upolowana_przez,zjedzona_przez) VALUES ('845','21.03.2016','22.03.2016','29','9','Roslinozerca','Roslinozerca');
INSERT INTO Myszy(numer_m,data_u,data_w,waga,dlugosc,upolowana_przez,zjedzona_przez) VALUES ('846','10.05.2016','12.05.2016','32','10','Gazownik','Cheetah');
INSERT INTO Myszy(numer_m,data_u,data_w,waga,dlugosc,upolowana_przez,zjedzona_przez) VALUES ('847','11.05.2016','13.05.2016','46','15','Maly Cpun','Harnold');
INSERT INTO Myszy(numer_m,data_u,data_w,waga,dlugosc,upolowana_przez,zjedzona_przez) VALUES ('893','13.02.2016','14.02.2016','51','17','Rudy','Roslinozerca');
INSERT INTO Myszy(numer_m,data_u,data_w,waga,dlugosc,upolowana_przez,zjedzona_przez) VALUES ('894','13.08.2016','15.08.2016','38','11','Mieczak','Silny');
INSERT INTO Myszy(numer_m,data_u,data_w,waga,dlugosc,upolowana_przez,zjedzona_przez) VALUES ('895','13.10.2016','16.10.2016','41','9','Silny','Michalowa');
INSERT INTO Myszy(numer_m,data_u,data_w,waga,dlugosc,upolowana_przez,zjedzona_przez) VALUES ('234','15.12.2016','17.12.2016','48','12','Michalowa','Michalowa');
INSERT INTO Myszy(numer_m,data_u,data_w,waga,dlugosc,upolowana_przez,zjedzona_przez) VALUES ('235','16.06.2016','18.06.2016','37','11','Mistrzunio','Mistrzunio');
INSERT INTO Myszy(numer_m,data_u,data_w,waga,dlugosc,upolowana_przez,zjedzona_przez) VALUES ('236','04.05.2016','07.05.2016','32','10','Aleksandra','Hitman');
INSERT INTO Myszy(numer_m,data_u,data_w,waga,dlugosc,upolowana_przez,zjedzona_przez) VALUES ('237','04.05.2016','05.05.2016','41','12','Mieczak','Rudy');
INSERT INTO Myszy(numer_m,data_u,data_w,waga,dlugosc,upolowana_przez,zjedzona_przez) VALUES ('523','05.05.2016','06.05.2016','45','11','Rudy','Rudy');
INSERT INTO Myszy(numer_m,data_u,data_w,waga,dlugosc,upolowana_przez,zjedzona_przez) VALUES ('524','05.05.2016','08.05.2016','25','7','Michalowa','Harnold');
INSERT INTO Myszy(numer_m,data_u,data_w,waga,dlugosc,upolowana_przez,zjedzona_przez) VALUES ('525','07.05.2016','08.05.2016','35','11','Gazownik','Gazownik');



INSERT INTO Incydenty(data_i,opis_i,pseudo,imie) VALUES('21.07.2015','Hermiona zrzucila krzeslo na Bonifacego','Hermiona','Bonifacy');
INSERT INTO Incydenty(data_i,opis_i,pseudo,imie) VALUES('05.04.2016','Walka w tunelu','Hitman','Gruby');
INSERT INTO Incydenty(data_i,opis_i,pseudo,imie) VALUES('15.06.2016','Wpadka w siec pajaka','Mieczak','Pajeczak');
INSERT INTO Incydenty(data_i,opis_i,pseudo,imie) VALUES('03.07.2017','Starcie na Manhattanie','Harnold','Kruci');
INSERT INTO Incydenty(data_i,opis_i,pseudo,imie) VALUES('06.03.2017','Wypadek samochodowy','Rudy','Michal');
INSERT INTO Incydenty(data_i,opis_i,pseudo,imie) VALUES('09.11.2016','Przejecie parku centralnego','Hitman','Gruby');
INSERT INTO Incydenty(data_i,opis_i,pseudo,imie) VALUES('14.10.2017','Wywrotka na rolkach','Hermiona','Kawalarz');
INSERT INTO Incydenty(data_i,opis_i,pseudo,imie) VALUES('13.08.2017','Zakup narkotykow','Maly Cpun','Marian');
INSERT INTO Incydenty(data_i,opis_i,pseudo,imie) VALUES('12.12.2017','Napad na bank','Mistrzunio','Pirat');
INSERT INTO Incydenty(data_i,opis_i,pseudo,imie) VALUES('09.07.2016','Obrabienie sklepu z karmy','Gazownik','Mateusz');
INSERT INTO Incydenty(data_i,opis_i,pseudo,imie) VALUES('25.10.2010','Utopienie Jakuba','Harnold','Jakub');



INSERT INTO Wrogowie(imie,gatunek) VALUES('Bonifacy','Kocisko');
INSERT INTO Wrogowie(imie,gatunek) VALUES('Jakub','Nurek');
INSERT INTO Wrogowie(imie,gatunek) VALUES('Michal','Szczurolap');
INSERT INTO Wrogowie(imie,gatunek) VALUES('Gruby','Kocisko');
INSERT INTO Wrogowie(imie,gatunek) VALUES('Kawalarz','Jezdziec');
INSERT INTO Wrogowie(imie,gatunek) VALUES('Pirat','Jezdziec');
INSERT INTO Wrogowie(imie,gatunek) VALUES('Pajeczak','Pajak');
INSERT INTO Wrogowie(imie,gatunek) VALUES('Marian','Szczurolap');
INSERT INTO Wrogowie(imie,gatunek) VALUES('Kruci','Szczur');
INSERT INTO Wrogowie(imie,gatunek) VALUES('Mateusz','Zlodziej');
INSERT INTO Wrogowie(imie,gatunek) VALUES('Marcin','Zlodziej');



INSERT INTO Walczacy(pseudo,imie,stopien_wrogosci) VALUES('Hermiona','Bonifacy','9');
INSERT INTO Walczacy(pseudo,imie,stopien_wrogosci) VALUES('Harnold','Jakub','4');
INSERT INTO Walczacy(pseudo,imie,stopien_wrogosci) VALUES('Harnold','Michal','5');
INSERT INTO Walczacy(pseudo,imie,stopien_wrogosci) VALUES('Mistrzunio','Pajeczak','10');
INSERT INTO Walczacy(pseudo,imie,stopien_wrogosci) VALUES('Silny','Mateusz','6');
INSERT INTO Walczacy(pseudo,imie,stopien_wrogosci) VALUES('Gazownik','Kawalarz','7');
INSERT INTO Walczacy(pseudo,imie,stopien_wrogosci) VALUES('Cheetah','Michal','3');
INSERT INTO Walczacy(pseudo,imie,stopien_wrogosci) VALUES('Cheetah','Marian','5');
INSERT INTO Walczacy(pseudo,imie,stopien_wrogosci) VALUES('Vladimir','Kruci','8');
INSERT INTO Walczacy(pseudo,imie,stopien_wrogosci) VALUES('Karol','Pirat','2');
INSERT INTO Walczacy(pseudo,imie,stopien_wrogosci) VALUES('Hitman','Gruby','8');
INSERT INTO Walczacy(pseudo,imie,stopien_wrogosci) VALUES('Hitman','Jakub','3');
INSERT INTO Walczacy(pseudo,imie,stopien_wrogosci) VALUES('Maly Cpun','Marcin', '5');
INSERT INTO Walczacy(pseudo,imie,stopien_wrogosci) VALUES('Rudy','Marcin', '3');



INSERT INTO Lapowki(lapowka) VALUES('kokaina');
INSERT INTO Lapowki(lapowka) VALUES('whiskas');
INSERT INTO Lapowki(lapowka) VALUES('mleko');
INSERT INTO Lapowki(lapowka) VALUES('amulet');
INSERT INTO Lapowki(lapowka) VALUES('mango');
INSERT INTO Lapowki(lapowka) VALUES('pozytywka');
INSERT INTO Lapowki(lapowka) VALUES('karma');
INSERT INTO Lapowki(lapowka) VALUES('ser edamski');
INSERT INTO Lapowki(lapowka) VALUES('pierscien');
INSERT INTO Lapowki(lapowka) VALUES('kurtka');
INSERT INTO Lapowki(lapowka) VALUES('pistolet');



INSERT INTO Lapowki_wrogow(lapowka,imie) VALUES('kokaina','Bonifacy');
INSERT INTO Lapowki_wrogow(lapowka,imie) VALUES('whiskas','Michal');
INSERT INTO Lapowki_wrogow(lapowka,imie) VALUES('mleko','Marcin');
INSERT INTO Lapowki_wrogow(lapowka,imie) VALUES('mango','Marcin');
INSERT INTO Lapowki_wrogow(lapowka,imie) VALUES('amulet','Kawalarz');
INSERT INTO Lapowki_wrogow(lapowka,imie) VALUES('pozytywka','Mateusz');
INSERT INTO Lapowki_wrogow(lapowka,imie) VALUES('karma','Gruby');
INSERT INTO Lapowki_wrogow(lapowka,imie) VALUES('ser edamski','Marcin');
INSERT INTO Lapowki_wrogow(lapowka,imie) VALUES('pierscien','Michal');
INSERT INTO Lapowki_wrogow(lapowka,imie) VALUES('pistolet','Bonifacy');


---------------------------- ZAD 3

w każdej z grup 1-3 jedno zapytanie z operacją grupowania

-------------- zad 3.1 proste na jednej relacji


---1 Podaj wrogów i ich stopień wrogości o stopniu wrogości powyżej 5

SELECT imie, stopien_wrogosci
  FROM Walczacy
  WHERE stopien_wrogosci > 5

---2 Wyświetl Koty wraz z ich płcią, pseudem szefa oraz przydziałem myszy zaczynając od największego a kończąc na najmniejszym

SELECT pseudo, plec, przydzial_m, pseudo_szefa
FROM Koty
ORDER BY przydzial_m DESC

---3 Wyświetl incydenty w kolejności chronologicznej

SELECT data_i, opis_i
FROM Incydenty
ORDER BY data_i


-------------- zad 3.2 złączenia

---1 Podaj liczbę członków w każdej z band

SELECT COUNT(k.pseudo), b.nazwa
  FROM Koty k
  INNER JOIN Bandy b
   ON k.numer_b = b.numer_b
  GROUP BY b.nazwa;

---2 Podaj pseudo kocic, które upolowały myszy w drugiej połowie 2016 roku

SELECT k.pseudo
  FROM Koty k
  INNER JOIN Myszy m
   ON k.pseudo = m.upolowana_przez
  WHERE (m.data_u BETWEEN '01.07.2016' AND '31.12.2016') AND k.plec = 'K'

---3 Wyświetl 5 najcięższych myszy oraz ich numer

SELECT m.numer_m, m.waga
FROM Myszy my JOIN Myszy m
ON m.waga <= my.waga
GROUP BY m.waga, m.numer_m
HAVING COUNT(DISTINCT my.numer_m) < 6
ORDER BY m.waga DESC


-------------- zad 3.3 podzapytania

---1 Podaj incydenty które zdarzyły się kotom z bandy Harnolda

SELECT i.data_i, i.opis_i
  FROM Incydenty i
  WHERE i.pseudo
  IN (SELECT k.pseudo FROM Koty k WHERE k.pseudo_szefa = 'Harnold')

---2 Wyświetl imiona wrogów z gatunku 'Zlodziej' którzy dali łapówkę

SELECT wr.imie
  FROM Wrogowie wr
  WHERE wr.gatunek = 'Zlodziej'
  AND wr.imie IN ( SELECT l.imie FROM Lapowki_wrogow l);

---3 Wyświetl koty, które potrzebują zjeść mniej niż 6 myszy

SELECT k.pseudo
FROM Koty k
WHERE k.funkcja IN (
  SELECT f.funkcja
  FROM Funkcje f
  WHERE f.min_myszy < 6
GROUP BY f.min_myszy
);