-- vytvorime sekvenci se jmenem 'seq_zamest_id' 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'
SQL> CREATE SEQUENCE seq_zamest_id INCREMENT BY 2 START WITH 10;
Sequence created.
-- vytvorime sekvenci 'seq_limited' se zadanym omezenim pro minimalni a
-- maximalni hodnotu
SQL> CREATE SEQUENCE seq_limited INCREMENT BY 1 START WITH 3 MAXVALUE 10 MINVALUE 3;
Sequence created.
-- zobrazime vytvorene sekvence
SQL> SELECT sequence_name, increment_by, last_number FROM user_sequences;
SEQUENCE_NAME INCREMENT_BY LAST_NUMBER
------------------------------ ------------ -----------
SEQ_ZAMEST_ID 2 10
SEQ_LIMITED 1 3
-- tabulka zamestnancu
SQL> CREATE TABLE zamestnanci(
jmeno VARCHAR2(15),
prijmeni VARCHAR2(15),
id NUMBER CONSTRAINT pk_id PRIMARY KEY,
lokace VARCHAR2(10),
plat NUMBER);
Table created.
-- naplneni tabulky zamestnanci
SQL> INSERT INTO zamestnanci (jmeno, prijmeni, id, lokace, plat) VALUES ('Michal','Merta', seq_zamest_id.NEXTVAL,'Brno', 13000);
SQL> INSERT INTO zamestnanci (jmeno, prijmeni, id, lokace, plat) VALUES ('Jan','Orel', seq_zamest_id.NEXTVAL,'Brno', 18000);
SQL> INSERT INTO zamestnanci (jmeno, prijmeni, id, lokace, plat) VALUES ('Petr','Salava', seq_zamest_id.NEXTVAL,'Praha', 11000);
SQL> INSERT INTO zamestnanci (jmeno, prijmeni, id, lokace, plat) VALUES ('Petr','Salava', seq_zamest_id.NEXTVAL,'Ostrava', 9000);
SQL> INSERT INTO zamestnanci (jmeno, prijmeni, id, lokace, plat) VALUES ('Simon','Blazek', seq_zamest_id.NEXTVAL,'Praha', 21000);
SQL> INSERT INTO zamestnanci (jmeno, prijmeni, id, lokace, plat) VALUES ('Petr','Miska', seq_zamest_id.NEXTVAL,'Brno', 8000);
... 6 rows created ...
SQL> SELECT * FROM zamestnanci;
JMENO PRIJMENI ID LOKACE PLAT
--------------- --------------- ---------- ---------- ----------
Michal Merta 10 Brno 13000
Jan Orel 12 Brno 18000
Petr Salava 14 Praha 11000
Petr Salava 16 Ostrava 9000
Simon Blazek 18 Praha 21000
Petr Miska 20 Brno 8000
6 rows selected.
-- zobrazime zamestnance bez opakovani
SQL> SELECT DISTINCT jmeno, prijmeni FROM zamestnanci;
JMENO PRIJMENI
--------------- ---------------
Jan Orel
Michal Merta
Petr Miska
Petr Salava
Simon Blazek
-- zobrazime celkovy pocet zaznamu z tabulky zamestananci
SQL> SELECT COUNT(*) AS "zaznamu v tab." FROM zamestnanci;
zaznamu v tab.
-----------------
6
-- seradime zamestanace dle jejich prijmeni vzestupne
-- zobrazeni vcetne duplicit
SQL> SELECT * FROM zamestnanci ORDER BY prijmeni ASC;
JMENO PRIJMENI ID LOKACE PLAT
--------------- --------------- ---------- ---------- ----------
Simon Blazek 18 Praha 21000
Michal Merta 10 Brno 13000
Petr Miska 20 Brno 8000
Jan Orel 12 Brno 18000
Petr Salava 14 Praha 11000
Petr Salava 16 Ostrava 9000
6 rows selected.
-- zobrazime vydelky jednotlivych zamestnancu
SQL> SELECT jmeno, prijmeni, SUM(plat) FROM zamestnanci
GROUP BY jmeno, prijmeni;
JMENO PRIJMENI SUM(PLAT)
--------------- --------------- ----------
Jan Orel 18000
Petr Miska 8000
Petr Salava 20000
Simon Blazek 21000
Michal Merta 13000
-- seskupeni zamestnancu dle jmena a prijmeni a zobrazeni pouze tech skupin,
-- ktera maji jmeno rovno 'Petr'
SQL> SELECT jmeno, prijmeni, COUNT(*) FROM zamestnanci
GROUP BY jmeno, prijmeni HAVING jmeno = 'Petr';
JMENO PRIJMENI COUNT(*)
--------------- --------------- ----------
Petr Miska 1
Petr Salava 2
-- zobrazeni informaci o castkach, ktera jsou vyplacena zamestnancum v ruznych mestech
SQL> SELECT lokace, COUNT(*), SUM(plat), AVG(plat), MIN(plat), MAX(plat) FROM zamestnanci
GROUP BY lokace;
LOKACE COUNT(*) SUM(PLAT) AVG(PLAT) MIN(PLAT) MAX(PLAT)
---------- ---------- ---------- ---------- ---------- ----------
Brno 3 39000 13000 8000 18000
Ostrava 1 9000 9000 9000 9000
Praha 2 32000 16000 11000 21000
-- predchozi dotaz, avsak omezen na mesta, ktera maji vetsi prumerny plat
-- nez 12000, setrizeno vzestupne dle poctu zamestnancu
SQL> SELECT lokace, COUNT(*), SUM(plat), AVG(plat), MIN(plat), MAX(plat) FROM zamestnanci
GROUP BY lokace HAVING AVG(plat) > 12000 ORDER BY COUNT(*) ASC;
LOKACE COUNT(*) SUM(PLAT) AVG(PLAT) MIN(PLAT) MAX(PLAT)
---------- ---------- ---------- ---------- ---------- ----------
Praha 2 32000 16000 11000 21000
Brno 3 39000 13000 8000 18000
-- zobrazeni zamestnance s maximalnim platem
SQL> 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));
PRIJMENI CELK_PLAT
--------------- ----------
Blazek 21000