-- Ukol c.1: Oracle a prazdny retezec
-- Zkuste si porovnat '' a NULL
SELECT 'prazdny retezec je null' FROM dual WHERE '' IS NULL;
-- Ukol c.2: Vypiste id knih, ktere maji nejaky vytisk
SELECT DISTINCT kniha_id FROM vytisky;
-- Agregacni funkce a GROUP BY
-- Dokumentace
-- SELECT atribut, SUM(atribut2) FROM tabulka WHERE ... GROUP BY atribut;
-- Ukol c.3: Vypiste pocty vytisku pro jednotlive tituly (kniha_id, pocet jejich vytisku)
-- a/ Dale jej upravte tak, ze pocty vypise i pro knihy, ktere nemaji vytisk
-- b/ Vysledek usporadejte podle poctu vytisku od nejvetsiho po nejmensi
-- Dokumentace
-- c/ Vysledek upravte tak, ze misto kniha_id vypisete jeji nazev.
-- d/ Vysledek upravte tak, ze vypiste pouze knihy, ktere maji alespon 2 vytisky.
-- Ukol c.4: Vypiste celkovy pocet vytisku, ktere mame, a datum porizeni prvniho z nich.
SELECT COUNT(*) FROM vytisky;
-- Specialita od Oracle - vnorovani agregacnich funkci
-- Napr. SELECT MAX(SUM(atribut2)) FROM tabulka GROUP BY attribut;
-- Ukol c.5: Vypiste nazev knihy, ktera ma nejvice vytisku
-- Mnozinove operace nad relacemi
-- UNION - sjednoceni, UNION ALL - sjednoceni s opakovanim prvku
-- INTERSECT - prunik
-- MINUS - rozdil
-- Ukol c.6: Vypiste id knih, ktere nemaji ani jeden vytisk
-- Mnozinove operatory
-- atribut IN (list) - je prvkem mnoziny, analogicky NOT IN
-- Obecne: atribut <op> ANY (list), misto ANY lze pouzit SOME a ALL
-- Napr: atribut = ANY(list), atribut != ALL(list)
-- Ukol c.8: Vypiste nazvy knih, ktere maji alespon jeden vytisk
-- NOT IN - pozor na hodnoty NULL v seznamu!
SQL> SELECT id,nazev FROM knihy WHERE id NOT IN (2,3,NULL);
no rows selected
-- Toto je z duvodu, ze podminka NOT IN bude vyhodnocovana jako: id != 2 AND id != 3 AND id != NULL
-- V pripade pouziti vnoreneho prikazu SELECT je situace stejna!!! Za domaci cviceni si vyzkousejte.
-- Je-li v pripade ANY mnozina prazdna, je vysledek FALSE
SQL> SELECT nazev FROM knihy WHERE vydana >= ANY(SELECT vydana FROM knihy WHERE isbn='000-00');
-- Ukol c.9: Vypiste pocty vytisku pro kazdou knihu a take celkovy pocet vytisku.
KNIHA_ID COUNT(*)
---------- ----------
34 3
11 1
36 2
6
-- Tip: Lze vyuzit UNION...
-- Analyticke rozsireni pro GROUP BY
-- Dokumentace
-- Ukol c.10: Vypiste pocty vytisku pro kazdou knihu a take celkovy pocet vytisku.
-- Pouzijte ROLLUP...
SELECT kniha_id, COUNT(*) FROM vytisky GROUP BY ROLLUP(kniha_id);
-- Analyticke rozsireni OVER (PARTITION BY ...) -- misto GROUP BY
-- Dokumentace
-- Ukol c.11: Pro kazdy vytisk vypiste jeho id, id prislusne knihy, datum porizeni, vcetne
-- data porizeni prvniho vytisku stejne knihy a data porizeni posledniho vytisku dane knihy.
VYTISK_ID KNIHA_ID PORIZENO PRVNI POSLEDNI
---------- ---------- -------- -------- --------
1 11 07.03.12 07.03.12 07.03.12
21 34 02.03.12 02.03.12 12.03.12
22 34 07.03.12 02.03.12 12.03.12
23 34 12.03.12 02.03.12 12.03.12
24 36 14.10.11 14.10.11 09.03.12
25 36 09.03.12 14.10.11 09.03.12
SELECT vytisk_id, kniha_id, porizeno, MIN(porizeno) OVER (PARTITION BY kniha_id) AS prvni,
MAX(porizeno) OVER (PARTITION BY kniha_id) AS posledni FROM vytisky;
-- Hierarchicke dotazy - Dokumentace
-- Mame tabulku firma s organizacni 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" a modifikator
-- "PRIOR" specifikuje vazbu predchudce-naslednik, a pripadne klausule "START WITH"
-- specifikuje, ktera vetev nas zajima.
DROP TABLE firma;
CREATE TABLE firma (
id NUMBER PRIMARY KEY,
jmeno VARCHAR2(20),
mgr NUMBER REFERENCES firma);
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);
SQL> SELECT * FROM firma;
ID JMENO MGR
---------- -------------------- ----------
1 Zdenek
2 Jirka 1
3 Jirinka 1
4 Jitka 2
-- Ukol c.12: Vypiste pro vsechny zamenstnance jejich jmeno a jmeno jejich nadrizeneho
-- Tip: Pouzijte JOIN relaci a nejlepe vnejsi spojeni.
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(+);
-- Vyuziti hierachickeho dotazu: vazba predchudce-naslednik na sloupce mgr a id
-- Vypis vsechny sefy daneho zamestnance (id=4) (LEVEL obsahuje aktualni hodnotu "zanoreni"):
SELECT LEVEL, id AS "Zamestnanec", mgr AS "Sef"
FROM firma CONNECT BY PRIOR mgr=id START WITH id=4;
LEVEL Zamestnanec Sef
---------- ---------- ----------
1 4 2
2 2 1
3 1
-- Opacny smer hierarchie: zaciname vedoucim a smerujeme k jeho podrizenym
SELECT LEVEL, mgr AS "Zamestnanec", id AS "Podrizeny"
FROM firma CONNECT BY PRIOR id=mgr START WITH mgr=1;
LEVEL Zamestnanec Podrizeny
---------- ---------- ----------
1 1 2
2 2 4
1 1 3
-- Ukol c.13: Vypiste jmena nadrizenych zamestnance Jitka.
-- Vyuzijte hierarchicky dotaz.
LEVEL JMENO
---------- --------------------
1 Jitka
2 Jirka
3 Zdenek
-- Vypis sefy pro vsechny zamestnance (vsechny "vetve")
SELECT level, id AS "Zamestnanec", mgr AS "Sef" FROM firma CONNECT BY PRIOR mgr=id;
LEVEL Zamestnanec Sef
---------- ---------- ----------
1 1
1 2 1
2 1
1 3 1
2 1
1 4 2
2 2 1
3 1
-- V hierarchickych dotazech lze pouzit operator CONNECT_BY_ROOT, ktery
-- vraci hodnotu atributu z korenoveho (prvniho) radku hierarchie:
SELECT LEVEL, CONNECT_BY_ROOT id AS "Koren", id AS "Zamestnanec", mgr AS "Sef"
FROM firma CONNECT BY PRIOR mgr=id;
LEVEL Koren Zamestnanec Sef
---------- ---------- ---------- ----------
1 1 1
1 2 2 1
2 2 1
1 3 3 1
2 3 1
1 4 4 2
2 4 2 1
3 4 1
-- Pro vypsani cele cesty od listu do korene (prip. obracene) slouzi funkce SYS_CONNECT_BY_PATH:
-- Pretypovani na VARCHAR(20) je pouze na zkraceni vypisu a zamezeni zalamovani radku.
SELECT LEVEL, id AS "Zamestnanec", mgr AS "Sef", CAST(SYS_CONNECT_BY_PATH(id, '/') AS VARCHAR(20)) "Hierarchie"
FROM firma CONNECT BY PRIOR id=mgr START WITH id=1;
LEVEL Zamestnanec Sef Hierarchie
---------- ----------- ---------- --------------------
1 1 /1
2 2 1 /1/2
3 4 2 /1/2/4
2 3 1 /1/3