-----------------------------------------------------------
1. Vsetky dvojice vlastnych sesternic
-----------------------------------------------------------
SELECT sest1.meno || ' ' || sest1.priezvisko || ' - ' || sest2.meno || ' ' || sest2.priezvisko
FROM clovek sest1, clovek sest2, clovek rod1, clovek rod2
WHERE
  ((sest1.id_otec=rod1.id) OR (sest1.id_matka=rod1.id))
  AND
  ((sest2.id_otec=rod2.id) OR (sest2.id_matka=rod2.id))
  AND
  ((rod1.id_otec=rod2.id_otec) AND (rod2.id_matka=rod2.id_matka))
  AND sest1.pohlavie='Z' AND sest2.pohlavie='Z'
  AND sest1.id<sest2.id
--------------------------------------------------------------
2. Pre kazdeho cloveka jeho vek v rokoch (u mrtvych pri smrti)
--------------------------------------------------------------
SELECT 
  c.meno, 
  c.priezvisko, 
  CASE 
    WHEN c.datum_do IS NULL THEN YEAR(CURRENT DATE)-YEAR(c.datum_od)
    ELSE YEAR(c.datum_do) - YEAR(c.datum_od)
  END
FROM clovek c
--------------------------------------------------------------
3. Roztriedte ukoncene manzelstva podla toho, ci boli rozvedene 
   alebo zanikli smrtou niektoreho manzela
--------------------------------------------------------------
SELECT
  c1.meno, c1.priezvisko,
  c2.meno, c2.priezvisko,
  CASE
    WHEN 
      ((c1.datum_do IS NOT NULL) OR (c2.datum_do IS NOT NULL)) 
      AND
      (c1.datum_do=m.datum_do) OR c2.datum_do=m.datum_do) THEN 'zanikli smrtou'
    ELSE 'rozvedene'
  END AS ukoncene
FROM rn_manzelstvo m
JOIN rn_clovek c1 ON c1.id=m.id_manzel
JOIN rn_clovek c2 ON c2.id=m.id_manzelka
WHERE m.datum_do IS NOT NULL
ORDER BY 5

-----------------------------------------------------------
5. Polygamisti
-----------------------------------------------------------
WITH muzi(idpolyg) AS (
  SELECT DISTINCT m1.id_manzel
  FROM rn_manzelstvo m1, rn_manzelstvo m2
  WHERE m1.id_manzel=m2.id_manzel   
    AND 
    (
      (m1.datum_od BETWEEN m2.datum_od AND m2.datum_do)
      OR 
      (m2.datum_od BETWEEN m1.datum_od AND m1.datum_do)
      OR
      (m1.datum_od > m2.datum_od AND m2.datum_do IS NULL)
      OR
      (m2.datum_od > m1.datum_od AND m1.datum_do IS NULL)
    )
   AND m1.id_manzelka<>m2.id_manzelka
)
SELECT c.meno, c.priezvisko
FROM muzi
JOIN rn_clovek c ON c.id=idpolyg

----------------------------------------------------------------
6. Vsetky dvojice osob takych, ze druha je muzskym predkom prvej
----------------------------------------------------------------


WITH cl(id, idpotomok) AS (
  (SELECT c1.id, c2.id
  FROM rn_clovek c1, rn_clovek c2
  WHERE (c2.id_otec=c1.id AND c1.id_otec IS NULL) or (c2.id_matka=c1.id AND c1.id_matka IS NULL)
  )
  UNION ALL
  (  
  SELECT cl.idpotomok, c.id
  FROM rn_clovek c, cl
  WHERE cl.idpotomok=c.id_otec or cl.idpotomok=c.id_matka
  )
  UNION ALL
  (  
  SELECT cl.id, c.id
  FROM rn_clovek c, cl
  WHERE cl.idpotomok=c.id_otec or cl.idpotomok=c.id_matka
  )

),
temp(id,idpotomok) AS(
  SELECT DISTINCT id, idpotomok
  FROM cl
)
SELECT potomok.meno, potomok.priezvisko, otec.meno, otec.priezvisko
FROM temp 
JOIN rn_clovek otec ON otec.id=temp.id
JOIN rn_clovek potomok ON potomok.id=temp.idpotomok
WHERE otec.pohlavie='M'

---------------------------------
7. Postupnost predkov po praslici
---------------------------------
WITH cl(id, idpotomok, popis) AS (
  SELECT c1.id, c2.id, c2.meno || ' ' || c2.priezvisko
  FROM rn_clovek c1, rn_clovek c2
  WHERE (c2.id_matka=c1.id AND c1.id_matka IS NULL) 
  AND c2.pohlavie='Z'

  UNION ALL

  SELECT cl.id, c.id, cl.popis || '->' || c.meno || ' ' || c.priezvisko
  FROM rn_clovek c, cl
  WHERE cl.idpotomok=c.id_matka
  AND c.pohlavie='Z'
)
SELECT c2.meno || ' ' || c2.priezvisko, c.meno || ' ' || c.priezvisko || '->' || cl.popis
from cl
join rn_clovek c on c.id=cl.id
join rn_clovek c2 on c2.id=cl.idpotomok
where c.pohlavie='Z'

UNION ALL

SELECT c.meno || ' ' || c.priezvisko, c.meno || ' ' || c.priezvisko
FROM rn_clovek c
WHERE c.id NOT IN (SELECT cl.idpotomok FROM cl)
AND c.pohlavie='Z'