-- 1) Tabulka dual:
-- Syntaxe oraclu nedovoluje pouziti prikazu "select" bez klauzule "from"
-- (napr. MySQL neco takoveho povoluje). Prikaz "select 1+1" je syntakticky
-- nespravny. Chceme-li zapsat prikaz bez pristupu dat k tabulce muzeme uvest
-- tabulku dual, ke ktere mame automaticky pristup aniz bychom ji vytvareli.
SELECT 1+1 FROM dual;
DESC dual;

-- chyba: do tabulky dual nemame zapisova prava (je umistena v systemove databazi) 
INSERT INTO dual VALUES ('A');

-- Tabulka dual neni v nasi databazi, takze pokud potrebujeme mit v nasi
-- databazi vlastni tabulku s nazvem dual nic nam v tom nebrani.
CREATE TABLE dual (a number);
INSERT INTO dual VALUES (1);
SELECT * FROM dual;

DROP TABLE dual;


-- 2) Spojeni tabulek
-- Mame tabulku ucitelu, tabulku predmetu a tabulku kdo, co uci. Chceme
-- vypsat textove ktery ucitel uci ktery predmet:
DROP TABLE ucitele;
DROP TABLE predmety;
DROP TABLE uci;

CREATE TABLE ucitele (id NUMBER PRIMARY KEY, jmeno VARCHAR2(20));
INSERT INTO ucitele VALUES (1,'Matyas');
INSERT INTO ucitele VALUES (2,'Riha');
INSERT INTO ucitele VALUES (3,'Svenda');

CREATE TABLE predmety (id NUMBER PRIMARY KEY, jmeno VARCHAR2(20));
INSERT INTO predmety VALUES (1,'Architektura RDBS');
INSERT INTO predmety VALUES (2,'Projekt z DBS');
INSERT INTO predmety VALUES (3,'Autentizace');
INSERT INTO predmety VALUES (4,'Zonglovani');

CREATE TABLE uci (kdo NUMBER, co NUMBER);
INSERT INTO uci VALUES (2,1);
INSERT INTO uci VALUES (2,3);
INSERT INTO uci VALUES (1,3);

-- 3) JOIN - spojeni ucitele se jmenem vyucovaneho predmetu
-- Pozor: ve vysledku nejsou zobrazeni ucitele, kteri nic neuci ani
-- predmety, ktere nikdo neuci.
SELECT ucitele.jmeno, predmety.jmeno AS Predmet FROM predmety, ucitele, uci WHERE
ucitele.id=uci.kdo AND uci.co=predmety.id;

-- 4) OUTER JOIN - zakladni join nam nevrati takove zaznamy z prvni tabulky, ktere nemaji odpovidaji zaznam (cizi klic) z druhe tabulky a naopak.
-- Resenim je pouzit "levy" (resp. "pravy") join. Levy join prochazi vsechny zaznamy v prvni tabulce (resp. druhe) a hleda k nim odpovidajici zaznamy z druhe (resp. prvni) tabulky. 
-- Pokud odpovidajici zaznam nenalezne, nahradi jej hodnotou NULL. Na vystup tak obdrzime i hodnoty, ktere nemaji v druhe tabulce odpovidajici zaznam.

-- 4A) vypis identifikaci predmetu, ktery uci dany ucitel
-- zobrazi se i ucitele, ktery nic neuci
SELECT ucitele.jmeno, uci.co FROM ucitele, uci WHERE ucitele.id =
uci.kdo(+);
-- 4B) vypis identifikaci ucitele, kteri uci dany predmet
-- zobrazi se i predmet, ktery nikdo neuci.
SELECT predmety.jmeno, uci.kdo FROM predmety, uci WHERE predmety.id =
uci.co(+);

-- doplneni dotazu 4A) o nalezeni jmen predmetu dle identifikaci
SELECT ucitele.jmeno, predmety.jmeno FROM ucitele, uci, predmety WHERE
ucitele.id = uci.kdo(+) AND uci.co=predmety.id(+);

-- doplneni dotazu 4B) o nalezeni jmen ucitelu dle identifikace
SELECT predmety.jmeno, ucitele.jmeno FROM ucitele, predmety, uci WHERE
predmety.id = uci.co(+) AND uci.kdo=ucitele.id(+);

-- 5) Hierarchicke dotazy
-- Mame tabulku firma s oraganizacni strukturou firmy. Mame-li vypsat jmeno
-- zamestnance a jmeno jeho primeho nadrizeneho, jde to lehce. Chceme-li
-- i jmeno nadrizeneho nadrizeneho, je to mozne zapsat pomoci spojeni 3 tabulek
-- firma. Obecne, zname-li maximalni pocet stupnu hierarchie, muzeme je vypsat.
-- Neni-li vsak pocet stupnu omezen urcitou konstantou, nemuzeme pomoci relacni
-- algebry vypsat kompletni hierarchii. Proto je do syntaxe oraclu pridana
-- specialni konstrukce pro praci s hierarchiemi. Klausule "CONNECT BY PRIOR"
-- specifikuje vazbu predchudce-naslednik, a pripadne klausule "START WITH"
-- specifikuje, ktera vetev nas zajima.
DROP TABLE firma;
CREATE TABLE firma (id NUMBER, jmeno VARCHAR2(20), mgr NUMBER);
INSERT INTO firma VALUES (1,'Zdenek',NULL);
INSERT INTO firma VALUES (2,'Jirka',1);
INSERT INTO firma VALUES (3,'Jirinka',1);
INSERT INTO firma VALUES (4,'Jitka',2);
SELECT * FROM firma;

-- nadrizeny nadrizeneho zamestnance (bez pouziti hierarchickych dotazu)
SELECT a.jmeno AS "Zamestnanec", b.jmeno AS "Sef", c.jmeno AS "Sefa sef" FROM firma a, firma b, firma c WHERE a.mgr=b.id AND b.mgr=c.id;
SELECT a.jmeno AS "Zamestnanec", b.jmeno AS "Sef", c.jmeno  AS "Sefa sef" FROM firma a, firma b, firma c WHERE a.mgr=b.id(+) AND b.mgr=c.id(+);
SELECT a.jmeno AS "PodPodrizeny", b.jmeno AS "Podrizeny", c.jmeno AS "Sef" FROM firma a, firma b, firma c WHERE a.mgr(+)=b.id AND b.mgr(+)=c.id;

-- vyuziti hierrachickeho dotazu. Vazba predchudce naslednik na sloupce mgr a id 
-- vyhodnocovani se provede pouze pro zamestnance, ktery ma id = 4 (Jitka)
-- promenna LEVEL obsahuje aktualni hodnotu "zanoreni" dotazu  
SELECT LEVEL, id AS "Podrizeny", mgr AS "Sef" FROM firma CONNECT BY PRIOR mgr=id START WITH id=4;

-- opacny smer hierarchie. zaciname vedoucim a smerujeme k jeho podrizenym
SELECT LEVEL, mgr AS "Sef", id AS "Podrizeny" FROM firma CONNECT BY PRIOR id=mgr START WITH mgr=1;

-- vypis vsech dotazu pro vsechny "vetve" (tedu pro vsechny zamestnance)
SELECT level, id AS "Podrizeny", mgr AS "Sef" FROM firma CONNECT BY PRIOR mgr=id;