CREATE VIEW V_POCETZAM
AS
SELECT KOD, COUNT(*) AS POCET
FROM ZAMESTNANCI
GROUP BY KOD
/*view, ktore vypluje pocet zamestnancov pre kazdy utvar*/


CREATE VIEW V_VYPLATASUM
AS
SELECT Z.KOD, P.DATUM, SUM(P.VYPLATA) AS VYPLATASUM
FROM ZAMESTNANCI Z
INNER JOIN PLATY P
	ON Z.OC = P.OC
GROUP BY Z.KOD, P.DATUM
/*view, ktore vypluje pre kazdy mesiac a kazdy utvar celkovu vyplatu zamestnancov ...preto beriem do uvahy cely datum, pretoze by sa ti mohlo stat, ze scitas rovnaky mesic z roznych rokov*/

CREATE VIEW V_VYPLATAAVG
AS
SELECT Z.KOD, SUBSTRING(P.DATUM, 3, 2) AS ROK, AVG(P.VYPLATA) AS VYPLATAAVG
FROM ZAMESTNANCI Z
INNER JOIN PLATY P
	ON Z.OC = P.OC
GROUP BY Z.KOD, SUBSTRING(P.DATUM, 3, 2)
/*view, ktore vypluje pre kazdy rok a kazdy utvar priemernu vyplatu*/

SELECT
 U.KOD
,U.VEDUCI
,PZ.POCET
,VS.VYPLATASUM
,VA.VYPLATAAVG
FROM UTVAR U

INNER JOIN V_POCETZAM PZ
	ON PZ.KOD = U.KOD

INNER JOIN V_VYPLATASUM VS
	ON VS.KOD = PZ.KOD

INNER JOIN V_VYPLATAAVG AV
	ON AV.KOD = PZ.KOD
WHERE VS.DATUM = '0105' AND AV.ROK = '05'
/*vysledne view, ktorym pospajas tie predosle predpripravene...*/