-- vytvorime sekvenci se jmenem 'pokus' s pocatecni hoidnotou 10 a
-- inkrementacnim krokem 2. 
-- aktualni hodnota sekvence ziskame 'jmeno_sekvence.CURRVAL'
-- zvyseni hodnoty sekvence o stanoveny krok a vraceni nove hodnoty 
-- provedeme pomoci 'jmeno_sekvence.NEXTVAL'  
CREATE SEQUENCE pokus;

SELECT pokus.NEXTVAL FROM dual;
SELECT pokus.CURRVAL FROM dual;

DROP SEQUENCE pokus;

-- vytvorime sekvenci 'seq_limited' se zadanym omezenim pro minimalni a
-- maximalni hodnotu
CREATE SEQUENCE seq_limited INCREMENT BY 1 START WITH 3 MAXVALUE 10 MINVALUE
3;

-- zobrazime vytvorene sekvence
SELECT sequence_name, increment_by, last_number FROM user_sequences;

-- smazani sekvence
DROP SEQUENCE seq_limited;

-- zrus tabulku a sekvenci
DROP TABLE zamestnanci;
DROP SEQUENCE seq_zamest_id;
-- sekvence pro id zamestnancu
CREATE SEQUENCE seq_zamest_id;
-- tabulka zamestnancu
CREATE TABLE zamestnanci(
	jmeno VARCHAR2(15), 
	prijmeni VARCHAR2(15), 
	id NUMBER CONSTRAINT pk_id PRIMARY KEY, 
	lokace VARCHAR2(10),
	plat NUMBER);

-- naplneni tabulky zamestnanci
INSERT INTO zamestnanci (jmeno, prijmeni, id, lokace, plat) VALUES ('Michal',
'Merta', seq_zamest_id.NEXTVAL,'Brno', 13000);
INSERT INTO zamestnanci (jmeno, prijmeni, id, lokace, plat) VALUES ('Jan',
'Orel', seq_zamest_id.NEXTVAL,'Brno', 18000);
INSERT INTO zamestnanci (jmeno, prijmeni, id, lokace, plat) VALUES
('Petr','Salava', seq_zamest_id.NEXTVAL,'Praha', 11000);
INSERT INTO zamestnanci (jmeno, prijmeni, id, lokace, plat) VALUES
('Petr','Salava', seq_zamest_id.NEXTVAL,'Ostrava', 9000);
INSERT INTO zamestnanci (jmeno, prijmeni, id, lokace, plat) VALUES
('Simon','Blazek', seq_zamest_id.NEXTVAL,'Praha', 21000);
INSERT INTO zamestnanci (jmeno, prijmeni, id, lokace, plat) VALUES
('Petr','Miska', seq_zamest_id.NEXTVAL,'Brno', 8000);
    
SELECT * FROM zamestnanci;

-- zobrazime zamestnance bez opakovani
SELECT DISTINCT jmeno, prijmeni FROM zamestnanci;

-- zobrazime celkovy pocet zaznamu z tabulky zamestananci
SELECT COUNT(*) AS "zaznamu v tab." FROM zamestnanci;

-- seradime zamestanace dle jejich prijmeni vzestupne 
-- zobrazeni vcetne duplicit 
SELECT * FROM zamestnanci ORDER BY prijmeni ASC;

-- zobrazime vydelky jednotlivych zamestnancu
SELECT jmeno, prijmeni, SUM(plat) FROM zamestnanci
GROUP BY jmeno, prijmeni;
    
-- seskupeni zamestnancu dle jmena a prijmeni a zobrazeni pouze tech skupin,
-- ktera maji jmeno rovno 'Petr'
SELECT jmeno, prijmeni, COUNT(*) FROM zamestnanci
GROUP BY jmeno, prijmeni HAVING jmeno = 'Petr';

-- zobrazeni informaci o castkach, ktera jsou vyplacena zamestnancum v ruznych mestech
SELECT lokace, COUNT(*), SUM(plat), AVG(plat), MIN(plat), MAX(plat) FROM zamestnanci
GROUP BY lokace;

-- predchozi dotaz, avsak omezen na mesta, ktera maji vetsi prumerny plat
-- nez 12000, setrizeno vzestupne dle poctu zamestnancu
SELECT lokace, COUNT(*), SUM(plat), AVG(plat), MIN(plat), MAX(plat) FROM zamestnanci
GROUP BY lokace HAVING AVG(plat) > 12000 ORDER BY COUNT(*) ASC;

-- zobrazeni zamestnance s maximalnim platem
SELECT prijmeni, celk_plat FROM 
  (SELECT jmeno, prijmeni, SUM(plat) AS celk_plat FROM zamestnanci 
   GROUP BY jmeno, prijmeni)
  WHERE celk_plat = (SELECT MAX(celk_plat) FROM 
  (SELECT jmeno, prijmeni, SUM(plat) AS celk_plat FROM zamestnanci
   GROUP BY jmeno, prijmeni));







