create table mib_clovek (
id int not null primary key, 
meno varchar(20) not null, 
priezvisko varchar(20) not null, 
prve_priezv varchar(20), 
id_otec int, 
id_matka int , 
datum_od date not null, 
datum_do date);

insert into mib_clovek values 
(1,'Milan','Bodnar',NULL,NULL,NULL,'3.6.1980',NULL),
(2,'Evka','Klasovska','Cukanova',NULL,NULL,'27.7.1980',NULL),
(3,'Mikulas','Klasovsky',NULL,NULL,NULL,'21.10.1978',NULL),
(4,'Mariana','Bodnarova','Klasovska',3,2,'5.7.2009',NULL),
(5,'Jozef','Klasovsky',NULL,3,2,'4.5.2007',NULL),
(6,'Daniela','Bodnarova','bodnarova',1,4,'31.1.2029','2.5.2070'),
(7,'Peter','Juhasz',NULL,NULL,NULL,'3.8.1999','3.8.2062'),
(8,'Katarina','Juhasz','Klasovska',3,2,'23.3.2008',NULL),
(9,'Perla','Juhasz','Juhasz',7,8,'3.8.2030','5.6.2031'),
(10,'Andrezej','Dzerdevnmy',NULL,NULL,NULL,'6.4.2024','5.3.2066'),
(11,'et','dzerdevnmy',NULL,10,6,'3.5.2050',NULL),
(12,'Andrea','Klasovska','Chupikova',NULL,NULL,'15.7.2009',NULL),
(13,'Jozef','Klasovsky',NULL,5,12,'8.8.2030',NULL),
(14,'Etela','Juhasz','Juhasz',7,8,'3.3.2033',NULL),
(15,'Ignac','Bajza',NULL,NULL,NULL,'2.5.2031','10.11.2068');

create table mib_manzelstvo (
id int not null primary key,
id_on int not null,
id_ona int not null,
dat_od date not null,
dat_do date);

insert into mib_manzelstvo values
(1,3,2,'5.5.2005',NULL),
(2,1,4,'7.4.2028','5.7.2029'),
(3,7,8,'4.4.2028',NULL),
(4,10,6,'4.5.2048','6.5.2060'),
(5,10,4,'6.4.2061','1.2.2066'),
(6,5,12,'20.3.2029',NULL),
(7,15,14,'6.8.2055','10.11.2068');

alter table mib_clovek add foreign key mib_id_otec (id_otec) references mib_clovek;
alter table mib_clovek add foreign key mib_id_matka (id_matka) references mib_clovek;
alter table mib_manzelstvo add foreign key mib_id_on (id_on) references mib_clovek;
alter table mib_manzelstvo add foreign key mib_id_ona (id_ona) references mib_clovek;





select c.meno,c.priezvisko from mib_clovek c  where c.id in (select m.id_on from mib_manzelstvo m )


update mib_manzelstvo set id_on=1, id_ona=4 where id_on=4;

select c.meno,c.priezvisko from mib_clovek c  where not(c.id in (select m.id_on from mib_manzelstvo m where m.dat_do is NULL)) and prve_priezv is NULL

select c.meno,c.priezvisko from mib_clovek c  where not(c.id in (select m.id_ona from mib_manzelstvo m where m.dat_do is NULL)) and c.prve_priezv is not null



1, Vypte vetkch ud, ktor s slobodn.

select c.meno,c.priezvisko 
from mib_clovek c  
where 
 not(c.id in (select m.id_on from mib_manzelstvo m where m.dat_do is NULL))
and c.prve_priezv is NULL and c.datum_do is null
union all
select c.meno,c.priezvisko 
from mib_clovek c  
where 
 not(c.id in (select m.id_ona from mib_manzelstvo m where m.dat_do is NULL))
and c.prve_priezv is not null and c.datum_do is null

alebo
(ak ide len o doteraz nezosobasenych)

select c.id,c.meno,c.priezvisko
from mib_clovek c
where 
 c.id not in (select m.id_on from mib_manzelstvo m) and c.datum_do is NULL
and 
 c.id not in (select m.id_ona from mib_manzelstvo m) and c.datum_do is NULL



2, Vypte vetky dvojice muov a ien takch,
   e ena je sesternicou mua.

select s.meno, s.priezvisko, b.meno,b.priezvisko 
from  
 mib_clovek pred, mib_clovek p1,mib_clovek p2, mib_clovek s, mib_clovek b 
where pred.prve_priezv is null and p1.id_otec=pred.id and p2.id_otec=pred.id and p1.id>p2.id
and (s.id_otec=p1.id or s.id_matka=p1.id) and (p2.id=b.id_otec or p2.id=b.id_matka) and
(( s.prve_priezv is null and b.prve_priezv is not null) or (s.prve_priezv is not null and b.prve_priezv is null)) and s.datum_do is null and b.datum_do is null ;



3, Vypte vetkch ud, ktor boli rozveden

select distinct c1.meno, c1.priezvisko
from  
 mib_clovek c1, mib_manzelstvo m 
where 

( c1.id=m.id_on 
or
 c1.id=m.id_ona)
and 
 m.dat_do is not null
and c1.datum_do is null

4, Njdite enu, ktor rodila v najniom veku

select c.meno, c.priezvisko, count(*)from mib_clovek c, mib_clovek d
where c.id=d.id_matka and d.datum_od-c.datum_od=(select min(d.datum_od-c.datum_od) from mib_clovek c, mib_clovek d
where c.id=d.id_matka)

5, Vypte v. vdovcov (t.j. ud, ktorm zomrel posledn manel)

select c2.meno, c2.priezvisko
from mib_manzelstvo m,mib_clovek c1, mib_clovek c2
where m.id_on=c1.id and m.id_ona=c2.id and (m.dat_do = c1.datum_do and c2.datum_do is null) 
union
select c1.meno, c1.priezvisko 
from mib_manzelstvo m,mib_clovek c1, mib_clovek c2
where m.id_on=c1.id and m.id_ona=c2.id and (m.dat_do = c2.datum_do and c1.datum_do is null)

6,Vypte vetky dvojice osb takch, e druh je muskm predkom prvej.

with
pom (id, id_pot) as(
select o.id,p.id from mib_clovek o, mib_clovek p 
where o.id=p.id_otec
union all
select p.id,c.id
from mib_clovek c, pom p
where p.id_pot=c.id_otec or p.id_pot=c.id_matka
)
select * from pom


7, Pre kadho lovek slo jeho genercie po mei (udia, ktorch otec nie je v databze zaregistrovan maj slo 1)

with
pom (id,gen) as
(
select c.id,1 from mib_clovek c where c.id_otec is null
union all
select c.id,p.gen+1
from pom p, mib_clovek c
where p.id =c.id_otec
)
select * from pom

