Liana Liana - 4 months ago 12
SQL Question

ORACLE SQL: Show the lowest salary in the department with the highest average salary

NOTE, this is a homework question.

Please show the department number and the lowest salary in the department whose average salary is the highest average salary.

This is what I have so far,

SELECT DEPARTMENT_ID, MAX_AVG_SALARY
FROM
(SELECT DEPARTMENT_ID, AVG(SALARY) AS MAX_AVG_SALARY
FROM EMPLOYEES
GROUP BY DEPARTMENT_ID)
WHERE MAX_AVG_SALARY =
(SELECT MAX(MAX_AVG_SALARY)
FROM
(SELECT DEPARTMENT_ID,
AVG(SALARY) AS MAX_AVG_SALARY
FROM EMPLOYEES
GROUP BY DEPARTMENT_ID
));


I can get the department_id with the highest salary, but then how do I find the lowest salary in the same department?

Please help!

Thanks!

Answer
SELECT MINIMUM_SALARY,DEPARTMENT_ID
FROM
(
 SELECT AVG(SALARY) AS AVERAGE_SALARY,
        MIN(SALARY) AS MINIMUM_SALARY,
        DEPARTMENT_ID
  FROM EMPLOYEES
 GROUP BY DEPARTMENT_ID
)EMPLOYEE_AGGREGATED
WHERE
    AVERAGE_SALARY = (SELECT MAX(AVG(SALARY)) FROM EMPLOYEES GROUP BY DEPARTMENT_ID)