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 reg query on DYNALLOC feature raghuraman123 SYNCSORT 12 Wed Jan 10, 2018 2:42 pm query to fetch record which has only ... maxsubrat DB2 12 Mon Dec 11, 2017 5:03 pm How to query MAXGENS of an existing P... Chuchulo Hatyzak JCL & VSAM 4 Wed Nov 22, 2017 10:23 pm Native SQL Query kishpra DB2 1 Wed Nov 22, 2017 8:38 pm Query on IEFBR14 with GDG Ashishpanpaliya JCL & VSAM 4 Tue Nov 07, 2017 8:34 pm

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