Soluções dos exercícios sobre subconsultas |
1.
select job,ename,sal from emp where (sal,job) in (select max(sal),job from emp group by job);
2.
select job,ename,sal from emp where (sal,job) in (select min(sal),job from emp group by job) order by sal;
3.
select ename,hiredate,deptno from emp where (hiredate,deptno) in (select max(hiredate),deptno from emp group by deptno) order by hiredate;
4.
select e.ename, e.sal, e.deptno from emp e where e.sal > (select avg(e2.sal) from emp e2 where e2.deptno= e.deptno) order by e.deptno;
5.
select d.deptno, d.dname from dept d where not exists (select e.empno from emp e where e.deptno = d.deptno);
6.
define anual = sal*14+nvl(comm,0); select deptno, sum(&anual) from emp group by deptno having sum(&anual)= (select max(sum(&anual)) from emp group by deptno);
7.
select to_char(hiredate,'YYYY'), count(empno) from emp group by to_char(hiredate,('YYYY')) having count(empno) = (select max(count(empno)) from emp group by to_char(hiredate,'YYYY'));
8.
select e.ename, e.sal, e.deptno, avg(a.sal) from emp e, emp a where e.deptno=a.deptno and e.sal > (select avg(sal) from emp where deptno=e.deptno) group by e.ename, e.sal, e.deptno;
Outra solução poderá ser a apresentada a seguir, que usa um subquery na clausula SELECT:
select e.ename, e.sal, e.deptno, (select avg(e2.sal) from emp e2 where e2.deptno= e.deptno) from emp e where e.sal > (select avg(e2.sal) from emp e2 where e2.deptno= e.deptno) order by e.deptno;
9.
select ename, hiredate, '*' from emp where hiredate = (select max(hiredate) from emp) union select ename, hiredate, ' ' from emp where hiredate <> (select max(hiredate) from emp);
10.
select * from (select * from emp order by sal desc) where rownum <= 3
Realizado por Turtle Learning ®. Última alteração em 2011-08-14