CREATE TABLE  zoznam_nehnutelnosti
	(kod_nehn  number  not null,
	druh  varchar(15)  not null,
	lokalita  varchar(15)  not null,
	v_OV  varchar(3)  not null,
	rozloha_poz  varchar2(5)  not null,
	poc_izieb  number(1)  not null,
	kod_formy  varchar(2)  not null,
	datum_zmluvy date  not null,
	celk_cena_tis  decimal(6,2)  not null,  
	kod_prov  char(2)  not null,
	zlava  varchar(20)  not null,
	primary key (kod_nehn),
	foreign key  (kod_formy) references forma_ponuky (kod_formy),
	foreign key  (kod_prov) references provizie (kod_prov)
);


CREATE TABLE  majitelia
	(kod_nehn  number  not null,
	druh  varchar(15)  not null,
	meno  varchar(15)  not null,
	priezvisko  varchar(20)  not null,
	tel_cislo  varchar(15)  not null,
	doh_suma_tis decimal(6,2)  not null,
	datum_zmluvy date  not null,
	foreign key  (kod_nehn) references zoznam_nehnutelnosti(kod_nehn)
);


CREATE TABLE  celkova_cena
	(kod_nehn  number  not null,
	doh_suma_tis decimal(6,2)  not null,
	kod_prov  char(2)  not null,
	suma_prov_tis  decimal(6,2)  not null,
	celk_cena_tis  decimal(6,2)  not null,
	foreign key  (kod_nehn) references zoznam_nehnutelnosti(kod_nehn),
	foreign key  (kod_prov) references provizie(kod_prov)
);



CREATE TABLE  provizie
	(kod_prov  char(2)  not null,
	kod_druhu  char(2)  not null,
	kod_formy  varchar(2)  not null,
	provizia  number(3)  not null,
	akcia  number(3)  not null,
	horuca_akcia  number(3)  not null,
	primary key (kod_prov),
	foreign key  (kod_druhu) references druh_nehn(kod_druhu),
	foreign key  (kod_formy) references forma_ponuky(kod_formy)
);



CREATE TABLE  druh_nehn
	(kod_druhu  char(2)  not null,
	druh  varchar(15)  not null,	
	primary key (kod_druhu)
);



CREATE TABLE  forma_ponuky
	(kod_formy  varchar(2)  not null,
	forma varchar(15)  not null,
	primary key (kod_formy)
);


      
      
      
      DROP TABLE druh_nehn;
      DROP TABLE forma_ponuky;
      DROP TABLE provizie;
      DROP TABLE zoznam_nehnutelnosti;
      DROP TABLE majitelia;
      DROP TABLE celkova_cena;

INSERT INTO zoznam_nehnutelnosti
VALUES ('012','RD','Tr.Teplice','ano','6a','5','PD','1.6.05','1700,0','AA','horuca akcia');
INSERT INTO zoznam_nehnutelnosti
VALUES ('123','byt','TN-Zlatovce','ano','50m2','2','PD','15.8.05','900,0','BA','horuca akcia');
INSERT INTO zoznam_nehnutelnosti
VALUES ('333','byt','Tr.Teplice','nie','63m2','2','PD','1.2.06','1100,0','BA',' - ');
INSERT INTO zoznam_nehnutelnosti
VALUES ('345','stav.pozemok','Drietoma','ano','7a','0','PD','1.2.06','880,0','CA',' - ');
INSERT INTO zoznam_nehnutelnosti
VALUES ('355','RD','Soblahov','ano','5a','5','PD','15.2.06','2420,0','AA',' - ');
INSERT INTO zoznam_nehnutelnosti
VALUES ('136','RD','Tr.Teplice','ano','6a','6','PD','1.9.05','1645,0','AA','horuca akcia');
INSERT INTO zoznam_nehnutelnosti
VALUES ('376','RD','TN-centrum','ano','7a','6','PD','1.3.06','2530,0','AA',' - ');
INSERT INTO zoznam_nehnutelnosti
VALUES ('378','nebyt.priestory','TN-Zlatovce','ano','100m2','3','PN','1.3.06','60,0','DB',' - ');
INSERT INTO zoznam_nehnutelnosti
VALUES ('245','byt','TN-Sihot','nie','100m2','3','PD','15.11.05','1100,0','BA','akcia');
INSERT INTO zoznam_nehnutelnosti
VALUES ('183','stav.pozemok','Soblahov','ano','8a','0','PD','15.10.05','1460,0','CA','akcia');
INSERT INTO zoznam_nehnutelnosti
VALUES ('235','byt','Tr.Teplice','ano','83m2','3','PN','1.11.05','14,4','BB','akcia');
INSERT INTO zoznam_nehnutelnosti
VALUES ('400','nebyt.priestory','TN-centrum','ano','125m2','3','PN','15.3.06','90,0','DB',' - ');
INSERT INTO zoznam_nehnutelnosti
VALUES ('401','stav.pozemok','Tr.Teplice','ano','7a','0','PD','15.3.06','1000,0','CA',' - ');
INSERT INTO zoznam_nehnutelnosti
VALUES ('402','byt','TN-Juh','nie','50m2','2','PD','15.3.06','896,0','BA',' - ');
INSERT INTO zoznam_nehnutelnosti
VALUES ('403','byt','TN-Zlatovce','ano','30m2','1','PN','15.3.06','10,0','BB',' - ');
INSERT INTO zoznam_nehnutelnosti
VALUES ('404','byt','TN-Juh','ano','85m2','4','PD','15.3.06','1570,0','BA',' - ');
INSERT INTO zoznam_nehnutelnosti
VALUES ('405','byt','TN-Juh','ano','85m2','4','PD','15.3.06','1450,0','BA',' - ');
INSERT INTO zoznam_nehnutelnosti
VALUES ('406','byt','TN-Juh','ano','70m2','3','PN','15.3.06','22,0','BB',' - ');
INSERT INTO zoznam_nehnutelnosti
VALUES ('407','RD','Drietoma','ano','5a','4','PD','15.3.06','715,0','AA',' - ');
INSERT INTO zoznam_nehnutelnosti
VALUES ('408','nebyt.priestory','Tr.Teplice','ano','80m2','3','PN','15.3.06','50,0','DB',' - ');
INSERT INTO zoznam_nehnutelnosti
VALUES ('409','byt','Tr.Teplice','nie','65m2','2','PD','15.3.06','840,0','BA',' - ');

INSERT INTO majitelia
VALUES ('012','RD','Peter','Hrivak','0905 632842','1600,0','1.6.05');
INSERT INTO majitelia
VALUES ('123','byt','Maria','Zelenkova','0902 456111','850,0','15.8.05');
INSERT INTO majitelia
VALUES ('333','byt','Pavol','Sobotka','0911 353626','950,0','1.2.06');
INSERT INTO majitelia
VALUES ('345','stav.pozemok','Ivan','Berec','0911 655699','800,0','1.2.06');
INSERT INTO majitelia
VALUES ('355','RD','Stano','Mlinarik','0905 468987','220,0','15.2.06');
INSERT INTO majitelia
VALUES ('136','RD','Tomas','Novak','0910 798123','1550,0','1.9.05');
INSERT INTO majitelia
VALUES ('376','RD','Vlado','Belandsky','0903 174568','2300,0','1.3.06');
INSERT INTO majitelia
VALUES ('378','nebyt.priestory','Zuzana','Silkova','0902 165893','30,0','1.3.06');
INSERT INTO majitelia
VALUES ('245','byt','Matus','Zlatovsky','0911 446655','1000,0','15.11.05');
INSERT INTO majitelia
VALUES ('183','stav.pozemok','Peter','Hrivak','0905 632842','1350,0','15.10.05');
INSERT INTO majitelia
VALUES ('235','byt','Martina','Bulkova','0904 714396','8,0','1.11.05');
INSERT INTO majitelia
VALUES ('400','nebyt.priestory','Martin','Vlk','0908 112456','45,0','15.3.06');
INSERT INTO majitelia
VALUES ('401','stav.pozemok','Juraj','Rulsky','0910 968456','910,0','15.3.06');
INSERT INTO majitelia
VALUES ('402','byt','Milos','Balaj','0904 563128','800,0','15.3.06');
INSERT INTO majitelia
VALUES ('403','byt','Miroslav','Pstros','0911 557894','5,0','15.3.06');
INSERT INTO majitelia
VALUES ('404','byt','Lubos','Zatko','0905 789323','1400,0','15.3.06');
INSERT INTO majitelia
VALUES ('405','byt','Emilia','Valkova','0911 998667','1300,0','15.3.06');
INSERT INTO majitelia
VALUES ('406','byt','Jan','Masar','0903 353699','11,0','15.3.06');
INSERT INTO majitelia
VALUES ('407','RD','Viera','Dubova','0904 557998','650,0','15.3.0');
INSERT INTO majitelia
VALUES ('408','nebyt.priestory','Patrik','Ziak','0902 987456','25,0','15.3.06');
INSERT INTO majitelia
VALUES ('409','byt','Patrik','Ziak','0902 987456','750,0','15.3.0');

INSERT INTO celkova_cena
VALUES ('012','1600,0','AA','100,0','1700,0');
INSERT INTO celkova_cena
VALUES ('123','850,0','BA','50,0','900,0');
INSERT INTO celkova_cena
VALUES ('333','950,0','BA','150,0','1100,0');
INSERT INTO celkova_cena
VALUES ('345','800,0','CA','80,0','880,0');
INSERT INTO celkova_cena
VALUES ('355','220,0','AA','22,0','2420,0');
INSERT INTO celkova_cena
VALUES ('136','1550,0','AA','95,0','1645,0');
INSERT INTO celkova_cena
VALUES ('376','2300,0','AA','230,0','2530,0');
INSERT INTO celkova_cena
VALUES ('378','30,0','DB','30,0','60,0');
INSERT INTO celkova_cena
VALUES ('245','1000,0','BA','100,0','1100,0');
INSERT INTO celkova_cena
VALUES ('183','1350,0','CA','110,0','1460,0');
INSERT INTO celkova_cena
VALUES ('235','8,0','BB','6,4','14,4');
INSERT INTO celkova_cena
VALUES ('400','45,0','DB','45,0','90,0');
INSERT INTO celkova_cena
VALUES ('401','910,0','CA','90,0','1000,0');
INSERT INTO celkova_cena
VALUES ('402','800,0','BA','96,0','896,0');
INSERT INTO celkova_cena
VALUES ('403','5,0','BB','5,0','10,0');
INSERT INTO celkova_cena 
VALUES ('404','1400,0','BA','170,0','1570,0');
INSERT INTO celkova_cena
VALUES ('405','1300,0','BA','150,0','1450,0');
INSERT INTO celkova_cena
VALUES ('406','11,0','BB','11,0','22,0');
INSERT INTO celkova_cena
VALUES ('407','650,0','AA','65,0','715,0');
INSERT INTO celkova_cena
VALUES ('408','25,0','DB','25,0','50,0');
INSERT INTO celkova_cena
VALUES ('409','750,0','BA','90,0','840,0');

INSERT INTO provizie
VALUES ('AA','A','PD','10','8','6');
INSERT INTO provizie
VALUES ('AB','A','PN','100','80','50');
INSERT INTO provizie
VALUES ('BA','B','PD','12','10','6');
INSERT INTO provizie
VALUES ('BB','B','PN','100','80','50');
INSERT INTO provizie
VALUES ('CA','C','PD','10','8','6');
INSERT INTO provizie
VALUES ('DA','D','PD','10','8','6');
INSERT INTO provizie
VALUES ('DB','D','PN','100','80','50');

INSERT INTO druh_nehn
VALUES ('A','RD');
INSERT INTO druh_nehn
VALUES ('B','byt');
INSERT INTO druh_nehn
VALUES ('C','stav.pozemok');
INSERT INTO druh_nehn
VALUES ('D','nebyt.priestory');

INSERT INTO forma_ponuky
VALUES ('PD','predaj');
INSERT INTO forma_ponuky
VALUES ('PN','prenajom');


1)
select MJ.meno, MJ.priezvisko, ZN.druh, ZN.celk_cena_tis, MJ.doh_suma_tis
from majitelia MJ, zoznam_nehnutelnosti ZN
where ZN.kod_nehn=MJ.kod_nehn;

2)
select MJ.meno, MJ.priezvisko,MJ.tel_cislo, F.forma,ZN.poc_izieb,ZN.celk_cena_tis
from zoznam_nehnutelnosti ZN, majitelia MJ, forma_ponuky F
where ZN.kod_nehn=MJ.kod_nehn
and ZN.kod_formy=F.kod_formy
and ZN.druh='byt'
order by MJ.priezvisko;

3)
select ZN.kod_nehn, ZN.druh, ZN.datum_zmluvy,ZN.celk_cena_tis,ZN.zlava
from zoznam_nehnutelnosti ZN
where ZN.datum_zmluvy <'15.10.05';

4)
select DN.druh, count(ZN.druh) as pocet_druhov_nehn
from zoznam_nehnutelnosti ZN inner join druh_nehn DN on
ZN.druh=DN.druh
group by DN.druh;

5)
select AVG(C.celk_cena_tis) as priem_cena_RD 
from zoznam_nehnutelnosti ZN inner join celkova_cena C 
on ZN.kod_nehn=C.kod_nehn
group by ZN.druh,ZN.zlava
having ZN.druh='RD'
and ZN.zlava='horuca akcia' ;   

/doplnujuci dotaz na vypocet sumy ceny RD v horucej akcii,
doplnujuci k predchadzajucemu dotazu/

select SUM(C.celk_cena_tis) as cena_vsetkych_RD
from zoznam_nehnutelnosti ZN inner join celkova_cena C
on ZN.kod_nehn=C.kod_nehn
group by ZN.druh,ZN.zlava
having ZN.druh='RD'
and ZN.zlava='horuca akcia';   

6)
select SUM(C.suma_prov_tis) as predp_vynos
from celkova_cena C inner join zoznam_nehnutelnosti ZN on 
c.kod_nehn=ZN.kod_nehn
where ZN.zlava =' - ';

7)
select ZN.druh,ZN.poc_izieb,ZN.celk_cena_tis,F.forma 
from zoznam_nehnutelnosti ZN inner join forma_ponuky F 
on ZN.kod_formy=F.kod_formy 
and ZN.celk_cena_tis=(select MIN(ZN.celk_cena_tis) from zoznam_nehnutelnosti ZN
where ZN.druh='byt'
and ZN.poc_izieb='2'
and ZN.kod_formy='PD');

8)
select kod_nehn,druh, celk_cena_tis
from zoznam_nehnutelnosti
where celk_cena_tis=(select MAX(celk_cena_tis) from zoznam_nehnutelnosti where druh='RD');

9)
select ZN.druh, ZN.lokalita, ZN.rozloha_poz, MJ.priezvisko, MJ.tel_cislo, ZN.celk_cena_tis 
from zoznam_nehnutelnosti ZN,majitelia MJ
where ZN.kod_nehn=MJ.kod_nehn
and ZN.druh='stav.pozemok'
and ZN.celk_cena_tis<'900,0';  

10)
select ZN.kod_nehn,zn.druh,zn.lokalita,zn.celk_cena_tis, c.suma_prov_tis
from zoznam_nehnutelnosti ZN inner join celkova_cena C on
ZN.kod_nehn=C.kod_nehn
where C.suma_prov_tis=(select MAX(C.suma_prov_tis) from celkova_cena C);

11)
select ZN.druh, ZN.lokalita, ZN.poc_izieb, ZN.datum_zmluvy, ZN.celk_cena_tis,C.suma_prov_tis,
F.forma
from (zoznam_nehnutelnosti ZN inner join forma_ponuky F
on ZN.kod_formy=F.kod_formy) inner join celkova_cena C 
on ZN.kod_nehn=C.kod_nehn
where ZN.kod_formy='PN'
order by ZN.datum_zmluvy desc;
 
12)
select distinct MJ.priezvisko, MJ.meno, MJ.tel_cislo
from majitelia MJ
order by MJ.priezvisko;

13)
select ZN.kod_nehn, ZN.druh, ZN.lokalita,F.forma, ZN.celk_cena_tis,MJ.priezvisko,
MJ.tel_cislo,ZN.datum_zmluvy
from (zoznam_nehnutelnosti ZN inner join majitelia MJ on
ZN.kod_nehn=MJ.kod_nehn) inner join forma_ponuky F on
ZN.kod_formy=F.kod_formy
where ZN.datum_zmluvy>'15.2.06'
order by ZN.datum_zmluvy desc;

14)
select D.druh,P.kod_formy,P.provizia,P.akcia,P.horuca_akcia 
from provizie P inner join druh_nehn D 
on P.kod_druhu=D.kod_druhu
where D.druh='byt'
and P.kod_formy='PD';

15)
create view cista_provizia
as
select MJ.kod_nehn,MJ.doh_suma_tis,(MJ.doh_suma_tis * provizia / 100)
as "cista_provizia_tis"
from (majitelia MJ inner join zoznam_nehnutelnosti ZN on 
MJ.kod_nehn=ZN.kod_nehn) inner join provizie P on
ZN.kod_prov=P.kod_prov;

select * from cista_provizia;


16)
create view sum_cista_provizia
as
select SUM(P.provizia*MJ.doh_suma_tis/100)
as "ideal_vynosy"
from (majitelia MJ inner join zoznam_nehnutelnosti ZN on
MJ.kod_nehn=ZN.kod_nehn) inner join provizie P on
ZN.kod_prov=P.kod_prov;


create view sum_akt_prov 
as
select SUM(C.suma_prov_tis) as "akt_predp_vynosy"
from celkova_cena C inner join zoznam_nehnutelnosti ZN on 
c.kod_nehn=ZN.kod_nehn;


select SCP.ideal_vynosy,SAP.akt_predp_vynosy,(SCP.ideal_vynosy - (SAP.akt_predp_vynosy)) 
as "aktualna_strata"
from sum_cista_provizia SCP,sum_akt_prov SAP ;

/alternativa/
create view SUM(C.suma_prov_tis) as predp_vynos
from celkova_cena C inner join zoznam_nehnutelnosti ZN on 
c.kod_nehn=ZN.kod_nehn;



/dotaz vypise mena vlastnikov a zoradi ich/
select MJ.priezvisko, MJ.meno, MJ.tel_cislo, F.forma,ZN.druh, MJ.datum_zmluvy
from ((majitelia MJ inner join zoznam_nehnutelnosti ZN on 
MJ.kod_nehn=ZN.kod_nehn) inner join forma_ponuky F on
ZN.kod_formy=F.kod_formy)
where MJ.druh='byt'
order by MJ.priezvisko;