-- 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.
SQL> SELECT 1+1 FROM dual;
1+1
----------
2
SQL> DESC dual;
Name Null? Type
----------------------------------------- -------- ----------------------------
DUMMY VARCHAR2(1)
-- chyba: do tabulky dual nemame zapisova prava (je umistena v systemove databazi)
SQL> INSERT INTO dual VALUES ('A');
INSERT INTO dual VALUES ('A')
*
ERROR at line 1:
ORA-01031: insufficient privileges
-- Tabulka dual neni v nasi databazi, takze pokud potrebujeme mit v nasi
-- databazi vlastni tabulku s nazvem dual nic nam v tom nebrani.
SQL> CREATE TABLE dual (a number);
SQL> INSERT INTO dual VALUES (1);
SQL> SELECT * FROM dual;
A
----------
1
SQL> DROP TABLE dual;
-- 2) Spojeni tabulek
-- Mame tabulku ucitelu, tabulku predmetu a tabulku kdo, co uci. Chceme
-- vypsat textove ktery ucitel uci ktery predmet:
SQL> DROP TABLE ucitele;
SQL> DROP TABLE predmety;
SQL> DROP TABLE uci;
SQL> CREATE TABLE ucitele (id NUMBER PRIMARY KEY, jmeno VARCHAR2(20));
SQL> INSERT INTO ucitele VALUES (1,'Matyas');
SQL> INSERT INTO ucitele VALUES (2,'Riha');
SQL> INSERT INTO ucitele VALUES (3,'Svenda');
SQL> CREATE TABLE predmety (id NUMBER PRIMARY KEY, jmeno VARCHAR2(20));
SQL> INSERT INTO predmety VALUES (1,'Architektura RDBS');
SQL> INSERT INTO predmety VALUES (2,'Projekt z DBS');
SQL> INSERT INTO predmety VALUES (3,'Autentizace');
SQL> INSERT INTO predmety VALUES (4,'Zonglovani');
SQL> CREATE TABLE uci (kdo NUMBER, co NUMBER);
SQL> INSERT INTO uci VALUES (2,1);
SQL> INSERT INTO uci VALUES (2,3);
SQL> 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.
SQL> SELECT ucitele.jmeno, predmety.jmeno AS Predmet FROM predmety, ucitele, uci WHERE
ucitele.id=uci.kdo AND uci.co=predmety.id;
JMENO PREDMET
-------------------- --------------------
Riha Architektura RDBS
Riha Autentizace
Matyas Autentizace
-- 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
SQL> SELECT ucitele.jmeno, uci.co FROM ucitele, uci WHERE ucitele.id =
uci.kdo(+);
JMENO CO
-------------------- ----------
Matyas 3
Riha 1
Riha 3
Svenda
-- 4B) vypis identifikaci ucitele, kteri uci dany predmet
-- zobrazi se i predmet, ktery nikdo neuci.
SQL> SELECT predmety.jmeno, uci.kdo FROM predmety, uci WHERE predmety.id =
uci.co(+);
JMENO KDO
-------------------- ----------
Architektura RDBS 2
Projekt z DBS
Autentizace 2
Autentizace 1
Zonglovani
-- doplneni dotazu 4A) o nalezeni jmen predmetu dle identifikaci
SQL> SELECT ucitele.jmeno, predmety.jmeno FROM ucitele, uci, predmety WHERE
ucitele.id = uci.kdo(+) AND uci.co=predmety.id(+);
JMENO JMENO
-------------------- --------------------
Matyas Autentizace
Riha Architektura RDBS
Riha Autentizace
Svenda
-- doplneni dotazu 4B) o nalezeni jmen ucitelu dle identifikace
SQL> SELECT predmety.jmeno, ucitele.jmeno FROM ucitele, predmety, uci WHERE
predmety.id = uci.co(+) AND uci.kdo=ucitele.id(+);
JMENO JMENO
-------------------- --------------------
Architektura RDBS Riha
Projekt z DBS
Autentizace Riha
Autentizace Matyas
Zonglovani
-- 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.
SQL> DROP TABLE firma;
SQL> CREATE TABLE firma (id NUMBER, jmeno VARCHAR2(20), mgr NUMBER);
SQL> INSERT INTO firma VALUES (1,'Zdenek',NULL);
SQL> INSERT INTO firma VALUES (2,'Jirka',1);
SQL> INSERT INTO firma VALUES (3,'Jirinka',1);
SQL> INSERT INTO firma VALUES (4,'Jitka',2);
SQL> SELECT * FROM firma;
ID JMENO MGR
---------- -------------------- ----------
1 Zdenek
2 Jirka 1
3 Jirinka 1
4 Jitka 2
-- nadrizeny nadrizeneho zamestnance (bez pouziti hierarchickych dotazu)
SQL> 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;
Zamestnanec Sef Sefa sef
-------------------- -------------------- --------------------
Jitka Jirka Zdenek
SQL> 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(+);
Zamestnanec Sef Sefa sef
-------------------- -------------------- --------------------
Jitka Jirka Zdenek
Jirka Zdenek
Zdenek
Jirinka Zdenek
SQL> 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;
PodPodrizeny Podrizeny Sef
-------------------- -------------------- --------------------
Jitka Jirka Zdenek
Jirinka Zdenek
Jitka Jirka
Jirinka
Jitka
-- 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
SQL> SELECT LEVEL, id AS "Podrizeny", mgr AS "Sef" FROM firma CONNECT BY PRIOR mgr=id START WITH id=4;
LEVEL Podrizeny Sef
---------- ---------- ----------
1 4 2
2 2 1
3 1
-- opacny smer hierarchie. zaciname vedoucim a smerujeme k jeho podrizenym
SQL> SELECT LEVEL, mgr AS "Sef", id AS "Podrizeny" FROM firma CONNECT BY PRIOR id=mgr START WITH mgr=1;
LEVEL Sef Podrizeny
---------- ---------- ----------
1 1 2
2 2 4
1 1 3
-- vypis vsech dotazu pro vsechny "vetve" (tedy pro vsechny zamestnance)
SQL> SELECT level, id AS "Podrizeny", mgr AS "Sef" FROM firma CONNECT BY PRIOR mgr=id;
LEVEL Podrizeny Sef
---------- ---------- ----------
1 1
1 2 1
2 1
1 3 1
2 1
1 4 2
2 2 1
3 1