Database - 서브쿼리 실습

=실습1=

SMITH의 급여보다 
더 많이 받는 사원명과 급여를 출력하세요

 

select sal
from emp
where ename = 'SMITH';

select ename,sal
from emp
where sal > (select sal
              from emp
              where ename = 'SMITH')

=실습2=

직급이 CLERK인 사원의 부서의 부서번호와
부서명과 지역을 출력하세요
deptno , dname, loc

 

select deptno
from emp
where job='CLERK'

select deptno,dname,loc
from dept
where deptno in (select deptno
                 from emp
                 where job='CLERK')

=실습3=

이름에 T를 포함하고 있는 사원들과 같은 부서에서
근무하고 있는
사원의 사원번호와 이름을 출력하세요.

 

select distinct deptno
from emp
where ename like ('%T%')

select empno,ename,deptno
from emp
where deptno in (select distinct deptno
                 from emp
                 where ename like ('%T%'))

=실습4=

부서위치가 DALLAS인 모든 사원의 이름,부서번호를 출력하세요

 

select deptno
from dept
where loc = 'DALLAS'

select ename,deptno
from emp 
where deptno = (select deptno
                from dept
                where loc = 'DALLAS')
=실습5=

SALES부서의 모든 사원의 이름과 급여를 출력하세요

 

select deptno
from dept
where dname = 'SALES'

select ename,sal
from emp
where deptno = (select deptno
                from dept
                where dname = 'SALES')

=실습6=

KING이 담당하고 있는 모든 사원의 이름과 급여를 출력하세요

 

select empno
from emp 
where ename = 'KING'

select ename,sal
from emp
where mgr = (select empno
             from emp 
             where ename = 'KING')

=실습7=

자신의 급여가 평균 급여보다 많고
이름에 S가 들어가는 사원과 동일한 부서에서 근무하는
모든 사원의 사원번호,이름 및 급여를 출력하세요

 

select avg(sal)
from emp;

select deptno
from emp
where sal > (select avg(sal)
                      from emp)
                      and ename like ('%S%');

 

select empno,ename,sal
from emp
where deptno =(select deptno
                from emp
                where sal > (select avg(sal)
                from emp)
                and ename like ('%S%'))