← Back to all notes

DBMS (4IT218)

Published: 2/18/2026 • Updated: 5/18/2026

Prednasky

create table ODD (
	CIS_ODD SMALLINT NOT NULL,
	NAZEV VARCHAR2(40) NOT NULL,
	PATRO CHAR(3) NOT NULL,
	CONSTRAINT PK_ODD primary key (CIS_ODD)	
)

create table ZAM ( 
	CIS INTEGER not null, 
	CIS_ODD SMALLINT, 
	CIS_VED INTEGER, 
	JM VARCHAR2(40) not null,
	PLAT NUMBER(10,2) not null 
	
	constraint CKC_PLAT_ZAM check (PLAT between 100 and 60000), 
	constraint PK_ZAM primary key (CIS),
	constraint FK_ZAM_ZARAZENI_ODD foreign key (CIS_ODD) references ODD (CIS_ODD),
	constraint F_ZAM_NADRIZZAM foreign key (CIS_VED) references ZAM (CIS) 
);

Rizeni pristupu (Data Control Language)

GRANT {all prava_treba_privileges} on {nejakej_objekt_treba_tabulka} to {public_nebo_vycet_uzivatelu} [with grant option]

Standardizace jazyka

RDBS

Datove modelovani

Normalizace dat

Fyzicky model

Oracle prednaska s Davidem Krchem

Role v DB zpracovani, sprava dat

Cvika

Prvni cviko (2026-02-17)

SELECT * FROM zam WHERE titul = 'RNDR';
SELECT DISTINCT cis_odd
FROM zam
WHERE cis_odd IS NOT NULL;
SELECT DISTINCT zam.cis_odd, oddel.nazev 
    FROM zam join oddel ON zam.cis_odd = oddel.cis_odd 
WHERE zam.titul = 'ING';

/* Obdobne muzeme pouzit USING, protoze maji oba edge stejny column name */

SELECT DISTINCT cis_odd, oddel.nazev
FROM zam
JOIN oddel USING (cis_odd)
WHERE zam.titul = 'ING';
SELECT
    cis_odd,
    nazev
FROM
    oddel
WHERE
    cis_odd = ANY (
        SELECT
            zam.cis_odd
        FROM
            zam
        WHERE
            titul LIKE 'ING'
    );
SELECT
    cis_odd,
    nazev
FROM
    oddel
WHERE
    cis_odd IN (
        SELECT
            zam.cis_odd
        FROM
            zam
        WHERE
            titul LIKE 'ING'
    );
SELECT
    cis_odd,
    COUNT(cis_odd) AS user_count
FROM
    zam
WHERE
    titul = 'ING'
GROUP BY
    cis_odd;
SELECT
    oddel.cis_odd,
    oddel.nazev,
    z.titul
FROM
    oddel
    LEFT JOIN (
        SELECT
            titul,
            cis_odd
        FROM
            zam
        WHERE
            titul LIKE 'ING'
    ) z ON z.cis_odd = oddel.cis_odd;

S GROUP BY a prumerem platu:

SELECT
    nazev,
    COUNT(os_cis) AS pocet_ing,
    AVG(plat)     AS prum_plat
FROM
    oddel
    LEFT JOIN (
        SELECT
            titul,
            cis_odd,
            plat,
            os_cis
        FROM
            zam
        WHERE
            titul LIKE 'ING'
    ) USING ( cis_odd )
GROUP BY
    cis_odd,
    nazev
ORDER BY
    cis_odd;

ROUND a AVG:

SELECT ROUND(AVG(plat)) FROM zam;
/* Vypište čísla a jména zaměstnanců, kteří mají plat větší, než je průměrný plat v jejich oddělení. */

SELECT
    os_cis,
    jmeno
FROM
    zam a
WHERE
    plat > (
        SELECT
            AVG(plat)
        FROM
            zam b
        WHERE
            b.cis_odd = a.cis_odd
    );
SELECT
    os_cis,
    jmeno
FROM
         zam
    JOIN (
        SELECT
            cis_odd,
            AVG(plat) prumplat
        FROM
            zam
        GROUP BY
            cis_odd
    ) prum ON ( zam.cis_odd = prum.cis_odd )
WHERE
    zam.plat > prum.prumplat;

Treti cviko (2026-03-03)

/* Vypište čísla a názvy oddělení, ve kterých pracují méně než 2 pracovníci s přiděleným úkolem. */

SELECT cis_odd, nazev FROM oddel JOIN zam USING (cis_odd) GROUP BY cis_odd, nazev HAVING COUNT(os_cis) > 2;

– tady je having jako posledni filtr po GROUP BY


/* Pro všechna oddělení vypište počty zaměstnanců a počty zaměstnanců, kteří mají přidělený úkol.*/

SELECT
    o.cis_odd,
    o.nazev,
    COUNT(DISTINCT z.os_cis) AS pocet_zamestnancu,
    COUNT(DISTINCT u.os_cis) AS pocet_zam_s_ukolem
FROM
    oddel o
    LEFT JOIN zam   z ON z.cis_odd = o.cis_odd
    LEFT JOIN ukoly u ON u.os_cis = z.os_cis
GROUP BY
    o.cis_odd,
    o.nazev;

Ctvrte cviko (2026-03-10)

SELECT popis, os_cis, jmeno
FROM ukoly
JOIN zam USING (os_cis)
WHERE datum BETWEEN
      TO_DATE('2015-09-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS')
  AND TO_DATE('2015-12-31 00:00:00', 'YYYY-MM-DD HH24:MI:SS');

Pate cviko (2026-03-17)

Seste cviko (2026-03-24)

— test —

Sedme cviko (2026-03-31)

Osme cviko (2026-04-14)

Devate cviko (2026-04-21)

Desate cviko (2026-04-28)