--vsikni kteri nebyli odsouzeni
select * from vrah left join trest on vrah.ID = trest.vrah_id where trest.id is null;
--vsichni soudci co nikoho neodsoudili
select * from soudce left join trest on soudce.id = trest.soudce_id where trest.id is null;

select jmeno as vrah, null as soudce from vrah left join trest on vrah.ID = trest.vrah_id where trest.id is null
union
select null as vrah, jmeno as soudce from soudce left join trest on soudce.id = trest.soudce_id where trest.id is null;

--napiste posledni veznici kde vrah pobyval
select jmeno, nazev from vrah inner join trest on vrah.id = trest.vrah_id inner join veznice on trest.veznice_id = veznice.id where (select max(trest.datum_nastupu)from trest  where trest.vrah_id = vrah.id group by trest.vrah_id) = datum_nastupu;

--veznice kteje jsou ted prazdne 
select id from veznice
minus
select veznice_id from trest where add_months (datum_nastupu, delka_trestu * 12) > (select current_date from dual);

--veznice ktere byli vzdy prazdne
select id from veznice
minus
select veznice_id from trest;

-- A, B sjednoceno B, A
select id from veznice
minus
select veznice_id from trest 
where add_months(datum_nastupu, delka_trestu * 12) > (select current_date from dual)
union
select id from veznice
minus
select veznice_id from trest;