ovreddy
Active User
Joined: 06 Dec 2004 Posts: 211 Location: Keane Inc., Minneapolis USA.
|
|
|
|
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. |
|