IDAS2_CV1_SAMOSTATNA_PRACE

2.
CREATE OR REPLACE FORCE 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 USING(ODDELENI_ID)
WHERE (SELECT avg(mzda) *0.7 FROM A_HR.ZAMESTNANCI) <= MZDA
AND (SELECT avg(mzda) *1.3 FROM A_HR.ZAMESTNANCI) >= MZDA
ORDER BY mzda;
3.
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)
GROUP BY oddeleni_nazev
HAVING SUM(mzda) =
(SELECT MAX(SUM(mzda)) FROM A_HR.zamestnanci GROUP BY oddeleni_id);

4.
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;