-- sa se afiseze departamentul cu cel mai mare salariu mediu

describe angajati;
describe departamente;


SELECT AVG(salariul)
FROM  angajati a, departamente d
WHERE a.id_departament = d.id_departament;

SELECT AVG(salariul) sal_mediu, denumire_departament
FROM  angajati a, departamente d
WHERE a.id_departament = d.id_departament
GROUP BY denumire_departament
HAVING AVG(salariul) = (SELECT MAX (AVG(salariul)) FROM angajati GROUP BY id_departament)
ORDER BY 1 DESC;    -- in order by putem folosi aliasuri, in having nu

SELECT * FROM
(SELECT AVG(salariul) sal_mediu, denumire_departament
FROM  angajati a, departamente d
WHERE a.id_departament = d.id_departament
GROUP BY denumire_departament
ORDER BY 1 DESC)
WHERE ROWNUM = 1;   -- <= 3 => primele 3 in rodinea salariilor medii


SET serveroutput ON;
DECLARE
V_DEN VARCHAR2(200);
V_SAL NUMBER;

BEGIN

V_DEN := 'abcd';   --operator de atribuire
SELECT AVG(salariul) sal_mediu, denumire_departament      INTO V_SAL, V_DEN  --selectul trebuie sa returneze un rand! nu 0, nu 3
FROM  angajati a, departamente d
WHERE a.salariul = d.id_departament
GROUP BY denumire_departament
--having avg(salariul) = (select max (avg(salariul)) from angajati group by id_departament)
ORDER BY 1 DESC;

DBMS_OUTPUT.put_line('Departementul '|| V_DEN || ' are salariul '|| ROUND(V_SAL, 2));

EXCEPTION
    WHEN VALUE_ERROR THEN
    DBMS_OUTPUT.PUT_LINE('Valoare prea mare');
    WHEN TOO_MANY_ROWS THEN
    DBMS_OUTPUT.PUT_LINE('Prea multe randuri');
    WHEN OTHERS THEN   -- others e un handler
    DBMS_OUTPUT.PUT_LINE('A aparut o execeptie: ' || SQLERRM); -- afiseaza exceptia aparuta
                                                               -- la prima execeptie aparuta blocul se termina
END;
/