clovek a jeho synovia
select t1.id,t1.meno,t2.id,t2.meno from mib_clen t1, mib_clen t2 where t1.id_otec=t2.id

clovek a pocet jeho synov
select t1.id,t1.meno,count(*) from mib_clen t1, mib_clen t2 where t1.id=t2.id_otec group by t1.meno,t1.id

clovek a jeho vnuci
select t1.id,t1.meno,t3.id,t3.meno from mib_clen t1, mib_clen t2, mib_clen t3 where t1.id=t2.id_otec and t2.id=t3.id_otec

dvojice bratov
select t2.id,t2.meno,t4.id,t4.meno from mib_clen t1, mib_clen t2, mib_clen t3, mib_clen t4 
where t1.id=t2.id_otec and t3.id=t4.id_otec and t4.id_otec=t2.id_otec and t2.id < t4.id 

mrtvy a jeho vek pri smrti (v rokoch)
select c1.id,c1.meno,year(c1.datum_do-c1.datum_od)
from mib_clen c1
where c1.datum_do is  not null

clovek a jeho vek (v rokoch) (u mrtvych vek pri smrti)
select c1.id,c1.meno,year(c1.datum_do-c1.datum_od)
from mib_clen c1
where c1.datum_do is  not null
union all
select c1.id,c1.meno,year('15.5.2002'-c1.datum_od)
from mib_clen c1
where c1.datum_do is  null

clovek a jeho vek (v dnoch) (u mrtvych vek pri smrti)
select c1.id,c1.meno,days(c1.datum_do)-days(c1.datum_od)
from mib_clen c1
where c1.datum_do is  not null
union all
select c1.id,c1.meno,days('15.5.2002')-days(c1.datum_od)
from mib_clen c1
where c1.datum_do is  null


rekurzia, vyber potomkov
with
mib_a(id, id_pot) as
(
	select t1.id_otec,t1.id
	from mib_clen t1
union all
	select t1.id,t2.id
	from mib_a t1, mib_clen t2 where t1.id_pot=t2.id_otec
)
select t1.id,t1.meno,t2.id,t2.meno from mib_a t, mib_clen t1, mib_clen t2 where t.id =t1.id and t.id_pot= t2.id

rekurzia, uroven jedincov v hierarchii rodokmena
with
tab (id,cgen) as (
select c.id,1 
from mib_clen c
where c.id_otec is null
union all
select c.id,t.cgen+1
from mib_clen c, tab t
where t.id = c.id_otec )
select c.id,c.meno,t.cgen
from mib_clen c, tab t
where c.id=t.id