create table mib_zapas(
id1 integer not null, 
id2 integer  not null,
 goly1 integer not null,
 goly2 integer not null,
 body1 integer,
 body2 integer, 
primary key(id1, id2));

create table mib_muzstvo (
id integer not null, 
meno varchar(30) not null, 
mesto varchar(20) not null, 
primary key(id));

drop table mib_muzstvo;
drop table mib_zapas;

insert into mib_muzstvo values 
(1, 'Real Madrid', 'Madrid'),(2,'Manchester United', 'Manchester'),(3,'Bayern Mnichov','Mnichov'),
(4,'Inter Milano','Milano'),(5,'Juventus Turin','Turin');

alter TABLE MIB_zapas add foreign key idprvy (id1) references MIB_muzstvo;
alter TABLE MIB_zapas add foreign key iddruhy (id2) references MIB_muzstvo;

insert into mib_zapas values
(1,2,1,0,null,null),
(1,3,4,2,null,null),
(1,4,3,5,null,null),
(1,5,1,1,null,null),
(2,3,2,1,null,null),
(2,4,5,1,null,null),
(2,5,2,2,null,null),
(3,4,2,2,null,null),
(3,5,1,0,null,null),
(4,5,0,0,null,null);


UPDATE mib_zapas SET body1 = 3, body2 = 0 WHERE goly1>goly2;
UPDATE mib_zapas SET body1 = 1, body2 = 1 WHERE goly1=goly2;
UPDATE mib_zapas SET body1 = 0, body2 = 3 WHERE goly1<goly2;

select m1.meno,m2.meno,char(z.goly1),char(z.goly2) 
from mib_muzstvo m1, mib_muzstvo m2, mib_zapas z 
where z.goly1=z.goly2 and m1.id=z.id1 and m2.id=z.id2;

select m1.meno,m2.meno,char(z.goly1),char(z.goly2) 
from mib_muzstvo m1, mib_muzstvo m2, mib_zapas z 
where (z.id1=2 and z.id1=m1.id and z.id2=m2.id) or (z.id2=2 and z.id1=m1.id and z.id2=m2.id) ;

select m1.meno,m2.meno,char(z.goly1),char(z.goly2) 
from mib_muzstvo m1, mib_muzstvo m2, mib_zapas z 
where (z.id1=(select m.id from mib_muzstvo m where meno='Manchester United' ) 
and z.id1=m1.id and z.id2=m2.id) 
or (z.id2=(select m.id from mib_muzstvo m where meno='Manchester United' ) 
and z.id1=m1.id and z.id2=m2.id) ;

select m1.meno,m2.meno,char(z.goly1),char(z.goly2) 
from mib_muzstvo m1, mib_muzstvo m2, mib_zapas z 
where (z.id1=(select m.id from mib_muzstvo m where meno='Manchester United' ) 
and z.id1=m1.id and z.id2=m2.id) 
union
select m1.meno,m2.meno,char(z.goly1),char(z.goly2) 
from mib_muzstvo m1, mib_muzstvo m2, mib_zapas z 
where (z.id2=(select m.id from mib_muzstvo m where meno='Manchester United' ) 
and z.id1=m1.id and z.id2=m2.id) 

select m2.meno,char(z.goly1),char(z.goly2) 
from mib_muzstvo m1, mib_muzstvo m2, mib_zapas z 
where (z.id1=(select m.id from mib_muzstvo m where meno='Manchester United' ) 
and z.id1=m1.id and z.id2=m2.id) 
union
select m1.meno,char(z.goly1),char(z.goly2) 
from mib_muzstvo m1, mib_muzstvo m2, mib_zapas z 
where (z.id2=(select m.id from mib_muzstvo m where meno='Manchester United' ) 
and z.id1=m1.id and z.id2=m2.id) 

select count(z.goly1) from mib_zapas z where z.id1=
(select m.id from mib_muzstvo m where m.meno='Bayern Mnichov')
union
select count(z.goly2) from mib_zapas z where z.id2=
(select m.id from mib_muzstvo m where m.meno='Bayern Mnichov')