IDAS2_CV1_SPOLECNAPRACE

CREATE OR REPLACE VIEW cv1_pozice_oddeleni_10000(pozice_nazev, oddeleni_nazev,prum_mzda) AS
SELECT A_HR.PRAC_POZICE.Pozice_nazev,  A_HR.ODDELENI.ODDELENI_Nazev, ROUND(avg(A_HR.ZAMESTNANCI.mzda),2)  FROM A_HR.ZAMESTNANCI
JOIN A_HR.PRAC_POZICE ON A_HR.ZAMESTNANCI.POZICE_ID = A_HR.prac_pozice.POZICE_ID
JOIN A_HR.ODDELENI ON A_HR.ZAMESTNANCI.ODDELENI_ID = A_HR.ODDELENI.ODDELENI_id
GROUP BY A_HR.PRAC_POZICE.Pozice_nazev,  A_HR.ODDELENI.ODDELENI_Nazev
HAVING avg( A_HR.ZAMESTNANCI.mzda)>10000
ORDER BY ROUND(avg(A_HR.ZAMESTNANCI.mzda),2);

SELECT * FROM cv1_pozice_oddeleni_10000;

CREATE OR REPLACE VIEW  cv1_zamestnanci_oddeleni(cele_jmeno, oddeleni_nazev) AS
SELECT jmeno || ' ' || prijmeni AS Cele_Jmeno, NVL(ODDELENI_NAZEV,'NEPŘIŘAZENO') AS Oddeleni_Nazev
FROM A_HR.zamestnanci LEFT JOIN A_HR.ODDELENI USING(ODDELENI_ID)
WHERE NVL(Oddeleni_nazev,'NEPŘIŘAZENO') NOT IN ('Sales','Shipping','Purchasing');

SELECT * FROM cv1_zamestnanci_oddeleni;

CREATE OR REPLACE VIEW cv1_bez_provize(zamestnanec_id, prijmeni, datum_nastup, dni) AS
SELECT ZAMESTNANEC_ID,prijmeni, TO_char(Datum_nastup,'DD/MM/YYYY') AS Datum_nastupu,
trunc(SYSDATE - DATUM_NASTUP) || ' dni' AS dni
FROM A_HR.ZAMESTNANCI
WHERE PROVIZE_procent IS NULL AND mzda >= (SELECT (avg(A_HR.ZAMESTNANCI.MZDA)*1.6) FROM A_HR.ZAMESTNANCI);

SELECT * FROM cv1_bez_provize;

CREATE OR REPLACE VIEW cv1_oddeleni_suma_mezd(oddeleni_nazev, suma_mezd) AS
SELECT oddeleni_nazev, SUM(mzda)FROM A_HR.ZAMESTNANCI
LEFT JOIN A_HR.ODDELENI ON A_HR.ZAMESTNANCI.ODDELENI_ID = A_HR.ODDELENI.ODDELENI_ID
WHERE datum_nastup < to_date('01/01/1998','DD/mm/yyyy')
GROUP BY oddeleni_nazev HAVING COUNT(A_HR.ZAMESTNANCI.ZAMESTNANEC_ID)>2
ORDER BY  SUM(mzda) DESC;

SELECT * FROM cv1_oddeleni_suma_mezd;

CREATE OR REPLACE VIEW cv1_nejstarsi_nejmladsi(jmeno, prijmeni, datum_nastup, stav) AS
SELECT Jmeno, prijmeni, datum_nastup,
(CASE WHEN DATUM_NASTUP = (SELECT MAX(datum_nastup) FROM A_HR.ZAMESTNANCI) THEN 'SLUŽEBNĚ NEJSTARŠÍ' ELSE 'SLUŽEBNĚ NEJMLADŠÍ' END)
AS stav FROM A_HR.ZAMESTNANCI
WHERE
Datum_nastup=(SELECT MIN(Datum_nastup) FROM A_HR.ZAMESTNANCI WHERE manazer_id IS NOT NULL)
OR
datum_nastup=(SELECT MAX(datum_nastup)FROM A_hr.zamestnanci WHERE manazer_id IS NOT NULL);

SELECT * FROM cv1_nejstarsi_nejmladsi;