--16
SELECT
    D.DEPARTMENT_ID,
    D.DEPARTMENT_NAME,
    J.JOB_ID,
    J.JOB_TITLE,
    SUM(E.SALARY),
    COUNT(*)
FROM
    EMPLOYEES     E,
    DEPARTMENTS   D,
    JOBS          J
WHERE
    E.DEPARTMENT_ID = D.DEPARTMENT_ID
    AND J.JOB_ID = E.JOB_ID
GROUP BY
    J.JOB_ID,
    J.JOB_TITLE,
    D.DEPARTMENT_ID,
    D.DEPARTMENT_NAME
ORDER BY
    1;

--17

SELECT
    D.DEPARTMENT_ID,
    D.DEPARTMENT_NAME,
    MIN(E.SALARY) MINIM
FROM
    EMPLOYEES     E,
    DEPARTMENTS   D
WHERE
    E.DEPARTMENT_ID = D.DEPARTMENT_ID
GROUP BY
    D.DEPARTMENT_ID,
    D.DEPARTMENT_NAME
HAVING
    AVG(SALARY) = (
        SELECT
            MAX(AVG(E.SALARY))
        FROM
            EMPLOYEES     E,
            DEPARTMENTS   D
        WHERE
            E.DEPARTMENT_ID = D.DEPARTMENT_ID
        GROUP BY
            D.DEPARTMENT_ID
    );

--18
--a

SELECT
    D.DEPARTMENT_ID,
    D.DEPARTMENT_NAME,
    COUNT(*) ANGAJATI
FROM
    EMPLOYEES     E,
    DEPARTMENTS   D
WHERE
    E.DEPARTMENT_ID = D.DEPARTMENT_ID
GROUP BY
    D.DEPARTMENT_ID,
    DEPARTMENT_NAME
HAVING
    COUNT(*) <= 4;

--b

SELECT
    D.DEPARTMENT_ID,
    D.DEPARTMENT_NAME,
    COUNT(*) ANGAJATI
FROM
    EMPLOYEES     E,
    DEPARTMENTS   D
WHERE
    E.DEPARTMENT_ID = D.DEPARTMENT_ID
GROUP BY
    D.DEPARTMENT_ID,
    DEPARTMENT_NAME
HAVING
    COUNT(*) = (
        SELECT
            MAX(COUNT(*))
        FROM
            DEPARTMENTS   D
            JOIN EMPLOYEES     E ON ( D.DEPARTMENT_ID = E.DEPARTMENT_ID )
        GROUP BY
            E.DEPARTMENT_ID
    );

--19

SELECT
    EMPLOYEE_ID,
    LAST_NAME,
    TO_CHAR(HIRE_DATE, 'dd')
FROM
    EMPLOYEES
WHERE
    TO_CHAR(HIRE_DATE, 'dd') = (
        SELECT
            TO_CHAR(HIRE_DATE, 'dd')
        FROM
            EMPLOYEES
        GROUP BY
            TO_CHAR(HIRE_DATE, 'dd')
        HAVING
            COUNT(*) = (
                SELECT
                    MAX(COUNT(*))
                FROM
                    EMPLOYEES
                GROUP BY
                    TO_CHAR(HIRE_DATE, 'dd')
            )
    );

--20

SELECT
    COUNT(COUNT(*))
FROM
    EMPLOYEES
GROUP BY
    DEPARTMENT_ID
HAVING
    COUNT(*) > 15;

--21

SELECT
    DEPARTMENT_ID,
    SUM(SALARY) SUMA
FROM
    EMPLOYEES
GROUP BY
    DEPARTMENT_ID
HAVING COUNT(*) > 10
       AND DEPARTMENT_ID != 30
ORDER BY
    2 ASC;

--22

SELECT
    D.DEPARTMENT_ID,
    D.DEPARTMENT_NAME,
    COUNT(*) ANGAJATI,
    ROUND(AVG(SALARY)) SAL_MEDIU
FROM
    DEPARTMENTS   D,
    EMPLOYEES     E
WHERE
    D.DEPARTMENT_ID = E.DEPARTMENT_ID (+)
GROUP BY
    D.DEPARTMENT_ID,
    D.DEPARTMENT_NAME;

--23

SELECT
    L.CITY,
    D.DEPARTMENT_NAME   DEPARTMENT,
    J.JOB_TITLE         JOB,
    SUM(E.SALARY) SUM_SALARY
FROM
    EMPLOYEES     E,
    JOBS          J,
    DEPARTMENTS   D,
    LOCATIONS     L
WHERE
    E.DEPARTMENT_ID = D.DEPARTMENT_ID
    AND L.LOCATION_ID = D.LOCATION_ID
    AND E.JOB_ID = J.JOB_ID
GROUP BY
    D.DEPARTMENT_ID,
    D.DEPARTMENT_NAME,
    J.JOB_TITLE,
    L.CITY
HAVING
    D.DEPARTMENT_ID > 80;

--24

SELECT
    E.EMPLOYEE_ID,
    E.LAST_NAME
FROM
    JOB_HISTORY   JH,
    EMPLOYEES     E
WHERE
    JH.EMPLOYEE_ID = E.EMPLOYEE_ID
GROUP BY
    E.EMPLOYEE_ID,
    E.LAST_NAME
HAVING
    COUNT(*) >= 2;

--25

SELECT
    AVG(NVL(COMMISSION_PCT, 0)) AVERAGE
FROM
    EMPLOYEES;