Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

Popular SQL Queries asked in Interviews

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> Mainframe Interview Questions
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    Post subject: Popular SQL Queries asked in Interviews
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    Post subject:
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    IBMMAINFRAMES.com Support Forums -> Mainframe Interview Questions All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts Need help with below queries ashek15 DB2 3 Thu Mar 23, 2017 1:38 am
No new posts Running queries against OLTP Database A_programmers DB2 3 Tue Jul 14, 2015 4:51 pm
No new posts Selective execution of a queries Delip DB2 9 Fri Jan 16, 2015 3:51 pm
No new posts Re-write queries to minimize the effe... Anil Khanna DB2 6 Mon Aug 04, 2014 1:49 pm
No new posts Interview Questions asked in IBM trushant.w Mainframe Interview Questions 3 Fri Apr 18, 2014 9:33 am


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us