-- 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