create table vyrobok (
pcv number primary key,
nazov char(10),
vyr_c number,
pred_c number
);

insert into vyrobok values (1,'stol',null,1200);
insert into vyrobok values (2,'stolicka',null,1800);
insert into vyrobok values (3,'policka',null,1100);

create table sklad (
pcm number primary key,
nazov_m char(20),
mj char(2),
jc number,
mnoz number
);

insert into sklad values (1,'drevotrieska','m2',400,200);
insert into sklad values (2,'drevotrieska','m2',350,120);
insert into sklad values (3,'kovanie','ks',120,100);
insert into sklad values (4,'panty','ks',60,200);
insert into sklad values (5,'skrutky','ks',5,1500);
insert into sklad values (6,'sklo3','m2',600,100);
insert into sklad values (7,'sklo5','m2',300,60);

create table vyroba (
pcv number,
pcm number,
spot_m float
);

insert into vyroba values (1,2,2);
insert into vyroba values (1,3,4);
insert into vyroba values (1,6,1.6);
insert into vyroba values (2,1,0.8);
insert into vyroba values (2,3,4);
insert into vyroba values (2,7,0.6);
insert into vyroba values (3,1,0.6);
insert into vyroba values (3,2,1.2);
insert into vyroba values (3,4,4);

select vyroba.pcv,vyroba.pcm,vyroba.spot_m,sklad.jc,sklad.jc*vyroba.spot_m as CENA from vyroba,sklad where vyroba.pcm=sklad.pcm and vyroba.pcv=1;
select vyroba.pcv,vyroba.pcm,vyroba.spot_m,sklad.jc,sklad.jc*vyroba.spot_m as CENA from vyroba,sklad where vyroba.pcm=sklad.pcm;
select vyroba.pcv,sum(sklad.jc*vyroba.spot_m) as CELKOM from vyroba,sklad where vyroba.pcm=sklad.pcm group by vyroba.pcv;

heslo:greif
meno:vgreif


select pcv, pred_c
from vyrobok
where pred_c > 1500;


create view tab1 as
select vyroba.pcv,sum(sklad.jc*vyroba.spot_m) as CELKOM from vyroba,sklad where vyroba.pcm=sklad.pcm group by vyroba.pcv;

select * from tab1;

update vyrobok 
set vyr_c = (select celkom from tab1 where tab1.pcv=1)
where vyrobok.pcv = 1;
 

update vyrobok 
set vyr_c = (select celkom from tab1 where tab1.pcv=vyrobok.pcv)
;

select * from vyrobok; 

select vyroba.pcv,vyroba.pcm,vyroba.spot_m,sklad.jc,sklad.jc*vyroba.spot_m as CENA 
from vyroba,sklad 
where vyroba.pcm=sklad.pcm 
and vyroba.pcv=1;




select vyroba.pcv,sum(sklad.jc*vyroba.spot_m) as CELKOM 
from vyroba,sklad 
where vyroba.pcm=sklad.pcm 
group by vyroba.pcv; 



insert into objednavka
create table objednane
(pcob number (4),
pcv number (4),
mnv number (4));

values(1,2,to_date('10.10.2006','dd.mm.yyyy'),to_date('14.10.2006',dd.mm.yyyy'),to_date('20.10.2006','dd.mm.yyyy'));
insert into objednavka
values(3,2,to_date('10.10.2006','dd.mm.yyyy'),to_date('10.10.2006','dd.mm.yyyy'),to_date('10.10.2006','dd.mm.yyyy'));

insert into objednavka
values(2,1,to_date('14.10.2006','dd.mm.yyyy'),to_date('14.10.2006','dd.mm.yyyy'),'');
);


insert into objednavka
values(3,2,to_date('10.10.2006','dd.mm.yyyy'),to_date('14.10.2006',dd.mm.yyyy'),to_date('20.10.2006','dd.mm.yyyy'));

insert into objednavka
values(2,1,to_date('14.10.2006','dd.mm.yyyy'),to_date('14.10.2006','dd.mm.yyyy'),'');
 
insert into objednane
values (1,2,10);

insert into objednane
values (1,3,4);
insert into objednane
values (2,1,15);


select o.pcob, o.pcv, o.mnv, v.pred_c,(o.mnv*v.pred_c) suma 
from objednane o, vyrobok v
where o.pcv=v.pcv;

select o.pcob, sum(o.mnv*v.pred_c)
from objednane o, vyrobok v
where o.pcv=v.pcv
group by o.pcob;


select o.pcob, o.pcv, o.mnv, v.pred_c, (o.mnv*v.pred_c) suma
from objednane o, vyrobok v
where o.pcv = v.pcv;

select o.pcob, o.pcv, o.mnv, v.pred_c, (o.mnv*v.pred_c) suma
from objednane o, vyrobok v
where o.pcv = v.pcv
order by o.pcob;


select o.pcob,sum(o.mnv*v.pred_c) celkom
from objednane o, vyrobok v
where o.pcv = v.pcv
group by o.pcob;


insert into objednavka
values(3,2,to_date('14.10.2006','dd.mm.yyyy'),to_date('14.10.2006','dd.mm.yyyy'),'');

create view tab2
as select o.pcob, sum(o.mnv*v.pred_c) celkom
from objednane o, vyrobok v
where o.pcv=v.pcv
group by o.pcob;

select ob.pcob, ob.pcod, tab2.celkom
from objednavka ob, tab2
where ob.pcob=tab2.pcob;

select ob.pcod, sum(tab2.celkom) dlh
from objednavka ob, tab2
where ob.pcob=tab2.pcob
group by ob.pcod;