Multiple Rows Subquery

1.    Getting max(sal) in each department from emp table? 

     Answer:

select * from emp where sal in(select max(sal) from emp group by deptno);           

2.    Working in sales or research department from emp, dept? 

    Answer:

select ename, deptno from emp

where deptno in(select deptno from dept where dname=’sales’ or dname=’research’);

3.   working as “supervisors” (managers) from emp table using empno, mgr?

   Answer:

select * from emp where empno in(select mgr from emp);

4.    employees who are not working as “supervisors” (managers) from emp using empno, mgr?

     Answer:

select * from emp where empno not in(select nvl(mgr,0) from emp);