IBM Mainframe Forum Index
 
Log In
 
IBM Mainframe Forum Index Mainframe: Search IBM Mainframe Forum: FAQ Register
 

Popular SQL Queries asked in Interviews


IBM Mainframe Forums -> Mainframe Interview Questions
Post new topic   Reply to topic
View previous topic :: View next topic  
Author Message
revathi

New User


Joined: 07 Mar 2005
Posts: 3

PostPosted: Fri Mar 18, 2005 11:06 am
Reply with quote

pls answer to this queries its very urgent

1) list emp details whose salary greater than the lowest salary of an emp belongs to dept no 20?

2) list emp details if more than 10 employees present in dept no 10?

3) find the most recently joined employee in each department?

4) which department has the highest annual renumeration bill?

5) write a correlated sub query to list out the employee who earn more than the average salary of other department?

6)find the nth maximum salary?
Back to top
View user's profile Send private message
ovreddy

Active User


Joined: 06 Dec 2004
Posts: 211
Location: Keane Inc., Minneapolis USA.

PostPosted: Sat Mar 19, 2005 6:51 pm
Reply with quote

Hi Revathi,

I considered standard Oracle provided EMP table as a base table to answer your queries. Here are your answers...

1) list emp details whose salary greater than the lowest salary of an emp belongs to dept no 20?

Ans: select * from emp where sal>(select min(sal) from emp where deptno=20);

2) list emp details if more than 10 employees present in dept no 10?

Ans: select deptno,count(*) NoEMP from emp group by deptno having count(*)>10;

3) find the most recently joined employee in each department?

Ans: select * from emp where (deptno,hiredate) in (select deptno,max(hiredate) from emp group by deptno);

4) which department has the highest annual renumeration bill?

Ans: Question is not clear or related to structure of table.

5) write a correlated sub query to list out the employee who earn more than the average salary of other department?

Ans: select * from emp a where sal>(select avg(sal) from emp b where a.deptno=b.deptno);

6)find the nth maximum salary?

Ans: select sal from emp a where n=(select count(distinct sal) from emp b where a.sal<=b.sal);

Revathi let me know what really you need in 4th query.

Thanks,
Reddy.
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic View Bookmarks
All times are GMT + 6 Hours
Forum Index -> Mainframe Interview Questions

 


Similar Topics
Topic Forum Replies
No new posts Index related queries DB2 5
No new posts Need help with below queries DB2 3
No new posts Running queries against OLTP Database DB2 3
No new posts Selective execution of a queries DB2 9
No new posts Re-write queries to minimize the effe... DB2 6
Search our Forums:

Back to Top