Can You Explain This SQL Query - Inline View Included [message #415194] |
Sun, 26 July 2009 03:33  |
peace2009
Messages: 11 Registered: July 2009
|
Junior Member |
|
|
I do not fully understand the following query:
SELECT a.last_name, a.salary, a.department_id, b.salavg
FROM employees a, (SELECT department_id,
AVG(salary) salavg
FROM employees
GROUP BY department_id) b
WHERE a.department_id = b.department_id
AND a.salary > b.salavg;
The inline view can return several records. Can you please tell me in step by step how does this work. I understand the concept of join tables and aliases, etc. I just need to know the mechanism of this code.
Thanks
[Updated on: Sun, 26 July 2009 03:34] Report message to a moderator
|
|
|
|
|
|
|
|
Re: Can You Explain This SQL Query - Inline View Included [message #415268 is a reply to message #415194] |
Mon, 27 July 2009 00:01  |
peace2009
Messages: 11 Registered: July 2009
|
Junior Member |
|
|
The inlive view returns the average salary against each of the avaiable department IDs. And the query returns names of employees in each department that are having salary greater than the average salary of all the employees belonging to the same department as he belongs.
Say, Mr. Abbott and Mr. Babbitt belong to same department SALES and say that they are the only two in that department.
Assume Mr. Abbott's salary is $500 and Mr. Babbitt's salary is $400, so the average of the salaries in department SALES would be 450.
When the query is executed in this case,
Mr. Abbott's departmentid, name, salary would be returned along with the average salary in that department, because his salary is $500 > $450 (average).
|
|
|