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

Topo

Realizado por Turtle Learning ®. Última alteração em 2011-08-14