0

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

  • which DBMS are using? – Joe Taras Mar 17 '14 at 8:07
  • MySQL @JoeTaras – user2493976 Mar 17 '14 at 8:28
0

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 
  • This query is not working – user2493976 Mar 17 '14 at 8:30
  • Sorry, the above is for Oracle, I edited it to fit in MySQL, no that big difference anyway, just I added alias to the from table "OUTER_TABLE", please copy the above and try again.. – Adel Mar 17 '14 at 8:43
  • Yea its working now....can you please explain the difference between what my query was doing and this query....what was I doing wrong? – user2493976 Mar 17 '14 at 8:51
  • Sure, in your query you were only bringing the manager with the min salary, but in my query I got what you wrote, then excluding the salary of 6000 but out of the () if you note above, the "WHERE MIN_EMP_SALARY >= 6000" this has only to be extracted after to extract the in between () select, after that just order as you need.. – Adel Mar 17 '14 at 9:04

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.