1.2 - Sub Consultas

  • Published on
    21-Apr-2015

  • View
    10

  • Download
    4

Embed Size (px)

Transcript

<p>6/5/2010</p> <p>Sub-ConsultasCludio Ratke</p> <p>Conceitos</p> <p>Uma subconsulta uma instruo SELECT que incorporada em outra instruo SELECT.</p> <p>ExemplosWHERE</p> <p>SELECT FROM WHEREFIELD_LIST</p> <p>lista_select tabela(s) operador (SELECT lista_select colunax FROM tabela); lista_select, (SELECT lista_select xFROM tabela) tabela(s) lista_select (SELECT xFROM</p> <p>SELECT FROM</p> <p>FROM</p> <p>SELECT FROM</p> <p>tabela)</p> <p>HAVING</p> <p>SELECT lista_select, FUNCTION FROM tabela(s) GROUP BY lista_select HAVING FUNCTION (SELECT xFROM tabela)</p> <p>1</p> <p>6/5/2010</p> <p>Usando Sub-Consulta na clusula WhereSELECT ename FROM emp WHERE sal &gt;2975</p> <p>(SELECT sal FROM emp WHERE empno=7566);</p> <p>salrio do funcionrio 7566. A consulta externa obtm o resultado da consulta interna e o utiliza para exibir todos os funcionrios que recebem mais que essa quantia</p> <p>Usando Sub-Consultas na clusula WhereSELECT FROM WHERE ename, job emp job = (SELECT FROM WHERE sal &gt; (SELECT FROM WHERE</p> <p>CLERK</p> <p>job emp empno = 7369)1100</p> <p>AND</p> <p>sal emp empno = 7876);</p> <p>Usando Sub-Consulta na clusula WhereSELECT FROM WHERE ename, job, sal emp 800 sal = (SELECT MIN(sal) FROM emp);</p> <p>2</p> <p>6/5/2010</p> <p>Usando Sub-Consulta na clusula WhereSELECT ename, sal FROM emp WHERE (job, deptno) in (SELECT job, deptno FROM emp WHERE ename = 'ALLEN')</p> <p>Usando Sub-Consulta na clusula FromSELECT a.ename, a.sal, a.deptno FROM (SELECT * FROM emp) a</p> <p>SELECT a.ename, a.sal, a.deptno, b.salavg FROM emp a JOIN (SELECT deptno, avg(sal) salavg FROM emp GROUP BY deptno) b USING (deptno) WHERE a.sal &gt; b.salavg;</p> <p>Usando Sub-Consulta - clusula HavingSELECT deptno, MIN(sal) FROM emp GROUP BY deptno HAVING MIN(sal) &gt; (SELECT MIN(sal) FROM emp WHERE deptno = 20);</p> <p>3</p>