Portal | Manuals | References | Downloads | Info | Programs | JCLs | Mainframe wiki | Quick Ref

Author Message
Currently Banned

New User

Joined: 26 May 2005
Posts: 45
Location: gurgaon

 Posted: Tue Dec 27, 2005 6:42 pm    Post subject: SQL Query 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

priyesh.agrawal

Senior Member

Joined: 28 Mar 2005
Posts: 1452
Location: Chicago, IL

Posted: Tue Dec 27, 2005 7:30 pm    Post subject: Re: SQL Query

 Code: SELECT EMP, SAL FROM WHERE SAL > (SELECT AVG(SAL) FROM WHERE DEPT= 'first') AND DEPT= 'first'; UNION          SELECT EMP, SAL FROM WHERE SAL > (SELECT AVG(SAL) FROM WHERE DEPT= 'second') AND DEPT= 'second'; UNION SELECT EMP, SAL FROM WHERE SAL > (SELECT AVG(SAL) FROM WHERE DEPT= 'third') AND DEPT= 'third'; UNION SELECT EMP, SAL FROM WHERE SAL > (SELECT AVG(SAL) FROM WHERE DEPT= 'fourth') AND DEPT= 'fourth'; UNION SELECT EMP, SAL FROM WHERE SAL > (SELECT AVG(SAL) FROM WHERE DEPT= 'fifth') AND DEPT= 'fifth';

The query above will extract all the employees having more than AVG salary dept wise....

Regards,

Priyesh.
khamarutheen

Active Member

Joined: 23 Aug 2005
Posts: 680
Location: NJ

Posted: Tue Dec 27, 2005 7:40 pm    Post subject: Query

Hi frnd,

 Code: select ename from employee where sal>=(select avg(sal) a from dept);

Use the above query
priyesh.agrawal

Senior Member

Joined: 28 Mar 2005
Posts: 1452
Location: Chicago, IL

Posted: Tue Dec 27, 2005 7:49 pm    Post subject: Re: SQL Query

 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.
khamarutheen

Active Member

Joined: 23 Aug 2005
Posts: 680
Location: NJ

Posted: Tue Dec 27, 2005 7:57 pm    Post subject: query

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...
 All times are GMT + 6 Hours
 Page 1 of 1

Search our Forum:

 Topic Author Forum Replies Posted Similar Topics How to force a select query to abend neo4u DB2 5 Mon Apr 23, 2018 1:36 pm Query to compare 2 values of 1 column... Poha Eater DB2 13 Fri Mar 09, 2018 10:45 am How to replace the below query? pkmurali DB2 12 Tue Feb 27, 2018 9:51 pm Need Guidance regarding UPDATE query Poha Eater DB2 3 Mon Feb 12, 2018 11:35 pm Query to skip Max time-stamp and fetc... vickey_dw DB2 8 Sun Jan 28, 2018 1:09 am

 © 2003-2017 IBM MAINFRAME Software Support Division
 Job Vacancies | Forum Rules | Bookmarks | Subscriptions | FAQ | Polls | Contact Us