-- Operatory:
-- Razeni podle precedence:
-- 1. unarni +,-
-- 2. *,/
-- 3. binarni +,-,||
-- 4. =,!=,<,>,<=,>=,IS NULL, LIKE, BETWEEN, IN
-- 5. NOT
-- 6. AND
-- 7. OR

DROP TABLE zamestnanci;
CREATE TABLE zamestnanci(
	id NUMBER PRIMARY KEY,
	jmeno VARCHAR2(20) NOT NULL,
	plat NUMBER DEFAULT 10000,
	pozn VARCHAR2(20));

DROP TABLE vedouci;
CREATE TABLE vedouci(
	id NUMBER PRIMARY KEY,
	jmeno VARCHAR2(20) NOT NULL);

-- vlozeni zamestnancu s vyplnenou poznamkou
INSERT INTO zamestnanci (id, jmeno, plat, pozn) VALUES (1, 'Michal Merta', 15000, 'Sikovny');
INSERT INTO zamestnanci (id, jmeno, plat, pozn) VALUES (2, 'Petr Mersyn', 10000, 'Jeste sikovnejsi');
-- bez poznamky 
INSERT INTO zamestnanci (id, jmeno, plat) VALUES (3, 'Tomas Jedno', 9000);

INSERT INTO vedouci (id, jmeno) VALUES (1, 'Tomas Jedno');
INSERT INTO vedouci (id, jmeno) VALUES (2, 'Michal David');
INSERT INTO vedouci (id, jmeno) VALUES (3, 'Michal Merta');

-- A) Hodnota NULL: hodnota NULL znamena ZADNA DATA
INSERT INTO zamestnanci (id, jmeno, plat, pozn) VALUES (4, 'Petr Hnizdo', 12000, NULL);

-- B) operator zretezeni: '||'
-- Zretezeni typu char a char je char, je-li jeden operand typu varchar2
-- je vysledek typu varchar2.
SELECT 'Poznamka u ' || jmeno || ' je ' || pozn AS "Retezec s poznamkou" FROM zamestnanci;
SELECT 'Zamestnanec ' || jmeno || ' ma plat ' || TO_CHAR(plat) AS "Retezec s platem" FROM zamestnanci; 

-- C) Nerovna se: muzeme pouzit !=,^= i <>. (krome hodnoty NULL)
SELECT jmeno, plat AS "plat != 15000" FROM zamestnanci WHERE plat != 15000;
SELECT jmeno, plat AS "Plat mimo Petra Mersyna" FROM zamestnanci WHERE jmeno <> 'Petr Mersyn';



-- Testovani hodoty NULL - POUZE podminkou IS [NOT] NULL 
SELECT jmeno AS "Zamestnanci s poznamkou" FROM zamestnanci WHERE pozn IS NOT NULL;
SELECT jmeno AS "Zamestnanci bez poznamky" FROM zamestnanci WHERE pozn IS NULL;

-- Prace s hodnotou NULL:
-- If A is: Condition         Evaluates to:
-- 10       a IS NULL         FALSE
-- 10       a IS NOT NULL     TRUE
-- NULL     a IS NULL         TRUE
-- NULL     a IS NOT NULL     FALSE
-- 10       a = NULL          UNKNOWN
-- 10       a != NULL         UNKNOWN
-- NULL     a = NULL          UNKNOWN
-- NULL     a != NULL         UNKNOWN
-- NULL     a = 10            UNKNOWN
-- NULL     a != 10           UNKNOWN

-- D) podminka [NOT] LIKE - srovnava podobne retezce 
-- _ zastupuje jeden znak
-- % - zastupuje zadny az mnoho znaku
-- pokud budu chtit srovnavat % a _ jako normalni znaky musim pouzit escape
-- znaky napr: jmeno LIKE 'a\%q' ESCAPE '\'
-- srovnavani je CASE SENSITIVE, chci-li opak, muzu pouzit napr. 
-- UPPER(jmeno) LIKE 'RI%';
SELECT jmeno AS "jmeno %Mer%" , plat FROM zamestnanci WHERE jmeno LIKE '%Mer%';
SELECT jmeno AS "jmeno Pe_r Hnizdo", plat FROM zamestnanci WHERE jmeno LIKE 'Pe_r Hnizdo';

-- E) Operator BETWEEN
-- Vetsi nebo rovno nez spodni mez a zaroven mensi nebo rovno nez horni mez,
SELECT jmeno AS "Plat mezi 10 a 14", plat FROM zamestnanci WHERE plat BETWEEN 10000 AND 14000;

-- F) Mnozinove operace: UNION - sjednoceni
--                       UNION ALL - sjednoceni s opakovanim prvku
--                       INTERSECT - prunik
--                       MINUS - rozdil
SELECT jmeno AS "vsichni bez opakovani" FROM zamestnanci UNION SELECT jmeno FROM vedouci;
SELECT jmeno AS "zamestnanec i vedouci" FROM zamestnanci INTERSECT SELECT jmeno FROM vedouci;
SELECT jmeno AS "jen zamestnanec" FROM zamestnanci MINUS SELECT jmeno FROM vedouci;

-- G) IN, NOT IN, ANY, SOME, ALL
-- vyraz se srovnava s nekterym (alespon jednim) prvkem mnoziny:
-- VYRAZ =,!=,<,>,<= nebo >= ANY (mnozina)
-- SOME - ekvivalent s ANY
SELECT jmeno AS "zamestanec i vedouci", plat FROM zamestnanci WHERE jmeno = ANY (SELECT jmeno FROM vedouci);

-- vyraz se srovnava se vsemi prvky mnoziny:
-- VYRAZ =,!=,<,>,<= nebo >= ALL (mnozina). Je-li mnozina prazdna, je 
-- vysledek FALSE
SELECT jmeno AS "plat > nez Petrove", plat FROM zamestnanci WHERE plat > ALL(SELECT plat FROM zamestnanci WHERE jmeno LIKE 'Petr%');

-- IN - je prvkem mnoziny (rovnocene se "= ANY")
SELECT jmeno AS "id IN 1,4,1025", id FROM zamestnanci WHERE id IN (1,4,1025);

-- NOT IN - neni prvkem mnoziny (rovnocene s "!=ALL")
SELECT jmeno AS "id NOT IN 2,3", id FROM zamestnanci WHERE id NOT IN (2,3);

-- zjisteni struktury tabulky:
DESC zamestnanci;