Popular SQL Queries asked in Interviews

revathi · New User Joined: 07 Mar 2005 Posts: 3

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 · 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.