SET SERVEROUTPUT ON;

-- EJERCICIO 1
DECLARE

    V_MAX_SALARY VARCHAR2(10);
    V_AVG_SALARY VARCHAR2(10);
    V_MIN_SALARY VARCHAR2(10);
    V_SUM_SALARY VARCHAR2(10);
    V_TOTAL_EMPLOYEES VARCHAR2(10);

    ERR_CODIGO_ERROR NUMBER;
    ERR_MENSAJE_ERROR VARCHAR2(255);

BEGIN

    SELECT
        TO_CHAR(MAX(SALARY),'$999,999'),
        TO_CHAR(ROUND(AVG(SALARY)),'$999,999'),
        TO_CHAR(MIN(SALARY),'$999,999'),
        TO_CHAR(SUM(SALARY),'$999,999'),
        COUNT(EMPLOYEE_ID)

    INTO
        V_MAX_SALARY,
        V_AVG_SALARY,
        V_MIN_SALARY,
        V_SUM_SALARY,
        V_TOTAL_EMPLOYEES
    FROM EMPLOYEES;

    DBMS_OUTPUT.PUT_LINE(LPAD(('INFORME DE LA EMPRESA ' || SYSDATE) ,200,' '));
    DBMS_OUTPUT.PUT_LINE(LPAD('------------------------------------------------',60,' '));
    DBMS_OUTPUT.PUT_LINE('SALARIO MÁXIMO        SALARIO PROMEDIO        SALARIO MÍNIMO      SALARIO TOTAL');
    DBMS_OUTPUT.PUT_LINE('-------------------------------------------------------------------------------');
    DBMS_OUTPUT.PUT_LINE('  ' || V_MAX_SALARY || '             ' || V_AVG_SALARY || '              ' || V_MIN_SALARY || '           ' || V_SUM_SALARY);
    DBMS_OUTPUT.PUT_LINE('-------------------------------------------------------------------------------');
    DBMS_OUTPUT.PUT_LINE('LOS VALORES CALCULADOS ESTÁN EFECTUADOS SOBRE ' || V_TOTAL_EMPLOYEES || ' EMPLEADOS');

    EXCEPTION
    WHEN NO_DATA_FOUND THEN
        DBMS_OUTPUT.PUT_LINE('LA CONSULTA SELECT NO RECUPERA DATOS');
    WHEN TOO_MANY_ROWS THEN
        DBMS_OUTPUT.PUT_LINE('LA CONSULTA SELECT RETORNA MUCHOS DATOS');
    WHEN OTHERS THEN
        ERR_CODIGO_ERROR := SQLCODE;
        ERR_MENSAJE_ERROR := SQLERRM;
        DBMS_OUTPUT.PUT_LINE('CUALQUIER OTRO ERROR SERÁ CAPTURADO DE IGUAL FORMA');
        DBMS_OUTPUT.PUT_LINE('CODIGO : ' || ERR_CODIGO_ERROR);
        DBMS_OUTPUT.PUT_LINE('ERROR  : ' || ERR_MENSAJE_ERROR);
END;

-- EJERCICIO 2

DECLARE

    V_DEPARTMENT_NAME1 DEPARTMENTS.DEPARTMENT_NAME%TYPE;
    V_COUNT_DEPARTMENT1 NUMBER;
    V_DEPARTMENT_NAME2 DEPARTMENTS.DEPARTMENT_NAME%TYPE;
    V_COUNT_DEPARTMENT2 NUMBER;

    ERR_CODIGO_ERROR NUMBER;
    ERR_MENSAJE_ERROR VARCHAR2(255);

BEGIN

    SELECT
        D.DEPARTMENT_NAME,
        COUNT(E.DEPARTMENT_ID)
    INTO
        V_DEPARTMENT_NAME1,
        V_COUNT_DEPARTMENT1

    FROM DEPARTMENTS D
    JOIN EMPLOYEES E
    ON E.DEPARTMENT_ID = D.DEPARTMENT_ID
    WHERE ROWNUM = 1
    group by D.DEPARTMENT_NAME, DEPARTMENT_NAME
    HAVING COUNT(E.DEPARTMENT_ID) = (SELECT
                                         MIN(COUNT(E.DEPARTMENT_ID))
                                     FROM DEPARTMENTS D
                                     JOIN EMPLOYEES E
                                     ON E.DEPARTMENT_ID = D.DEPARTMENT_ID
                                     group by D.DEPARTMENT_NAME);

    SELECT
        D.DEPARTMENT_NAME,
        COUNT(E.DEPARTMENT_ID)
    INTO
        V_DEPARTMENT_NAME2,
        V_COUNT_DEPARTMENT2
    FROM DEPARTMENTS D
    JOIN EMPLOYEES E
    ON E.DEPARTMENT_ID = D.DEPARTMENT_ID
    group by D.DEPARTMENT_NAME, DEPARTMENT_NAME
    HAVING COUNT(E.DEPARTMENT_ID) = (SELECT
                                        MAX(COUNT(E.DEPARTMENT_ID))
                                    FROM DEPARTMENTS D
                                    JOIN EMPLOYEES E
                                    ON E.DEPARTMENT_ID = D.DEPARTMENT_ID
                                    group by D.DEPARTMENT_NAME);

    DBMS_OUTPUT.PUT_LINE('INFORME PARA REDISTRIBUIR EMPLEADOS');
    DBMS_OUTPUT.PUT_LINE('-----------------------------------');
    DBMS_OUTPUT.PUT_LINE(' ');
    DBMS_OUTPUT.PUT_LINE('DEPARTAMENTO              TOTAL EMPLEADOS');
    DBMS_OUTPUT.PUT_LINE('-----------------------------------------');
    DBMS_OUTPUT.PUT_LINE(V_DEPARTMENT_NAME1 || '                 ' || V_COUNT_DEPARTMENT1);
    DBMS_OUTPUT.PUT_LINE(V_DEPARTMENT_NAME2 || '                      ' || V_COUNT_DEPARTMENT2);

    EXCEPTION
    WHEN NO_DATA_FOUND THEN
        DBMS_OUTPUT.PUT_LINE('LA CONSULTA SELECT NO RECUPERA DATOS');
    WHEN TOO_MANY_ROWS THEN
        DBMS_OUTPUT.PUT_LINE('LA CONSULTA SELECT RETORNA MUCHOS DATOS');
    WHEN OTHERS THEN
        ERR_CODIGO_ERROR := SQLCODE;
        ERR_MENSAJE_ERROR := SQLERRM;
        DBMS_OUTPUT.PUT_LINE('CUALQUIER OTRO ERROR SERÁ CAPTURADO DE IGUAL FORMA');
        DBMS_OUTPUT.PUT_LINE('CODIGO : ' || ERR_CODIGO_ERROR);
        DBMS_OUTPUT.PUT_LINE('ERROR  : ' || ERR_MENSAJE_ERROR);

END;

-- EJERCICIO 3

DECLARE

    V_NOMBRE VARCHAR2(45);
    V_ANIOS NUMBER;
    V_SALARIO VARCHAR2(10);
    V_BONO VARCHAR2(10);

    ERR_CODIGO_ERROR NUMBER;
    ERR_MENSAJE_ERROR VARCHAR2(255);

BEGIN

    SELECT
        FIRST_NAME || ' ' || LAST_NAME,
        TRUNC((SYSDATE - HIRE_DATE)/365),
        TO_CHAR(SALARY,'$999,999'),
        TO_CHAR(SALARY*((TRUNC((SYSDATE - HIRE_DATE)/365))/100),'$999,999')
    INTO
        V_NOMBRE,
        V_ANIOS,
        V_SALARIO,
        V_BONO
    FROM EMPLOYEES
    WHERE TRUNC((SYSDATE - HIRE_DATE)/365) = (SELECT MAX(TRUNC((SYSDATE - HIRE_DATE)/365))
                                              FROM EMPLOYEES);

    DBMS_OUTPUT.PUT_LINE('INFORME PAGO POR ANTIGUEDAD');
    DBMS_OUTPUT.PUT_LINE('---------------------------');
    DBMS_OUTPUT.PUT_LINE('EMPLEADO      AÑOS TRABAJADOS     SALARIO ACTUAL      VALOR BONO');
    DBMS_OUTPUT.PUT_LINE('----------------------------------------------------------------');
    DBMS_OUTPUT.PUT_LINE(V_NOMBRE || '         ' || V_ANIOS || '             ' || V_SALARIO || '         ' || V_BONO);


    EXCEPTION
    WHEN NO_DATA_FOUND THEN
        DBMS_OUTPUT.PUT_LINE('LA CONSULTA SELECT NO RECUPERA DATOS');
    WHEN TOO_MANY_ROWS THEN
        DBMS_OUTPUT.PUT_LINE('LA CONSULTA SELECT RETORNA MUCHOS DATOS');
    WHEN OTHERS THEN
        ERR_CODIGO_ERROR := SQLCODE;
        ERR_MENSAJE_ERROR := SQLERRM;
        DBMS_OUTPUT.PUT_LINE('CUALQUIER OTRO ERROR SERÁ CAPTURADO DE IGUAL FORMA');
        DBMS_OUTPUT.PUT_LINE('CODIGO : ' || ERR_CODIGO_ERROR);
        DBMS_OUTPUT.PUT_LINE('ERROR  : ' || ERR_MENSAJE_ERROR);

END;

-- EJERCICIO 4

DECLARE

    V_MANAGER_ID EMPLOYEES.MANAGER_ID%TYPE;
    V_NAME VARCHAR2(50);
    V_EMPLEADOS_CARGO NUMBER;
    V_BONO VARCHAR2(10);

    ERR_CODIGO_ERROR NUMBER;
    ERR_MENSAJE_ERROR VARCHAR2(255);

BEGIN

    SELECT
        MANAGER_ID,
        COUNT(EMPLOYEE_ID),
        TO_CHAR(COUNT(EMPLOYEE_ID)*1000,'$999,999')
    INTO
        V_MANAGER_ID,
        V_EMPLEADOS_CARGO,
        V_BONO

    FROM EMPLOYEES
    GROUP BY MANAGER_ID
    HAVING COUNT(EMPLOYEE_ID) = (SELECT MAX(COUNT(MANAGER_ID))
                                 FROM EMPLOYEES
                                 GROUP BY MANAGER_ID);

    SELECT
        FIRST_NAME || ' ' || LAST_NAME
    INTO
        V_NAME
    FROM EMPLOYEES
    WHERE EMPLOYEE_ID = (SELECT MANAGER_ID
                         FROM EMPLOYEES
                         GROUP BY MANAGER_ID
                         HAVING COUNT(EMPLOYEE_ID) = (SELECT MAX(COUNT(MANAGER_ID))
                                                      FROM EMPLOYEES
                                                      GROUP BY MANAGER_ID));

    DBMS_OUTPUT.PUT_LINE('INFORME PAGO ASIGNACION JEFE CON MAS EMPLEADOS');
    DBMS_OUTPUT.PUT_LINE('----------------------------------------------');
    DBMS_OUTPUT.PUT_LINE('ID. JEFE: ' || V_MANAGER_ID || '  NOMBRE: ' || V_NAME);
    DBMS_OUTPUT.PUT_LINE(' ');
    DBMS_OUTPUT.PUT_LINE('EMPLEADOS A SU CARGO                VALOR ASIGNACION');
    DBMS_OUTPUT.PUT_LINE('----------------------------------------------------');
    DBMS_OUTPUT.PUT_LINE('         ' || V_EMPLEADOS_CARGO || '                            ' || V_BONO);

    EXCEPTION
    WHEN NO_DATA_FOUND THEN
        DBMS_OUTPUT.PUT_LINE('LA CONSULTA SELECT NO RECUPERA DATOS');
    WHEN TOO_MANY_ROWS THEN
        DBMS_OUTPUT.PUT_LINE('LA CONSULTA SELECT RETORNA MUCHOS DATOS');
    WHEN OTHERS THEN
        ERR_CODIGO_ERROR := SQLCODE;
        ERR_MENSAJE_ERROR := SQLERRM;
        DBMS_OUTPUT.PUT_LINE('CUALQUIER OTRO ERROR SERÁ CAPTURADO DE IGUAL FORMA');
        DBMS_OUTPUT.PUT_LINE('CODIGO : ' || ERR_CODIGO_ERROR);
        DBMS_OUTPUT.PUT_LINE('ERROR  : ' || ERR_MENSAJE_ERROR);

END;