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

Author Message
revathi

New User

Joined: 07 Mar 2005
Posts: 3

 Posted: Fri Mar 18, 2005 11:06 am    Post subject: Popular SQL Queries asked in Interviews 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?

ovreddy

Active User

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

 Posted: Sat Mar 19, 2005 6:51 pm    Post subject: 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.
 All times are GMT + 6 Hours
 Page 1 of 1

Search our Forum:

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

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