--1.

SELECT denumire_departament, AVG(salariul) SAL_MEDIU
FROM angajati a join departamente d
ON 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 sal_mediu DESC;
--alisaurile de coloana nu merg folosite in having

SELECT* FROM
(SELECT denumire_departament, AVG(salariul) SAL_MEDIU
FROM angajati a join departamente d
ON a.id_departament=d.id_departament
GROUP BY denumire_departament
ORDER BY sal_mediu DESC)
WHERE rownum<=1;--<=3 primele 3 in ordinea salariilor medii ,nu merge decat cu < sau <=

--daca vreau al treilea rand

SELECT* FROM
(SELECT denumire_departament, AVG(salariul) SAL_MEDIU
FROM angajati a join departamente d
ON a.id_departament=d.id_departament
GROUP BY denumire_departament
ORDER BY sal_mediu DESC)
WHERE rownum<=3
MINUS
SELECT* FROM
(SELECT denumire_departament, AVG(salariul) SAL_MEDIU
FROM angajati a join departamente d
ON a.id_departament=d.id_departament
GROUP BY denumire_departament
ORDER BY sal_mediu DESC)
WHERE rownum<=2;




DECLARE
V_DEN VARCHAR2(50);
V_SAL NUMBER;



BEGIN
    SELECT denumire_departament, AVG(salariul) SAL_MEDIU INTO V_DEN, V_SAL
    FROM angajati a join departamente d
    ON 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 sal_mediu DESC;
END;
/
--IMI DA EROARE,  TREBUIE SA LE PUN INTR O VARIABILA

SET SERVEROUTPUT ON;--TREBUIE SA PUN ASTA CA SA PERMITA EXECUTAREA LUI DBMS_OUTPUT.....
DECLARE
V_DEN VARCHAR2(50);
V_SAL NUMBER;
BEGIN
    SELECT denumire_departament, AVG(salariul) SAL_MEDIU INTO V_DEN, V_SAL
    FROM angajati a join departamente d
    ON 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 2 DESC;
    DBMS_OUTPUT.PUT_LINE('DEPARTAMENTUL' ||V_DEN||'ARE SAL MEDIU DE '||V_SAL);
END;
/

--EXCEPTIE

SET SERVEROUTPUT ON;
DECLARE
V_DEN VARCHAR2(2);
V_SAL NUMBER;
BEGIN
   V_DEN:='ABC'
;    SELECT denumire_departament, AVG(salariul) SAL_MEDIU INTO V_DEN, V_SAL
    FROM angajati a join departamente d
    ON 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 2 DESC;
    DBMS_OUTPUT.PUT_LINE('DEPARTAMENTUL' ||V_DEN||'ARE SAL MEDIU DE '||V_SAL);

 EXCEPTION
 WHEN VALUE_ERROR THEN
   DBMS_OUTPUT.PUT_LINE('VALOARE PREA MARE PENTRU VARIABILA RESPECTIVA');
   WHEN TOO_MANY_ROWS THEN--UN FEL DE ELSE
    DBMS_OUTPUT.put_line('PREA MULTE RANDURI RETURNATE');
    WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('A APARUT O EXCEPTIE '||SQLERRM);
END;
/