CREATE TABLE ubytovani(ATTR_0 VARCHAR2(30),ATTR_1 VARCHAR2(30));
INSERT INTO ubytovani VALUES('Javor','Zelezna Ruda');
INSERT INTO ubytovani VALUES('Javorina','Strbske Pleso');
INSERT INTO ubytovani VALUES('Atrium','Praha');
INSERT INTO ubytovani VALUES('Ceasar','Rim');
INSERT INTO ubytovani VALUES('Exculibur','Long Beach');
CREATE TABLE hory(ATTR_0 VARCHAR2(30));
INSERT INTO hory VALUES('Zelezna Ruda');
INSERT INTO hory VALUES('Rocky Mountains');
INSERT INTO hory VALUES('Kietzbiel');
INSERT INTO hory VALUES('Strbske Pleso');
INSERT INTO hory VALUES('Harachov');
CREATE TABLE pamatky(ATTR_0 VARCHAR2(30));
INSERT INTO pamatky VALUES('Praha');
INSERT INTO pamatky VALUES('Rim');
CREATE TABLE plaz(ATTR_0 VARCHAR2(30));
INSERT INTO plaz VALUES('Long Beach');
CREATE TABLE spojeni(ATTR_0 VARCHAR2(30),ATTR_1 VARCHAR2(30));
INSERT INTO spojeni VALUES('Praha','Harachov');
INSERT INTO spojeni VALUES('Long Beach','Detroit');
INSERT INTO spojeni VALUES('Detroit','Rocky Mountains');
INSERT INTO spojeni VALUES('Praha','Bratislava');
INSERT INTO spojeni VALUES('Strbske Pleso','Bratislava');
INSERT INTO spojeni VALUES('Praha','Zurych');
INSERT INTO spojeni VALUES('Kietzbiel','Zurych');
INSERT INTO spojeni VALUES('Frankfurt','Praha');
INSERT INTO spojeni VALUES('Frankfurt','Long Beach');
INSERT INTO spojeni VALUES('Frankfurt','Rim');
INSERT INTO spojeni VALUES('Plzen','Zelezna Ruda');
INSERT INTO spojeni VALUES('Plzen','Praha');
CREATE TABLE teplota(ATTR_0 VARCHAR2(30),ATTR_1 NUMBER);
INSERT INTO teplota VALUES('Zelezna Ruda',12);
INSERT INTO teplota VALUES('Rim',25);
INSERT INTO teplota VALUES('Long Beach',28);
CREATE VIEW teple_podnebi AS
SELECT DISTINCT teplota0.ATTR_0 ATTR_0
FROM teplota teplota0
WHERE teplota0.ATTR_1>24;

CREATE VIEW spoj AS
SELECT DISTINCT spojeni0.ATTR_0 ATTR_0,spojeni0.ATTR_1 ATTR_1
FROM spojeni spojeni0
UNION
SELECT DISTINCT spojeni0.ATTR_1 ATTR_0,spojeni0.ATTR_0 ATTR_1
FROM spojeni spojeni0;

CREATE TABLE doprava(ATTR_0 VARCHAR2(30),ATTR_1 VARCHAR2(30));
INSERT INTO doprava
SELECT DISTINCT spoj0.ATTR_0,spoj0.ATTR_1
FROM spoj spoj0;

DECLARE
  c NUMBER;
BEGIN
LOOP
c := 0;
INSERT INTO doprava
SELECT DISTINCT spoj0.ATTR_0,doprava0.ATTR_1
FROM spoj spoj0,doprava doprava0
WHERE spoj0.ATTR_1=doprava0.ATTR_0
AND (spoj0.ATTR_0,doprava0.ATTR_1) NOT IN (SELECT ATTR_0,ATTR_1 FROM doprava);
c := c + SQL%ROWCOUNT;
COMMIT;
IF c = 0 THEN EXIT; END IF;
END LOOP;
END;
/
CREATE VIEW vyhovujici AS
SELECT DISTINCT 'koupani' ATTR_0,plaz0.ATTR_0 ATTR_1
FROM plaz plaz0,teple_podnebi teple_podnebi0
WHERE plaz0.ATTR_0=teple_podnebi0.ATTR_0
UNION
SELECT DISTINCT 'lyzovani' ATTR_0,hory0.ATTR_0 ATTR_1
FROM hory hory0
UNION
SELECT DISTINCT 'prohlizeni pamatek' ATTR_0,pamatky0.ATTR_0 ATTR_1
FROM pamatky pamatky0;

CREATE VIEW dovolena AS
SELECT DISTINCT vyhovujici0.ATTR_0 ATTR_0,doprava0.ATTR_0 ATTR_1,vyhovujici0.ATTR_1 ATTR_2,ubytovani0.ATTR_0 ATTR_3
FROM vyhovujici vyhovujici0,doprava doprava0,ubytovani ubytovani0
WHERE vyhovujici0.ATTR_1=doprava0.ATTR_1
AND vyhovujici0.ATTR_1=ubytovani0.ATTR_1;

