Resolved: SQL Task- getting the names of all the managers who have an AVG salary of 85k+
Hello Martin,
I just came up with a random task and I need your help with it. I tried a few times but I am getting an error.
The task is :
Display the list names of all the managers along with their employee number, department number, department name and salary where the salary should be greater than 85000.
I have used the following code:
SELECT e.emp_no, CONCAT(e.first_name, e.last_name) AS EMP_NAME, dm.dept_no, d.dept_name, s.salary FROM t_employees e
JOIN t_departments d ON dm.dept_no=d.dept_no
JOIN t_dept_manager dm ON e.emp_no=dm.emp_no
JOIN t_salaries s ON e.emp_no=s.emp_no
GROUP BY e.emp_no
HAVING s.salary>85000
ORDER BY s.salary;
This is the error that I am getting. Please help me on this.
Regards,
Archie
Hi Archie!
Thanks for reaching out.
Although we can join not just two but several tables at once, we have to be careful about the order in which we provide the matching columns. This is a requirement for the SQL Optimizer to be able to "understand" what we are asking for, so to speak.
In this particular query, please focus on this part of the FROM and ON clauses:
FROM t_employees e JOIN t_departments d ON dm.dept_no=d.dept_no
In the ON clause you are referring to the t_departments table through the alias d. While it is unclear to the computer which is the "dm" alias/table you are referring to. Instead, you've designated t_employees. However, we don't have dm.dept_no in the t_employees table. Therefore, what you need to do is re-organize your joins in a way that it is always clear for the computer which table you are referring to while composing the conditions in the ON clauses.
Please try this out and let us know if it solves the problem or if you need further assistance. Thank you.
Hope this helps.
Best,
Martin
Hello Martin,
Thanks for helping me out on this one. I changed the code and got a result of 22 records. Not sure whether it's correct so do share the solution with me.
1) Code:
SELECT e.emp_no, CONCAT(e.first_name,' ',e.last_name) AS Employee_Name, dm.dept_no, d.dept_name, s.salary FROM t_dept_manager dm
JOIN t_departments d ON dm.dept_no=d.dept_no
JOIN t_employees e ON dm.emp_no=e.emp_no
JOIN t_salaries s ON e.emp_no=s.emp_no
GROUP BY e.emp_no
HAVING s.salary>85000
ORDER BY s.salary;
2) I had already used the t_dept_manager dm in my query. I know it was not right after the FROM CLAUSE but I had used it later. Why did the SQL Optimizer / Computer had difficulty with that?
Regards,
Archie
Hi Archie!
Thanks for reaching out.
-
Apparently, your code delivers an output. As long as the salary values quoted in it are greater than 85000, then yes, your code is a valid solution to the task you are responding to.
-
Because of the order in which you had joined the tables. Order doesn't matter in the sense that you should obtain identical answer if you first join the t_salaries table to t_empoyees. Nevertheless, you have to keep an eye on the matching columns (and the aliases of the tables you are joining). You had used
dm.dept_no=d.dept_no
while joining
t_employees e JOIN t_departments
Don't forget that t_employees contains no dept_no field, hence the error messages you were encountering previously!
Hope this helps.
Best,
Martin