create table st_interpret(
id Integer NOT NULL PRIMARY KEY, 
meno VARCHAR(20), 
rok_vz DATE);

create table st_titul(
id INTEGER NOT NULL PRIMARY KEY, 
nazov VARCHAR(30), 
rok_vy DATE , 
vydavatel VARCHAR(30), 
cena REAL, 
idinterpret Integer);

create table zakaznik(
id INTEGER NOT NULL PRIMARY KEY, 
meno VARCHAR(30), 
adresa VARCHAR(30));

create table st_predaj(
id INTEGER NOT NULL PRIMARY KEY, 
pocet INTEGER,
datum DATE, 
idtitul INTEGER, 
idzakaznik INTEGER);


ALTER TABLE st_titul ADD CONSTRAINT spevak FOREIGN KEY(idinterpret) REFERENCES st_interpret;
ALTER TABLE st_predaj ADD CONSTRAINT odberatel FOREIGN KEY(idzakaznik) REFERENCES st_zakaznik;
ALTER TABLE st_predaj ADD CONSTRAINT tovar FOREIGN KEY(idtitul) REFERENCES st_titul;


-------inserty----------

insert into st_interpret values(1,'Guns n Roses','1.1.1984'),(2,'IRON MAIDEN','17.6.1980'),(3,'HELLOWEEN','13.12.1982'),(4,'EDGUY','29.7.1999'),(5,'STRATOVARIUS','26.6.1988'),(6,'IRON SAVIOR','11.11.1998');

insert into st_titul values(1,'Killers','18.2.1981','SONY',560,2),(2,'Use Your Illusions I','17.5.1986','GEFFEN RECORDS',680,1),(3,'Brave New World','1.1.2000','SONY',720,2),(4,'Keeper Of The 7th Keys','14.7.1983','GMB',450,3),(5,'Theater Of Salvation','7.7.2000','GMB',640,4),(6,'Episode','17.6.2000','GEFFEN RECORDS',620,5),(7,'Time Of The OATH','7.8.2001','GMB',720,3),
(8,'Fear OF THE DARK','5.6.1988','SONY',500,2);
insert into st_titul values(9,'Iron Fist','5.2.2000','MadStudio',440,5),(10,'Master Of The Rings','4.4.2000','RapidStudio',400,3),(11,'Piece Of Mind','17.2.1981','LA Studios',464,2);


insert into st_zakaznik values(1,'Music Centrum','Zvonarska 6'),(2,'Stanislav','Panelova 15'),(3,'Music Shop','Hlavna 90'),(4,'Jozko Mrkva','Zeleninova 1'),(5,'Night Club ISKRA','Leninova 15');

insert into st_predaj values(1,2,'20.12.1998',1,1),(2,5,'13.7.2001',8,3),(3,7,'6.6.2001',3,4),(4,1,'12.12.2001',3,2),(5,3,'1.12.2000',2,5);
insert into st_predaj values(6,6,'7.8.1986',10,3),(7,2,'17.6.2000',9,5),(8,1,'4.9.1988',10,2);
insert into st_predaj values(9,5,'12.12.2000',7,1),(10,3,'7.8.2000',5,2);
insert into st_predaj values(11,3,'9.8.1999',11,5),(12,8,'10.10.2000',11,1);

select t.vydavatel,sum(t.cena) 
from st_titul t,st_predaj p 
where p.idtitul=t.id 
GROUP BY (t.vydavatel);

select t.vydavatel,sum(t.cena) 
from st_titul t,st_predaj p 
where p.idtitul=t.id 
GROUP BY (t.vydavatel)
order by(sum(t.cena)) DESC;


select (select DISTINCT count (*)  from st_titul a where a.vydavatel<=t.vydavatel ) ,
t.vydavatel,sum(t.cena) 
from st_titul t,st_predaj p 
where p.idtitul=t.id 
GROUP BY (t.vydavatel)
order by(sum(t.cena)) DESC;

create view st_tmp(vydav, zisk) as
select t.vydavatel,sum(t.cena) 
from st_titul t,st_predaj p 
where p.idtitul=t.id 
GROUP BY (t.vydavatel)
order by(sum(t.cena)) DESC;

create view(cislo,v,z) st_vydavatelstva as
select (select count(*) from st_tmp tmp where tmp.vydav<=a.vydav),a.vydav,a.zisk from st_tmp a
group by(a.vydav,a.zisk) where cislo<=5;

create view st_vydavatelstva (cislo,v,z) as 
select (select count(*) from st_tmp tmp where tmp.vydav<=a.vydav),a.vydav,a.zisk from st_tmp a 
group by(a.vydav,a.zisk);

select * from st_vydavatelstva where cislo<=5;
create view st_vydavatelstva (cislo,v,z) as 
select (select count(*) from st_tmp tmp where tmp.vydav<=a.vydav),a.vydav,a.zisk from st_tmp a where cislo<=5 group by(a.vydav,a.zisk);