/*
Sa se scrie un bloc PL/SQL care printeaza angajatii care detin venitul maxim
din fiecare departament sau detin un salariu peste media job-ului
lor cu cel putin 15%. Sa se poata face distinctia intre ei.
*/
SET serveroutput ON;
BEGIN
    FOR angajat IN (SELECT e.ename,
                           e.sal + Nvl(e.comm, 0) Venit,
                           e.job,
                           'Venit Maxim'          Criteriu
                    FROM   emp e
                    WHERE  e.sal + Nvl(e.comm, 0) IN (SELECT Max(sal + Nvl(comm,
                                                                 0))
                                                      FROM   emp
                                                      GROUP  BY deptno)
                    UNION
                    SELECT e.ename,
                           e.sal + Nvl(e.comm, 0) Venit,
                           e.job,
                           'Venit Peste Medie'    Criteriu
                    FROM   emp e
                    WHERE  e.sal + Nvl(e.comm, 0) > (SELECT 1.15 * Avg(
                                                            sal + Nvl(comm, 0))
                                                     FROM   emp
                                                     WHERE  job = e.job)) LOOP
        dbms_output.Put_line(Rpad(angajat.ename, 30)
                             || Rpad(angajat.venit, 10)
                             || Rpad(angajat.job, 10)
                             || Rpad(angajat.criteriu, 20));
    END LOOP;
END;