[DB] EXISTS

-- 본인의 부서 평균보다 많이 받는 사람

select ename, sal from emp e1 where sal > (select avg(sal) from emp e2 where e1.deptno = e2.deptno);
-- sub query 내부의 별칭은 생략 가능
select ename, sal from emp e where sal > (select avg(sal) from emp where e.deptno = deptno);

Exists

- 데이터의 존재 유무를 확인하는 연산자

- 데이터의 행이 출력되는지의 여부를 확인하는 용도로 사용한다.

 

- SQL문에서 어떤 조건을 만족하는 집합의 존재 여부를 확인할 경우에 사용한다.

- 서브쿼리에 해당하는 row가 적어도 하나 이상 존재할 경우 SELECT문이 실행된다.

- Exists는 주어진 조건을 만족하는 첫 번째 열을 만나면 트랜잭션을 멈춘다.

  즉, 조건을 만족하면 처리를 멈추고 다음의 처리를 수행. 

- 서브 쿼리 조건에 만족하는 모든 레코드를 출력함.

-- 밑에 직원이 있으면 출력
select empno, ename, sal from emp where empno in (select mgr from emp);
select empno, ename, sal from emp e where exists (select *from emp where e.empno = mgr);

 

EXISTS, IN

EXISTS : 해당 row가 존재하는지만 확인함. 값들을 일일히 비교하지는 않는다.

IN : 실제 존재하는 데이터들의 모든 값을 확인한다.

 

실습 예제

--1. EMP 테이블에서 Blake와 같은 부서에 있는 모든 사원의 이름과 입사일자를 출력하는 SELECT문을 작성하시오.
--2. EMP 테이블에서 평균 급여 이상을 받는 모든 종업원에 대해서 종업원 번호와 이름을 출력하는 SELECT문을 작성하시오. 단 급여가 많은 순으로 출력하여라.
--3. EMP 테이블에서 이름에 “T”가 있는 사원이 근무하는 부서에서 근무하는 모든 종업원에 대해 사원 번호,이름,급여를 출력하는 SELECT문을 작성하시오. 
--단 사원번호 순으로 출력하여라.
--4. EMP 테이블에서 부서 위치가 Dallas인 모든 종업원에 대해 이름,업무,급여를 출력하는 SELECT문을 작성하시오.
--5. EMP 테이블에서 King에게 보고하는 모든 사원의 이름과 급여를 출력하는 SELECT문을 작성하시오.
--6. EMP 테이블에서 SALES부서 사원의 이름,업무를 출력하는 SELECT문을 작성하시오.
--7. EMP 테이블에서 월급이 부서 30의 최저 월급보다 높은 사원을 출력하는 SELECT문을 작성하시오.
--8. EMP 테이블에서 부서 10에서 부서 30의 사원과 같은 업무를 맡고 있는 사원의 이름과 업무를 출력하는 SELECT문을 작성하시오.
--9. EMP 테이블에서 FORD와 업무도 월급도 같은 사원의 모든 정보를 출력하는 SELECT문을 작성하시오

--10. EMP 테이블에서 업무가 JONES와 같거나 월급이 FORD이상인 사원의 정보를 이름,업무,부서번호,급여를 출력하는 SELECT문을 작성 (단 업무별, 월급이 많은 순으로 출력하여라.)
--11. EMP 테이블에서 SCOTT 또는 WARD와 월급이 같은 사원의 정보를 이름,업무,급여를 출력하는 SELECT문을 작성하시오.
--12. EMP 테이블에서 CHICAGO에서 근무하는 사원과 같은 업무를 하는 사원의 이름,업무를 출력하는 SELECT문을 작성하시오.
--13. EMP 테이블에서 부서별로 월급이 평균 월급보다 높은 사원을 부서번호,이름,급여를 출력하는 SELECT문을 작성하시오. 
--14. EMP 테이블에서 업무별로 월급이 평균 월급보다 낮은 사원을 부서번호,이름,급여를 출력하는 SELECT문을 작성하시오.
--15. EMP 테이블에서 적어도 한명 이상으로부터 보고를 받을 수 있는 사원을 업무,이름,사원번호,부서번호를 출력하는 SELECT문을 작성하시오.
--16. EMP 테이블에서 말단 사원의 사원번호,이름,업무,부서번호를 출력하는 SELECT문을 작성하시오

 

--1. EMP 테이블에서 Blake와 같은 부서에 있는 모든 사원의 이름과 입사일자를 출력하는 SELECT문을 작성하시오.
select ename, hiredate from emp where deptno = (select deptno from emp where ename = 'BLAKE');
select ename, hiredate from emp where deptno in (select deptno from emp where ename = 'BLAKE');

--2. EMP 테이블에서 평균 급여 이상을 받는 모든 종업원에 대해서 종업원 번호와 이름을 출력하는 SELECT문을 작성하시오. 단 급여가 많은 순으로 출력하여라.
select empno, ename, sal from emp where sal >= (select avg(sal) from emp) order by sal desc;

--3. EMP 테이블에서 이름에 “T”가 있는 사원이 근무하는 부서에서 근무하는 모든 종업원에 대해 사원 번호,이름,급여를 출력하는 SELECT문을 작성하시오. 
--단 사원번호 순으로 출력하여라.
select empno, ename, sal from emp where deptno in (select deptno from emp where ename like '%T%') order by empno;

--4. EMP 테이블에서 부서 위치가 Dallas인 모든 종업원에 대해 이름,업무,급여를 출력하는 SELECT문을 작성하시오.
select ename, job, sal from emp where deptno = (select deptno from dept where loc = 'DALLAS');

--5. EMP 테이블에서 King에게 보고하는 모든 사원의 이름과 급여를 출력하는 SELECT문을 작성하시오.
select ename, sal from emp where mgr = (select empno from emp where ename = 'KING');

--6. EMP 테이블에서 SALES부서 사원의 이름,업무를 출력하는 SELECT문을 작성하시오.
select ename, job from emp where deptno = (select deptno from dept where dname ='SALES');

--7. EMP 테이블에서 월급이 부서 30의 최저 월급보다 높은 사원을 출력하는 SELECT문을 작성하시오.
select * from emp where sal > (select min(sal) from emp where deptno = 30);

--8. EMP 테이블에서 부서 10에서 부서 30의 사원과 같은 업무를 맡고 있는 사원의 이름과 업무를 출력하는 SELECT문을 작성하시오.
select ename, job from emp where deptno = 10 and job in (select job from emp where deptno = 30);

--9. EMP 테이블에서 FORD와 업무도 월급도 같은 사원의 모든 정보를 출력하는 SELECT문을 작성하시오
select * from emp where (job, sal) = (select job, sal from emp where ename = 'FORD') and ename != 'FORD';


--10. EMP 테이블에서 업무가 JONES와 같거나 월급이 FORD이상인 사원의 정보를 이름,업무,부서번호,급여를 출력하는 SELECT문을 작성
--단 업무별, 월급이 많은 순으로 출력하여라.
select ename, job, deptno, sal from emp where job = (select job from emp where ename = 'JONES') or
sal >= (select sal from emp where ename = 'FORD') and ename not in ('JONES','FORD') order by job, sal desc;

--11. EMP 테이블에서 SCOTT 또는 WARD와 월급이 같은 사원의 정보를 이름,업무,급여를 출력하는 SELECT문을 작성하시오.
select ename, job, sal from emp where sal in (select sal from emp where ename in ('SCOTT','WARD'))
and ename not in ('SCOTT','WARD');

--12. EMP 테이블에서 CHICAGO에서 근무하는 사원과 같은 업무를 하는 사원의 이름,업무를 출력하는 SELECT문을 작성하시오.
select ename, job from emp where job in (select job from emp where deptno = (select deptno from dept where loc = 'CHICAGO'));

--13. EMP 테이블에서 부서별로 월급이 평균 월급보다 높은 사원을 부서번호,이름,급여를 출력하는 SELECT문을 작성하시오. 
select deptno, ename, sal from emp e where sal > (select avg(sal) from emp where e.deptno = deptno);

--14. EMP 테이블에서 업무별로 월급이 평균 월급보다 낮은 사원을 부서번호,이름,급여를 출력하는 SELECT문을 작성하시오.
select deptno, ename, sal from emp e where sal < (select avg(sal) from emp where e.job = job);

--15. EMP 테이블에서 적어도 한명 이상으로부터 보고를 받을 수 있는 사원을 업무,이름,사원번호,부서번호를 출력하는 SELECT문을 작성하시오.
-- 적어도 한명 이상으로부터 보고 받을 수 있는 경우 : 관리자이어야 함. (밑에 직원이 있어야한다.)
select job, ename, empno, deptno from emp where empno in (select mgr from emp);
select job, ename, empno, deptno from emp e where exists (select * from emp where e.empno = mgr);

--16. EMP 테이블에서 말단 사원의 사원번호,이름,업무,부서번호를 출력하는 SELECT문을 작성하시오
-- 말단 사원 : (밑에 직원이 없음)
select empno, ename, job, deptno from emp where empno not in (select mgr from emp where mgr is not null);
select empno, ename, job, deptno from emp e where not exists (select * from emp where e.empno = mgr);

 

/

5/6