|
View previous topic :: View next topic
|
| Author |
Message |
babuprasad_g
Currently Banned
New User
.jpg)
Joined: 26 May 2005
Posts: 45
Location: gurgaon
|
|
|
|
Hi folks,
There are 5 departments for each department there is few no of employees.
Extract all the employees for each departments Condition is( salary is greater than average salary of each department).
Please give me the query for this question.
Thanks & Regards
Prasad |
|
| Back to top |
|
 |
priyesh.agrawal
Senior Member
Joined: 28 Mar 2005
Posts: 1448
Location: Chicago, IL
|
|
|
|
| Code: |
SELECT EMP, SAL FROM <TABLE> WHERE SAL >
(SELECT AVG(SAL) FROM <TABLE> WHERE DEPT= 'first') AND DEPT= 'first';
UNION
SELECT EMP, SAL FROM <TABLE> WHERE SAL >
(SELECT AVG(SAL) FROM <TABLE> WHERE DEPT= 'second') AND DEPT= 'second';
UNION
SELECT EMP, SAL FROM <TABLE> WHERE SAL >
(SELECT AVG(SAL) FROM <TABLE> WHERE DEPT= 'third') AND DEPT= 'third';
UNION
SELECT EMP, SAL FROM <TABLE> WHERE SAL >
(SELECT AVG(SAL) FROM <TABLE> WHERE DEPT= 'fourth') AND DEPT= 'fourth';
UNION
SELECT EMP, SAL FROM <TABLE> WHERE SAL >
(SELECT AVG(SAL) FROM <TABLE> WHERE DEPT= 'fifth') AND DEPT= 'fifth'; |
The query above will extract all the employees having more than AVG salary dept wise....
Regards,
Priyesh. |
|
| Back to top |
|
|
khamarutheen
Active Member
Joined: 23 Aug 2005
Posts: 677
Location: NJ
|
|
|
|
Hi frnd,
| Code: |
| select ename from employee where sal>=(select avg(sal) a from dept); |
Use the above query |
|
| Back to top |
|
|
priyesh.agrawal
Senior Member
Joined: 28 Mar 2005
Posts: 1448
Location: Chicago, IL
|
|
|
|
| Quote: |
_________________
CORRECT ME IF I M WRONG |
khamarutheen....Is that your permanent Signature Line.
Not sure, If I misinterpreted the requirements...Well, If I did, your query seems to be very effiecient on TIME as well as CPU utilization.
Regards,
Priyesh. |
|
| Back to top |
|
|
khamarutheen
Active Member
Joined: 23 Aug 2005
Posts: 677
Location: NJ
|
|
|
|
Hi priyesh,
| Quote: |
| Is that your permanent Signature Line. |
S. since i wanna to enter each time while solving the problem. i made as permanent...  |
|
| Back to top |
|
|
|
|
