CREATE TABLE soudce (
id INTEGER NOT NULL,
jmeno VARCHAR2(30 CHAR) NOT NULL
);

ALTER TABLE soudce ADD CONSTRAINT soudce_pk PRIMARY KEY ( id );

CREATE TABLE trest (
id INTEGER NOT NULL,
delka_trestu INTEGER NOT NULL,
datum_nastupu DATE NOT NULL,
soudce_id INTEGER NOT NULL,
vrah_id INTEGER NOT NULL,
veznice_id INTEGER NOT NULL
);

ALTER TABLE trest ADD CONSTRAINT trest_pk PRIMARY KEY ( id );

CREATE TABLE veznice (
id INTEGER NOT NULL,
nazev VARCHAR2(30 CHAR) NOT NULL,
kapacita VARCHAR2(30 CHAR) NOT NULL
);

ALTER TABLE veznice ADD CONSTRAINT veznice_pk PRIMARY KEY ( id );

CREATE TABLE vrah (
id INTEGER NOT NULL,
jmeno VARCHAR2(30 CHAR) NOT NULL
);

ALTER TABLE vrah ADD CONSTRAINT vrah_pk PRIMARY KEY ( id );

ALTER TABLE trest
ADD CONSTRAINT trest_soudce_fk FOREIGN KEY ( soudce_id )
REFERENCES soudce ( id );

ALTER TABLE trest
ADD CONSTRAINT trest_veznice_fk FOREIGN KEY ( veznice_id )
REFERENCES veznice ( id );

ALTER TABLE trest
ADD CONSTRAINT trest_vrah_fk FOREIGN KEY ( vrah_id )
REFERENCES vrah ( id );

Insert into Soudce(ID, Jmeno) values (0,'karel');
Insert into Soudce(ID, Jmeno) values (1,'pepa');
Insert into Soudce(ID, Jmeno) values (2,'anezka');
Insert into Soudce(ID, Jmeno) values (3,'urvalek');

Insert into Veznice(ID, Nazev,KAPACITA) values (0,'bory',30);
Insert into Veznice(ID, Nazev,KAPACITA) values (1,'pankrac',300);
Insert into Veznice(ID, Nazev,KAPACITA) values (2,'mirov',600);
Insert into Veznice(ID, Nazev,KAPACITA) values (3,'lety',600);

Insert into Vrah(ID, Jmeno) values (0,'kajinek');
Insert into Vrah(ID, Jmeno) values (1,'roubal');
Insert into Vrah(ID, Jmeno) values (2,'cerny');
Insert into Vrah(ID, Jmeno) values (3,'breburda');
Insert into Vrah(ID, Jmeno) values (4,'hubka');

alter session set NLS_DATE_FORMAT = 'YYYYMMDD';
Insert into Trest(ID,DELKA_TRESTU,Datum_nastupu,SOUDCE_ID,VRAH_ID,VEZNICE_ID) values (0,'10','20101005',0,0,0);
Insert into Trest(ID,DELKA_TRESTU,Datum_nastupu,SOUDCE_ID,VRAH_ID,VEZNICE_ID) values (1,'10','20001005',0,0,1);
Insert into Trest(ID,DELKA_TRESTU,Datum_nastupu,SOUDCE_ID,VRAH_ID,VEZNICE_ID) values (2,'8','20131005',1,1,1);
Insert into Trest(ID,DELKA_TRESTU,Datum_nastupu,SOUDCE_ID,VRAH_ID,VEZNICE_ID) values (3,'6','20001005',2,2,2);
Insert into Trest(ID,DELKA_TRESTU,Datum_nastupu,SOUDCE_ID,VRAH_ID,VEZNICE_ID) values (4,2,'19901005',1,0,2);
Insert into Trest(ID,DELKA_TRESTU,Datum_nastupu,SOUDCE_ID,VRAH_ID,VEZNICE_ID) values (5,'2','19931005',2,0,3);
Insert into Trest(ID,DELKA_TRESTU,Datum_nastupu,SOUDCE_ID,VRAH_ID,VEZNICE_ID) values (6,'2','19961005',3,0,3);

SELECT 
  soudce.jmeno AS jmena_soudcu,
  NULL AS jmena_vrahu
FROM soudce
LEFT JOIN trest ON soudce.id = trest.soudce_id
WHERE trest.id IS NULL

UNION

SELECT 
  NULL AS jmena_soudcu,
  vrah.jmeno AS jmena_vrahu
FROM vrah
LEFT JOIN trest ON vrah.id = trest.vrah_id
WHERE trest.id IS NULL;

SELECT 
  vrah.jmeno,
  veznice.nazev
FROM vrah
INNER JOIN trest ON vrah.id = trest.vrah_id
INNER JOIN veznice ON trest.veznice_id = veznice.id
WHERE trest.datum_nastupu = (
  SELECT MAX(trest.datum_nastupu)
  FROM trest
  WHERE trest.vrah_id = vrah.id
);

-- A: Věznice, které jsou momentálně prázdné (nemají aktuálně žádného vězně)
SELECT veznice.nazev
FROM veznice
MINUS
SELECT veznice.nazev
FROM veznice veznice
JOIN trest ON veznice.id = trest.veznice_id
WHERE ADD_MONTHS(trest.datum_nastupu, trest.delka_trestu * 12) > CURRENT_DATE;

-- B: Věznice, které byly vždy prázdné (nikdy v nich nebyl žádný vězeň)
SELECT veznice.nazev
FROM veznice
WHERE NOT EXISTS (
  SELECT 1
  FROM trest
  WHERE trest.veznice_id = veznice.id
);

SELECT vrah.jmeno
FROM vrah
JOIN trest ON vrah.id = trest.vrah_id
JOIN soudce ON trest.soudce_id = soudce.id
JOIN veznice ON trest.veznice_id = veznice.id
GROUP BY vrah.id, vrah.jmeno
HAVING COUNT(DISTINCT soudce.id) = (SELECT COUNT(*) FROM soudce) AND COUNT(DISTINCT veznice.id) = (SELECT COUNT(*) FROM veznice);
