IDAS2_CV1_HOMEFORK

CREATE OR REPLACE VIEW  cv1_regiony_prumer_pocet(region_nazev, prum_mzda, pocet_zam) AS
SELECT NVL(region_nazev, 'N/A') AS region_nazev, ROUND(AVG(MZDA),1) AS prum_mzda, COUNT(*) AS pocet_zam
FROM A_HR.zamestnanci
LEFT JOIN A_HR.ODDELENI ON A_HR.ZAMESTNANCI.ODDELENI_ID = A_HR.ODDELENI.ODDELENI_ID
LEFT JOIN A_HR.ADRESY ON  A_HR.oddeleni.adresa_ID = A_HR.ADRESY.ADRESA_ID
LEFT JOIN A_HR.ZEME ON A_HR.adresy.zeme_ID = A_HR.zeme.zeme_ID
LEFT JOIN A_HR.REGIONY ON A_HR.regiony.region_id = A_HR.ZEME.region_id
GROUP BY REGION_NAZEV
ORDER BY REGION_NAZEV;

CREATE OR REPLACE VIEW  cv1_zam_kolem_prumeru(zamestnanec_id, jmeno, prijmeni,
oddeleni_nazev, mzda) AS
SELECT ZAMESTNANEC_ID,JMENO,PRIJMENI,ODDELENI_NAZEV,MZDA
FROM A_HR.ZAMESTNANCI
JOIN A_HR.ODDELENI ON A_HR.ZAMESTNANCI.ODDELENI_ID=A_HR.ODDELENI.ODDELENI_ID
WHERE
((SELECT ROUND(AVG(MZDA))FROM A_HR.ZAMESTNANCI)*0.7)<=mzda
AND
((SELECT ROUND(AVG(MZDA)) FROM  A_HR.ZAMESTNANCI)*1.3) >=mzda
ORDER BY ZAMESTNANEC_ID ASC;


CREATE OR REPLACE VIEW cv1_nejnakladnejsi_oddeleni(oddeleni_nazev, suma_mezd) AS
SELECT oddeleni_nazev,SUM(mzda) AS suma_mezd
FROM a_hr.zamestnanci JOIN a_hr.oddeleni USING (oddeleni_id)
HAVING SUM(mzda) =
(SELECT MAX(SUM(mzda))
FROM a_hr.zamestnanci JOIN a_hr.oddeleni USING (oddeleni_id) GROUP BY oddeleni_nazev)
GROUP BY oddeleni_nazev;

CREATE OR REPLACE VIEW cv1_zam_oddeleni_nadprumer(cele_jmeno, oddeleni_nazev, mzda)AS
SELECT jmeno || ' ' || prijmeni cele_jmeno, oddeleni.oddeleni_nazev, mzda
FROM A_HR.zamestnanci JOIN A_HR.oddeleni ON a_hr.zamestnanci.oddeleni_id = a_hr.oddeleni.oddeleni_id
JOIN (SELECT oddeleni_id, AVG(mzda) prumerna_mzda
FROM A_HR.zamestnanci
GROUP BY oddeleni_id)
prumery_oddeleni
ON zamestnanci.oddeleni_id = prumery_oddeleni.oddeleni_id WHERE mzda > prumerna_mzda;