[SQL] Query 예제

.SUB Query(서브 쿼리)

Q.'JONES'의 부서명을 출력하세요
select deptno from emp where ename='JONES';

select dname from dept where deptno=20; 
          
 JOIN : select e.ename, e.deptno, d.dname 
 from emp e, dept d where e.deptno=d.deptno and e.ename='JONES';
 SubQuery:select dname from dept
 where deptno=(select deptno from emp where ename='JONES');

Q. CLARK과 같은 부서에서 근무하는 사원의 이름과
부서명을 출력하는 쿼리문을 작성하세요
단,'CLARK'은 제외하세요

select ename,deptno from emp 
where deptno =(select deptno from emp where ename = 'CLARK')
and ename <> 'CLARK';      
          
JOIN:select e.ename, ee.deptno from emp e, emp ee
where e.deptno = ee.deptno and e.ename = 'CLARK' and ee.ename <> 'CLARK'; 

Q. WARD와 동일한 직급을 가진 사원의 이름과 월급을 출력하는 쿼리문을 작성하시오.

select ename,sal,job from emp 
where job = (select job from emp where ename ='WARD')
and ename <> 'WARD'; 
          
JOIN : select e.ename, e.sal,e.job from emp e, emp ee
where e.job= ee.job and ee.ename = 'WARD'
and e.ename <>'WARD';

Q. 평균 급여보다 많이 받는 사원의 이름과 급여, 부서번호를 출력하되
급여가 많은 순서로 출력하세요

select ename, sal, deptno from emp
where sal > (select avg(sal) from emp)
order by sal desc;

.단일행 Sub Query

Q. 자신의 직속 상관이 'KING'인사원의 이름과 급여를 출력하세요

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

select ee.ename,ee.sal from emp e, emp ee
where e.empno = ee.mgr and e.ename = 'KING';

Q. 부서번호가 10인 사원들 중에서 최대 급여를 받는 사원의 이름과 월급, 직급을 출력하시오.

select ename,sal,job,deptno from emp
where sal = (select MAX(sal) from emp where deptno=10); 

Q. 'DALLAS'에서 근무하는 사원의 이름, 부서번호, 근무지를 출력하는
서브 쿼리문을 작성하시오.

select ename,emp.deptno,loc 
from emp, dept   
where emp.deptno = (select deptno from dept where loc = 'DALLAS')
and loc = 'DALLAS'; 

Q. 'CLARK'보다 먼저 입사한 사원의 이름과 월급, 입사일 출력하세요 (2가지)

select ename,sal,hiredate from emp
where hiredate < (select hiredate from emp where ename = 'CLARK');       
       
select e.ename,e.sal,e.hiredate from emp e, emp ee 
where e.hiredate < ee.hiredate and ee.ename = 'CLARK';

. 다중행 sub query

Q. IN연산자를 이용하여 부서 별로 가장 급여를 많이 받는 사원의 이름, 급여, 부서 번호를 출력하세요

select ename,sal,deptno from emp
 where sal IN(select max(sal) from emp group by deptno);   

Q. JOB이 'MANAGER'인 사원이 속한 부서의 부서번호와 부서명, 지역을 출력하세요

select * from dept 
where deptno IN(select deptno from emp where job= 'MANAGER');

Q. 급여를 3000이상 받는 사원이 속한 부서의 모든 사원을 출력하는 쿼리작성하시오

select * from emp 
where deptno IN(select deptno from emp where sal >= 3000);     
select ename,deptno, sal from emp
where deptno in(select distinct(deptno) from emp where sal >= 3000);

.ALL

Q. 30번 소속 사원들 중에서 급여를 가장 많이 받는 사원보다 더 많은 급여를 받는 사원의 이름과 급여를 출력하세요 (다중, 단일 다 해보기)

select ename, sal from emp
where sal > all(select sal from emp where deptno = 30);         
select ename,sal from emp
where sal > (select max(sal) from emp where deptno = 30); 

Q. 30번 소속 사원 중에 가장 작은 월급을 받는 사람보다 더 많은 급여를 받는 사람을 출력하는 쿼리(다중,단일 다 해보기)

select* from emp 
where sal > any(select sal from emp where deptno = 30);
              
select* from emp 
where sal > (select Min(sal) from emp where deptno = 30);

Q. 이름에 'T'를 포함하고 있는 사원들과 같은 부서에서
근무하는 사원들의 이름과 사원번호를 출력하시오

   select ename, empno, deptno
   from emp
   where deptno IN (select deptno from emp where ename LIKE '%T%');

   select ename, empno, deptno
   from emp
   where deptno IN(select deptno from emp where INSTR(ename, 'T') <> 0);

Q. 급여가 평균 급여보다 많고 이름에 'S'가 들어가는
사원과 동일한 부서에서 근무하는 모든 사원의 이름, 급여를 출력하시오 **

   select ename, sal, deptno
   from emp
   where deptno IN(select deptno from emp where sal > (select AVG(sal) from emp)
   AND ename LIKE '%S%');

Q. 영업 사원들 보다 급여를 많이 받는 사원들의 이름, 직급, 급여를
출력하시오. 단, 영업사원들은 제외하시오

// 다중행 서브쿼리
   select ename, sal, job
   from emp
   where sal > ALL(select sal from emp where job = 'SALESMAN');

// 단일행 서브쿼리
   select ename, sal, job
   from emp
   where sal > (select MAX(sal) from emp where job = 'SALESMAN');

Q. 10번과 20번 부서에서 근무하는 사원들의 평균 급여보다
많이 받는 사원들의 이름, 급여, 부서번호를 출력하시오

   select ename, sal, deptno
   from emp
   where sal > (select AVG(sal) from emp where deptno = 10 OR deptno = 20);

   select ename, sal, deptno
   from emp
   where sal > (select AVG(sal) from emp where detpno IN(10,20));

Q. 'WARD'와 동일한 직급을 가진 사원의 이름과 직급을 출력하시오
(단일행 서브쿼리 이용)

   select ename, job
   from emp
   where job = (select job from emp where ename = 'WARD');

. 다중 컬럼 서브 쿼리

Q. 직급이 'MANAGER'인 사원들이 받는 급여들의 최소 급여보다 많이 받는
사원들의 이름과 급여, 부서번호를 출력하되 부서번호가 20인 사람은 제외하라.

   select ename, sal, deptno
   from emp
   where sal > (select MIN(sal) from emp where job = 'MANAGER')
   AND deptno <> 20;

   select ename, sal, deptno
   from emp
   where sal > ANY(select sal from emp where job = 'MANAGER')
   AND deptno <> 20;

. ROWDUM : sub Query에서 반환되는 각 행에 1부터 시작하는 순차 값을 할당함

select rownum, ename, sal, from emp;

Q. emp테이블에서 최상위 소득자 3명의 이름과 급여를 표시하시오

 select ROWNUM as RANK , ename, sal
 from (select ename, sal from emp ORDER BY sal desc)
 where ROWNUM <= 3;

 . ROWNUM 함수를 ORDER BY 함수와 함께 사용하면 순번이 뒤죽박죽 됩니다. 
   그 이유는 먼저 순번을 매기고 난 후, ORDER BY 정렬을 하기 때문입니다.
   그렇기 때문에 ORDER BY절로 정렬을 해준 후, ROWNUM을 해주어야 한다.

   select rownum, kbs.ename, kbs.sal from
   (select * from emp order by sal desc) kbs;

Q. 'SALES' 부서의 모든 사원의 이름과 급여를 출력하되
월급이 높은 사람이 먼저 나오고 거기에 순번을 붙이시오.

select rownum as rank, ename, sal
from (select * from emp ORDER BY sal desc)
where deptno = (select deptno from dept where dname = 'SALES');