How do I solve this problem of a correlated subquery

Question

We have an employee table with the following attributes :

employee_id
first_name
last_name
email
phone_number
hire_date
job_id
salary
commission_pct
manager_id
department_id

Display the manager name and the salary of the lowest paid employee for that manager. Exclude anyone whose manager is not known.Exclude any groups where the minimum salary is less than 6000.sort the output in descending order of salary. Use alias name "salary"

I have tried the following :

SELECT manager_id , (SELECT MIN(salary) 
                     FROM employees emp 
                     WHERE e.manager_id = emp.employee_id
                     GROUP BY employee_id)
FROM employees e
GROUP BY manager_id;

I don't seem to get correct outputs with this

Answer 1

Your query should be like:

SELECT FIRST_NAME, MIN_EMP_SALARY FROM
(
    SELECT  FIRST_NAME , (SELECT MIN(SALARY) 
                         FROM EMPLOYEES EMP
                         WHERE E.EMPLOYEE_ID = EMP.EMPLOYEE_ID
                         GROUP BY EMPLOYEE_ID) AS MIN_EMP_SALARY
    FROM  EMPLOYEES E
    GROUP BY FIRST_NAME , EMPLOYEE_ID
) OUTER_TABLE WHERE MIN_EMP_SALARY >= 6000
ORDER BY MIN_EMP_SALARY DESC