Home » SQL & PL/SQL » SQL & PL/SQL » Can You Explain This SQL Query - Inline View Included (SQL)
Can You Explain This SQL Query - Inline View Included [message #415194] Sun, 26 July 2009 03:33 Go to next message
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 #415203 is a reply to message #415194] Sun, 26 July 2009 05:43 Go to previous messageGo to next message
Michel Cadot
Messages: 64137
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
1/ What returns the inline view?
2/ How this inline view is related with the table?
3/ So what does return the query?

Regards
Michel
Re: Can You Explain This SQL Query - Inline View Included [message #415216 is a reply to message #415203] Sun, 26 July 2009 07:33 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
The SQL will list all of the students - by subject - who try to get others to do the homework for them.
Re: Can You Explain This SQL Query - Inline View Included [message #415253 is a reply to message #415216] Sun, 26 July 2009 16:21 Go to previous messageGo to next message
Littlefoot
Messages: 20900
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
./fa/1964/0/
Re: Can You Explain This SQL Query - Inline View Included [message #415254 is a reply to message #415194] Sun, 26 July 2009 16:37 Go to previous messageGo to next message
peace2009
Messages: 11
Registered: July 2009
Junior Member
I figured it out. By the way, its not a homework.

Thanks for taking time to read my thread though.

Have good day Smile
Re: Can You Explain This SQL Query - Inline View Included [message #415261 is a reply to message #415254] Sun, 26 July 2009 23:39 Go to previous messageGo to next message
Michel Cadot
Messages: 64137
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
And so the answer is?

Forum Guide
If you found an answer yourself, post it. That way we know the issue is resolved and we might learn from it.


Regards
Michel

[Updated on: Sun, 26 July 2009 23:40]

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 Go to previous message
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).
Previous Topic: open / close cursor
Next Topic: create table query with auto increment for one column
Goto Forum:
  


Current Time: Thu Dec 08 06:31:05 CST 2016

Total time taken to generate the page: 0.11570 seconds