/* SQL - 4th GL
SQL -> Limbaj pentru interogari structurate,
!!! Nu limbaj structurat (gen. a 3a) --> PLSQL, C, JAVA, PHP
2nd Gen - Assembler
1 GL - Cod Masina
*/

/*Sa se afiseze departamentul cu cel mai mare salariu mediu*/
SELECT denumire_departament, AVG(salariul) SAL_MEDIU
FROM departamente d JOIN angajati a
    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;


--sau
SELECT * FROM
(SELECT denumire_departament, AVG(salariul) SAL_MEDIU
FROM departamente d JOIN angajati a
    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)
WHERE ROWNUM<=1;

--
SELECT * FROM
(SELECT denumire_departament, AVG(salariul) SAL_MEDIU
FROM departamente d JOIN angajati a
    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)
WHERE ROWNUM<=3
MINUS
SELECT * FROM
(SELECT denumire_departament, AVG(salariul) SAL_MEDIU
FROM departamente d JOIN angajati a
    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)
WHERE ROWNUM<=2;

/*BLOC PL:
DECLARE - optional

BEGIN

EXCEPTION - optional

END
*/

/*Sa se afiseze departamentul cu cel mai mare salariu mediu*/
SET SERVEROUTPUT ON
DECLARE
    V_DEN VARCHAR2(50);
    V_SAL NUMBER;

BEGIN
    SELECT denumire_departament, AVG(salariul)  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 SALARIUL MEDIU DE '||ROUND(V_SAL,2));
END;
/

/*
EXCEPTII

SQLERRM-codul si descrierea exceptiei
SQLCODE-codul exceptiei aparute
*/
DECLARE
    V_DEN VARCHAR2(2);
    V_SAL NUMBER;

BEGIN
    SELECT denumire_departament, AVG(salariul)  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 SALARIUL MEDIU DE '||ROUND(V_SAL,2));
EXCEPTION
    WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('A APARUT O EXCEPTIE' ||SQLERRM);
END;
/

--Prima exceptie termina codul -> VALUE_ERROR - denumire pentru exceptia respectiva

DECLARE
    V_DEN VARCHAR2(2);
    V_SAL NUMBER;

BEGIN
    V_DEN := 'ABC';
    SELECT denumire_departament, AVG(salariul)  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 SALARIUL MEDIU DE '||ROUND(V_SAL,2));
EXCEPTION
    WHEN VALUE_ERROR THEN
    DBMS_OUTPUT.PUT_LINE('VALOARE PREA MARE PENTRU VARIABILA RESPECTIVA');
    WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('A APARUT O EXCEPTIE ' ||SQLERRM);
END;
/

--TOO_MANY_ROWS
DECLARE
    V_DEN VARCHAR2(200);
    V_SAL NUMBER;

BEGIN
    V_DEN := 'ABC';
    SELECT denumire_departament, AVG(salariul)  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 SALARIUL MEDIU DE '||ROUND(V_SAL,2));
EXCEPTION
    WHEN TOO_MANY_ROWS THEN
    DBMS_OUTPUT.PUT_LINE('PREA MULTE RANDURI RETURNATE');
    WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('A APARUT O EXCEPTIE ' ||SQLERRM);
END;
/

--NO DATA FOUND  join cu a.salariul si id_departament!!
DECLARE
    V_DEN VARCHAR2(200);
    V_SAL NUMBER;

BEGIN
    V_DEN := 'ABC';
    SELECT denumire_departament, AVG(salariul)  INTO V_DEN,V_SAL
    FROM angajati a JOIN departamente d
    ON a.salariul=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 SALARIUL MEDIU DE '||ROUND(V_SAL,2));
EXCEPTION
    WHEN VALUE_ERROR THEN
    DBMS_OUTPUT.PUT_LINE('VALOARE PREA MARE PENTRU VARIABILA RESPECTIVA');
    WHEN TOO_MANY_ROWS THEN
    DBMS_OUTPUT.PUT_LINE('PREA MULTE RANDURI RETURNATE');
    WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('A APARUT O EXCEPTIE ' ||SQLERRM);
END;
/
--BUFFER
DECLARE
    V_DEN VARCHAR2(200);
    V_SAL NUMBER;

BEGIN
    V_DEN := 'ABC';
    SELECT denumire_departament, AVG(salariul)  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;
    FOR i IN 1..1000000 LOOP
    DBMS_OUTPUT.PUT_LINE('DEPARTAMENTUL '||V_DEN||' ARE SALARIUL MEDIU DE '||ROUND(V_SAL,2));
    END LOOP;
EXCEPTION
    WHEN VALUE_ERROR THEN
    DBMS_OUTPUT.PUT_LINE('VALOARE PREA MARE PENTRU VARIABILA RESPECTIVA');
    WHEN TOO_MANY_ROWS THEN
    DBMS_OUTPUT.PUT_LINE('PREA MULTE RANDURI RETURNATE');
    WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('A APARUT O EXCEPTIE ' ||SQLERRM);
END;
/