create table korisnik(



korisnichkoID numeric not null unique,



korisnichkoIme varchar not null,



lozinka varchar(15) not null,



email varchar not null constraint pravilen_mail check(email similar to '%@%.com'),



primary key (korisnichkoID)



);







create table clanarina(



clanarinaID numeric unique,



period numeric not null,



primary key (clanarinaID)



);















create table domakjin(



clanarinaID numeric references Clanarina(clanarinaID) not null,



korisnichkoID numeric not null references Korisnik(korisnichkoID) unique,



slika numeric,



constraint pk_domakjin primary key (korisnichkoID, clanarinaID)



);





create table gostin(



embg varchar not null,



korisnichkoID numeric references korisnik(korisnichkoID),



clanarinaID numeric,



domakjinID numeric,



tip varchar,



primary key(korisnichkoID),



foreign key(domakjinID, clanarinaID) references domakjin (korisnichkoID, clanarinaID)



);







create table oceni(

clanarinaID numeric,

domakjinID numeric,

gostinID numeric references gostin(korisnichkoID),

ocena int not null,



foreign key (domakjinID, clanarinaID) references domakjin(korisnichkoID, clanarinaID),

primary key (domakjinID, clanarinaID, gostinID, ocena)



);









create table lokacija(



lokacijaID numeric not null unique,



grad varchar not null,



naselba varchar,



ulica varchar not null,



broj numeric not null,



domakjinID numeric,



clanarinaID numeric,



primary key (lokacijaID),



foreign key (domakjinID, clanarinaID) references domakjin (korisnichkoID, clanarinaID)



);







create table prestoj(



gostinID numeric references korisnik (korisnichkoID) not null unique,



domakjinID numeric,



clanarinaID numeric,



cena numeric not null,



vremetraenje numeric not null,



constraint fk_domakjin foreign key (domakjinID, clanarinaID) references domakjin(korisnichkoID, clanarinaID),



constraint pk_prestoj primary key(gostinID, domakjinID, clanarinaID)



);















create table online_Transakcija(



domakjinID numeric,



chlanarinaID numeric,



gostinID numeric,



embg_transakcija numeric not null unique,



tip_kartichka text not null,



smetka_primach numeric not null,



smetka_plakjach numeric not null,



foreign key (domakjinID, gostinID, chlanarinaID) references prestoj(domakjinID, gostinID, clanarinaID),



primary key(domakjinID, gostinID, chlanarinaID, embg_transakcija)



);















create table privatnoSmestuvanje(



domakjinID numeric,



clanarinaID numeric,



naziv text not null,



parking boolean not null,



foreign key (domakjinID, clanarinaID) references domakjin(korisnichkoID, clanarinaID),







primary key(domakjinID, clanarinaID)







);















create table hotel(



clanarinaID numeric not null,

domakjinID numeric not null,

hotelIme varchar,

parking bool not null,

dzvezdi numeric not null,



foreign key (domakjinID, clanarinaID) references domakjin (korisnichkoID, clanarinaID),



primary key(domakjinID, clanarinaID)



);















create table spaCentar(







spaID numeric not null unique,







adresa text not null,







bazen boolean not null,







sauna boolean not null,







clanarinaID numeric,







hotelID numeric,















primary key (spaID),







foreign key(hotelID, clanarinaID) references hotel(domakjinID, clanarinaID)















);























create table stan(







privatnoLiceID numeric,







clanarinaID numeric,







terasa bool,















foreign key(privatnoLiceID, clanarinaID) references privatnoSmestuvanje(domakjinID, clanarinaID),







primary key(privatnoLiceID, clanarinaID)







);















create table kukja(







privatnoSmestuvanjeID numeric not null,







clanarinaID numeric not null,







dvor bool,







tip_greenje text,















foreign key (privatnoSmestuvanjeID, clanarinaID) references privatnoSmestuvanje(domakjinID, clanarinaID),







primary key (privatnoSmestuvanjeID, clanarinaID)















);















create table soba_kukja(



soba_kID numeric,



kukjaID numeric,



clanarinaID numeric,



broj_kreveti numeric,



tip_kreveti text,



foreign key (kukjaID, clanarinaID) references kukja(privatnoSmestuvanjeID, clanarinaID),







primary key (kukjaID, clanarinaID,soba_kId)



);







create table soba_hotel(



soba_hID numeric,



hotelID numeric,



clanarinaID numeric,



broj_kreveti numeric,

tip_kreveti text,





foreign key (hotelID, clanarinaID) references hotel(domakjinID, clanarinaID),







primary key(hotelID, clanarinaID,soba_hID)







);



















create table bungalov (







bungalovID numeric not null unique,







brojBungalov numeric not null,







hotelID numeric not null,







clanarinaID numeric not null,















primary key (bungalovID),







foreign key (hotelID, clanarinaID) references hotel(domakjinID, clanarinaID)





);





alter table online_transakcija add iznos int;





insert into korisnik(korisnichkoID,korisnichkoIme,lozinka,email) values ('1', 'Angela','angela12','angela@yahoo.com' );



insert into korisnik(korisnichkoID,korisnichkoIme,lozinka,email) values ('2', 'Gogo','gogo12','gogo@yahoo.com' );



insert into korisnik(korisnichkoID,korisnichkoIme,lozinka,email) values ('3', 'Anastasija','anastasija12','anastasija@yahoo.com' );



insert into korisnik(korisnichkoID,korisnichkoIme,lozinka,email) values ('4', 'Olivera','olivera12','olivera@yahoo.com' );



insert into korisnik(korisnichkoID,korisnichkoIme,lozinka,email) values ('5', 'Blagoj','blagoj12','blagoj@yahoo.com' );



insert into korisnik(korisnichkoID,korisnichkoIme,lozinka,email) values ('6', 'Goran','goran12','goran@yahoo.com' );



insert into korisnik(korisnichkoID,korisnichkoIme,lozinka,email) values ('7', 'Bojana','bojana12','bojana@yahoo.com' );



insert into clanarina(clanarinaid,period) values ('1','3');



insert into domakjin(korisnichkoid,clanarinaid,slika) values ('1','1','0');

insert into domakjin(korisnichkoid,clanarinaid,slika) values ('5','1','10');





insert into lokacija(lokacijaid,grad,naselba,ulica,clanarinaid,domakjinid,broj) values ('1','Skopje','Gjorce','Socialisticka zora','1','1','49');



insert into lokacija(lokacijaid,grad,naselba,ulica,clanarinaid,domakjinid,broj) values ('4','Skopje','Centar','Makedonja','1','5','49');

insert into lokacija(lokacijaid,grad,naselba,ulica,clanarinaid,domakjinid,broj) values ('5','Skopje','Kisela Voda','Makedonja','1','5','49');





insert into hotel(clanarinaid,domakjinid,parking,dzvezdi,hotelIme) values ('1','1','true','4','Palas');



insert into soba_hotel(hotelID,clanarinaID,broj_kreveti,tip_kreveti,soba_hID) values ('1','1','2','Bracen','1');



insert into soba_hotel(hotelID,clanarinaID,broj_kreveti,tip_kreveti,soba_hID) values ('1','1','2','Bracen','2');



insert into soba_hotel(hotelID,clanarinaID,broj_kreveti,tip_kreveti,soba_hID) values ('1','1','2','Bracen','3');



insert into domakjin(korisnichkoid,clanarinaid,slika) values ('3','1','5');



insert into lokacija(lokacijaid,grad,naselba,ulica,clanarinaid,domakjinid,broj) values ('2','Skopje','Kozle','Lerinska','1','3','77');

insert into lokacija(lokacijaid,grad,naselba,ulica,clanarinaid,domakjinid,broj) values ('3','Skopje','Karposh','Lerinska','1','3','77');



insert into privatnoSmestuvanje(domakjinID, clanarinaID, naziv, parking) values('3', '1', 'Ansastasija Smeshtaj', '1');



insert into kukja(privatnoSmestuvanjeID, clanarinaID, dvor, tip_greenje) values('3', '1', '1', 'Parno');

insert into soba_kukja(kukjaID, clanarinaID, soba_kID, broj_kreveti, tip_kreveti) values('3', '1', '1', '2', 'Edinechen');

insert into soba_kukja(kukjaID, clanarinaID, soba_kID, broj_kreveti, tip_kreveti) values('3', '1', '2', '1', 'Brachen');



insert into gostin(embg,korisnichkoID,clanarinaid,domakjinID,tip) values ('123456679901','4','1','1','Student');



insert into gostin(embg,korisnichkoID,clanarinaid,domakjinID,tip) values ('123456679900','2','1','1','Turist');



insert into gostin(embg,korisnichkoID,clanarinaid,domakjinID,tip) values ('123456679902','6','1','3','Student');



insert into gostin(embg,korisnichkoID,clanarinaid,domakjinID,tip) values ('123456673902','7','1','3','Postojan');





insert into prestoj(gostinid,domakjinid,clanarinaid,cena,vremetraenje) values ('2','1','1','1000','2');

insert into prestoj(gostinid,domakjinid,clanarinaid,cena,vremetraenje) values ('4','1','1','6000','30');

insert into prestoj(gostinid,domakjinid,clanarinaid,cena,vremetraenje) values ('6','3','1','12000','15');

insert into prestoj(gostinid,domakjinid,clanarinaid,cena,vremetraenje) values ('7','3','1','2000','5');





insert into online_transakcija(domakjinid,chlanarinaid,gostinid,embg_transakcija,tip_kartichka,smetka_primach,smetka_plakjach, iznos) values ('1','1','2','1234123','master','1111','2222', '1000');



insert into spacentar(spaid,adresa,bazen,sauna,hotelid,clanarinaid) values ('1','karposh','true','false','1','1');



insert into oceni(domakjinID, clanarinaID, gostinID, ocena) values ('1', '1', '2', '5');



insert into oceni(domakjinID, clanarinaID, gostinID, ocena) values ('1', '1', '2', '4');



insert into oceni(domakjinID, clanarinaID, gostinID, ocena) values ('5', '1', '4', '5');



insert into oceni(domakjinID, clanarinaID, gostinID, ocena) values ('5', '1', '4', '3');







/*1. Најди ја просечната оцена ( од страна на гостите ) на домаќинiте што нудат приватно сместување.*/



select privatnoSmestuvanje.naziv, avg(oceni.ocena) from oceni, privatnoSmestuvanje, domakjin where (privatnoSmestuvanje.domakjinID, privatnoSmestuvanje.clanarinaID) = (domakjin.korisnichkoID, domakjin.clanarinaID) and (oceni.domakjinID, oceni.clanarinaID) = (domakjin.korisnichkoID, domakjin.clanarinaID) group by privatnoSmestuvanje.naziv;







/*2. Најди го максималнот износ направен при трансакција кон домаќинот со ИД  = 1. */

select korisnik.korisnichkoime, max(online_transakcija.iznos) from online_transakcija, domakjin, korisnik where (domakjin.korisnichkoID, domakjin.clanarinaID) = (online_transakcija.domakjinID, online_transakcija.chlanarinaID) and domakjin.korisnichkoId= korisnik.korisnichkoid group by korisnik.korisnichkoime;



/*  3. Кој/и домаќин/и нуди сместување на најголем број локации?*/

select korisnichkoIme from korisnik,

(select domakjin.korisnichkoid as did, count(lokacija.lokacijaid) as broj_lokacii from lokacija, domakjin where (domakjin.korѝisnichkoid, domakjin.clanarinaid) = (lokacija.domakjinid, lokacija.clanarinaid) group by did) as vkupno,

(select  max(pom_broj_lokacii) as max_lokacii from

(select count(lokacija.lokacijaid)as pom_broj_lokacii from lokacija, domakjin where (domakjin.korisnichkoid, domakjin.clanarinaid) = (lokacija.domakjinid, lokacija.clanarinaid) group by domakjin.korisnichkoid) as pom) as maximum

where vkupno.broj_lokacii = maximum.max_lokacii and korisnik.korisnichkoid = vkupno.did;



/* 4. Колкав процент гости (од вкупно гости кај сите домаќини)престојуваат кај домаќинот „Ангела“? */

select broj*100/vkupen_broj || '%' as procent from

(select count(prestoj.gostinid) as broj from prestoj, domakjin, korisnik where (prestoj.domakjinid, prestoj.clanarinaid) = (domakjin.korisnichkoid, domakjin.clanarinaid) and domakjin.korisnichkoid = korisnik.korisnichkoid and korisnik.korisnichkoime = 'Angela') as br,

(select count(gostin.embg) as vkupen_broj from gostin) as vkupno;



/* 5. Кој хотел располага со најголем број соби?*/

select hotel.hotelime from hotel,

(select soba_hotel.hotelid as hotelid, soba_hotel.clanarinaid as clid, count(soba_hotel.soba_hid) as broj_sobi from soba_hotel, hotel where (soba_hotel.hotelid, soba_hotel.clanarinaid) = (hotel.domakjinid, hotel.domakjinid) group by soba_hotel.hotelid, soba_hotel.clanarinaid) as count_sobi,

(select max(pom_broj_sobi) as max_sobi from

(select soba_hotel.hotelid, soba_hotel.clanarinaid, count(soba_hotel.soba_hid) as pom_broj_sobi from soba_hotel, hotel where (soba_hotel.hotelid, soba_hotel.clanarinaid) = (hotel.domakjinid, hotel.domakjinid) group by soba_hotel.hotelid, soba_hotel.clanarinaid) as pom

) as maximum

where (hotel.domakjinid, hotel.clanarinaid) = (count_sobi.hotelid, count_sobi.clid) and count_sobi.broj_sobi = maximum.max_sobi;





/* 6. Кој домаќин од Скопје има  просечна оцена повисока од 3 од страна на гостите? */

select korisnichkoIme from korisnik inner join

(select domakjin.korisnichkoid as prosechni_id, domakjin.clanarinaid as prosechni_clanarina, avg(oceni.ocena) as prosechna_ocena from oceni,  domakjin where (oceni.domakjinID, oceni.clanarinaID) = (domakjin.korisnichkoID, domakjin.clanarinaID) group by prosechni_id, prosechni_clanarina) as prosechni

on korisnik.korisnichkoid = prosechni_id and prosechni.prosechna_ocena > '3'

inner join lokacija on lokacija.domakjinid  = korisnik.korisnichkoid and lokacija.grad = 'Skopje';



/* 7.Колкав процент на луѓе користат сместување во хотел */

select gosti_hotel.br_gosti*100/vkupno.vk_gosti from



(select count(gostin.korisnichkoid) as vk_gosti from gostin) as vkupno,



(select prestoj.domakjinid as did, prestoj.clanarinaid as clid, count(prestoj.gostinid) as br_gosti from prestoj, domakjin, hotel where (prestoj.domakjinid, prestoj.clanarinaid) = (domakjin.korisnichkoid, domakjin.clanarinaid) and (domakjin.korisnichkoid, domakjin.clanarinaid) = (hotel.domakjinid, hotel.clanarinaid) group by prestoj.domakjinid, prestoj.clanarinaid) as gosti_hotel;





/* 8. Колкав процент на луѓе користат  приватно сместување ?*/

select gosti_ps.br_gosti*100/vkupno.vk_gosti || '%' as procent_gosti from



(select count(gostin.korisnichkoid) as vk_gosti from gostin) as vkupno,



(select prestoj.domakjinid as did, prestoj.clanarinaid as clid, count(prestoj.gostinid) as br_gosti from prestoj, domakjin, privatnoSmestuvanje where (prestoj.domakjinid, prestoj.clanarinaid) = (domakjin.korisnichkoid, domakjin.clanarinaid) and (domakjin.korisnichkoid, domakjin.clanarinaid) = (privatnoSmestuvanje.domakjinid, privatnoSmestuvanje.clanarinaid) group by prestoj.domakjinid, prestoj.clanarinaid) as gosti_ps;