create table pm_tovar (
id integer not null,
nazov varchar(20) not null,
cena integer  not null,
hmotnost integer,
primary key(id));

drop table pm_tovar;

create table pm_odberatel(
id integer not null,
ico integer not null,
nazov varchar(20),
adresa varchar(50) not null,
primary key(id));

drop table pm_odberatel;

create table pm_dodavatel(
id integer not null,
ico integer not null,
nazov varchar(20),
adresa varchar(50) not null,
primary key(id));

drop table pm_dodavatel;

create table pm_faktura(
id integer not null,
iddodavatel integer,
datum date not null,
idodberatel integer,
primary key(id) );

drop table pm_faktura;

create table pm_objednavka(
idfaktura integer not null,
idtovar integer not null,
mnozstvo integer not null);

drop table pm_objednavka;
------------------------------------------------------------------------------------------


alter table pm_objednavka add foreign key pm_idtovar(idtovar) references pm_tovar;

alter table pm_objednavka add foreign key pm_idfaktura(idfaktura) references pm_faktura;

alter table pm_faktura add foreign key pm_iddod(iddodavatel) references pm_dodavatel;

alter table pm_faktura add foreign key pm_idodb(idodberatel) references pm_odberatel;


dropnutia referencii:

alter table pm_objednavka drop foreign key pm_idtovar;

alter table pm_objednavka drop foreign key pm_idfaktura;

alter table pm_faktura drop foreign key pm_iddod;

alter table pm_faktura drop foreign key pm_idodb; 

-------------------------------------------------------------------------------------------

insert into pm_tovar values
(1,'klinec',1,10),
(2,'truba',5000,5000),
(3,'suchar',10,100),
(4,'zaclona',300,1000),
(5,'pol_chlpa',50,1);

insert into pm_dodavatel values
(1,1000,'Pierko&syn','Pod Mostom 7'),
(2,1001,'Xlp&dcera','Na Moste 13'),
(3,1002,'Syn&dcera','Pri Moste 1'),
(4,1003,'Petko&Hamo','Paza 3');

insert into pm_odberatel values
(1,2000,'Lev&Zuzana','Cmiter 9'),
(2,2001,'Trubadur','Sporak 15'),
(3,2002,'Hovnik s.r.o.','Valandy 19');

insert into pm_faktura values
(1,1,'2000-02-12',null),
(2,2,'2002-01-10',null),
(3,null,'1010-05-13',2);

insert into pm_objednavka values
(1,4,200),
(1,1,4000),
(1,2,100)
(2,5,10000),
(2,1,50),
(2,3,51)
(3,2,5),
(3,3,250),
(3,4,100),
(3,5,9000);

---------------------------------------------------------------
SELECTY:

Vsetky dodavky:
select d.nazov,f.datum,t.nazov,o.mnozstvo 
from pm_faktura as f 
join pm_dodavatel d on f.iddodavatel=d.id 
join pm_objednavka o on f.id=o.idfaktura
join pm_tovar t on t.id=o.idtovar; 

Vsetky objednavky:
select od.nazov,f.datum,t.nazov,o.mnozstvo 
from pm_faktura as f 
join pm_odberatel od on f.idodberatel=od.id 
join pm_objednavka o on f.id=o.idfaktura
join pm_tovar t on t.id=o.idtovar; 

Celkovy prichozi tovar:
select
t.nazov,sum(o.mnozstvo),sum(o.mnozstvo)*sum(t.hmotnost)
from
pm_objednavka o
join pm_tovar t on o.idtovar=t.id
join pm_faktura f on f.iddodavatel is not null and o.idfaktura=f.id
group by(t.nazov);

celkovy odideny tovar:
select
t.nazov,sum(o.mnozstvo),sum(o.mnozstvo)*sum(t.hmotnost)
from
pm_objednavka o
join pm_tovar t on o.idtovar=t.id
join pm_faktura f on f.idodberatel is not null and o.idfaktura=f.id
group by(t.nazov);

aktualny stav v sklade://Z L E
select
t.nazov,sum(o1.mnozstvo)
from pm_faktura f1 
join pm_objednavka o1 on f1.id=o1.idfaktura and f1.iddodavatel is not null,
pm_faktura f2
join pm_objednavka o2 on f2.id=o2.idfaktura and f2.idodberatel is not null
right outer join pm_tovar t on t.id=o1.idtovar or t.id=o2.idtovar
group by(t.nazov);


KOPIROVANIE Z TAB DO TAB(pricom struktura ostane zachovana):

vtvorme si novu tabulku subjekt:
create table pm_subjekt
(id int not null primary key,
nazov varchar(20) not null,
ico integer not null,
adresa varchar(30) not null,
typ ineteger not null);

Najprv si vytvorime temporary tabulku pm_tmp:

create table pm_tmp
(id int not null primary key,
stare_id integer not null,
nazov varchar(20) not null,
ico integer not null,
adresa varchar(30) not null,
typ ineteger not null);


select
(select count(*) from pm_dodavatel d1 where d1.id<d.id ),d.id,d.nazov,d.ico,d.adresa,'1'
from pm_dodavatel d;

insert into pm_tmp 
(select
(select count(*) from pm_dodavatel d1 where d1.id<=d.id ),d.id,d.nazov,d.ico,d.adresa,1
 from pm_dodavatel d);

precislovanie iddodavatel vo fakture
1 Zhodime referenciu
2 zhodime dodavatela
3 Updateneme fakturu:

update pm_faktura f set iddodavatel=(
 select 
 pom.id 
 from pm_tmp pom where pom.stare_id=f.iddodavatel);

insert into pm_subjekt
(select t.id,t.nazov,t.ico,t.adresa,t.typ
 from pm_tmp t); 

AND NOW: odberatel 

select
(select count(*) from pm_odberatel d1 where d1.id<=d.id )+(select count(*) from pm_tmp),d.id,d.nazov,d.ico,d.adresa,'1'
from pm_odberatel d;

delete from pm_tmp;

insert into pm_tmp(
select
(select count(*) from pm_odberatel d1 where d1.id<=d.id )%2B(select count(*) 
from pm_tmp),d.id,d.nazov,d.ico,d.adresa,0
 from pm_odberatel d);


create table pm_faktura(
id integer not null primary key,
datum date not null,
idsubjekt integer not null)

insert into pm_faktura(select * from pm_tmp);