220902 - 오라클 - 셀프 조인, 서브 쿼리, 다중행 서브쿼리

※ where 절에서는 그룹함수 불가 / having절 사용해야 함

 

 

셀프 조인 ex

○ ward 보다 늦게 입사한 사원 이름, 입사일

select e.ename, e.hiredate from employee e, employee f where f.ename='WARD' and e.hiredate>f.hiredate;

ENAME                HIREDATE
-------------------- --------
JONES                81/04/02
MARTIN               81/09/28
BLAKE                81/05/01
CLARK                81/06/09
SCOTT                87/07/13
KING                 81/11/17
TURNER               81/09/08
ADAMS                87/07/13
JAMES                81/12/03
FORD                 81/12/03
MILLER               82/01/23



○ scott과 동일부서에 근무하는 사원 출력

select e.ename from employee e, employee f where f.ename='SCOTT' and f.dno=e.dno and e.ename<>'SCOTT';

ENAME
--------------------
SMITH
JONES
SCOTT
ADAMS
FORD

 

○ 관리자보다 먼저 입사한 모든 사원의 이름 입사일, 관리자이름 입사일

select e.ename, e.hiredate, f.ename, f.hiredate from employee e, employee f where e.manager=f.eno and f.hiredate>e.hiredate;

ENAME                HIREDATE ENAME                HIREDATE
-------------------- -------- -------------------- --------
ALLEN                81/02/20 BLAKE                81/05/01
WARD                 81/02/22 BLAKE                81/05/01
JONES                81/04/02 KING                 81/11/17
BLAKE                81/05/01 KING                 81/11/17
CLARK                81/06/09 KING                 81/11/17
SMITH                80/12/17 FORD                 81/12/03

 

 

 

 

서브 쿼리

 : 다른 쿼리문에 삽입된 select문

 

 

○ SCOTT보다 급여 많이 받는 사원

select ename, salary from employee where salary > (select salary from employee where ename='SCOTT');

ENAME                    SALARY
-------------------- ----------
KING                       5000



○ 평균 급여 이상을 받는 사원수 조회

select count(*) from employee where salary > (select avg(salary) from employee);

  COUNT(*)
----------
         6



※ 테이블 만드는 방법
1. create table 테이블이름(컬럼명 타입, 컬럼명 타입,..)
2. 만들어진 테이블 구조 복사 - create table 테이블이름 as select * from employee where 1=0;
//데이터 가져오지 않고 구조만 가져옴
3. create table 테이블이름 as select * from employee;

 

※ 케이블에 자료 복사 : insert into emp_del select * from employee; 



○ 평균 급여보다 많이 받는 사원 모두 삭제

delete from emp_del where salary > (select avg(salary) from emp_del);

6 행이 삭제되었습니다.



○ 최소 급여 받는 사원의 이름, 담당업무, 급여 출력

select ename, job, salary from employee where salary = (select min(salary) from employee);

ENAME                JOB                    SALARY
-------------------- ------------------ ----------
SMITH                CLERK                     800

 

○ 30번 부서에서 최소 급여 구한 후, 부서별 최소 급여가 해당값보다 큰 부서 출력 (부서번호, 최소급여)

select dno, min(salary) from employee group by dno having min(salary) > (select min(salary) from employee where dno=30);

       DNO MIN(SALARY)
---------- -----------
        10        1300

 

 

 

다중행 서브쿼리

IN
ANY
SOME
ALL
EXISTS

 

 

○ IN : 서브쿼리의 결과들 중 하나라도 일치 시 리턴

 

ex. 부서별 최소급여 받는 사원번호, 이름

select eno, ename from employee where salary = (select min(salary) from employee group by dno);
//오류 // = 는 값 하나만 받기 때문에 여러 그룹 못 받음



select eno, ename from employee where salary in (select min(salary) from employee group by dno);

       ENO ENAME
---------- --------------------
      7369 SMITH
      7900 JAMES
      7934 MILLER

 

○ ANY : 서브쿼리가 반환하는 각각의 값 비교 => OR 과 유사

< ANY : 최대값보다 작음
> ANY : 최소값보다 큼
= ANY : IN과 동일


ex. 직급이 SALESMAN 아니면서 급여가 SALESMAN의 최댓값보다 낮은 사원


select eno, ename,salary from employee where job<>'SALESMAN' and salary < any(select salary from employee where job='SALESMAN');

       ENO ENAME                    SALARY
---------- -------------------- ----------
      7369 SMITH                       800
      7900 JAMES                       950
      7876 ADAMS                      1100
      7934 MILLER                     1300



○ ALL : 조건 모두 만족하면 값 리턴


EX. 직급이 SALESMAN 아니면서 급여가 SALESMAN 최소값보다 낮은 사원 출력


select eno, ename, job, salary from employee where job<>'SALESMAN' and salary < all(select salary from employee where job='SALESMAN');

       ENO ENAME                JOB                    SALARY
---------- -------------------- ------------------ ----------
      7876 ADAMS                CLERK                    1100
      7900 JAMES                CLERK                     950
      7369 SMITH                CLERK                     800



○ EXISTS : 서브쿼리의 결과 중 만족하는 값이 하나라도 존재하면 처리

=> 행의 존재 유무 확인, 그 외 작업 수행하지 않음 (고성능)

 

NOT EXISTS : NULL 있으면 TRUE 리턴

 

cf.

IN : 데이터들의 모든 값 확인

NOT IN : NULL 있으면 FALSE (NVL 권장)


EX. 

select d.dno, d.dname from department d where exists (select e.dno from employee e where d.dno=e.dno); 

//10,20,30 만 출력

       DNO DNAME
---------- ----------------------------
        20 RESEARCH
        30 SALES
        10 ACCOUNTING

 

 

 

문제 풀이

○ 사원번호 7788인 사원과 담당업무가 같은 사원 표시 (이름, 업무)

select ename, job from employee where job = (select job from employee where eno=7788);



○ 사원번호 7499인 사원보다 급여 많은 사원 (이름, 업무)

select ename, job from employee where salary > (select salary from employee where eno=7499);

ENAME                JOB
-------------------- ------------------
JONES                MANAGER
BLAKE                MANAGER
CLARK                MANAGER
SCOTT                ANALYST
KING                 PRESIDENT
FORD                 ANALYST



○ 최소 급여를 받는 사원의 이름, 업무, 급여 표시

select ename, job, salary from employee where salary = (select min(salary) from employee);

ENAME                JOB                    SALARY
-------------------- ------------------ ----------
SMITH                CLERK                     800



○ 업무별 평균급여가 가장 작은 담당 업무 찾기 (담당업무, 평균급여)

select job, avg(salary) from employee group by job having avg(salary) = (select round(min(avg(salary)),1) from employee group by job);

JOB                AVG(SALARY)
------------------ -----------
CLERK                   1037.5



○각 부서의 최소 급여를 받는 사원 이름, 급여, 부서번호


select ename, salary, dno from employee where salary in (select min(salary) from employee group by dno);

ENAME                    SALARY        DNO
-------------------- ---------- ----------
SMITH                       800         20
JAMES                       950         30
MILLER                     1300         10


○ job이 analyst 인 사원보다 급여 적으면서 analyst 아닌 사원들의 사원번호, 이름, 담당업무, 급여 표시


select eno, ename, job, salary from employee where job<>'ANALYST' and salary <all (select salary from employee where job='ANALYST');

       ENO ENAME                JOB                    SALARY
---------- -------------------- ------------------ ----------
      7566 JONES                MANAGER                  2975
      7698 BLAKE                MANAGER                  2850
      7782 CLARK                MANAGER                  2450
      7499 ALLEN                SALESMAN                 1600
      7844 TURNER               SALESMAN                 1500
      7934 MILLER               CLERK                    1300
      7654 MARTIN               SALESMAN                 1250
      7521 WARD                 SALESMAN                 1250
      7876 ADAMS                CLERK                    1100
      7900 JAMES                CLERK                     950
      7369 SMITH                CLERK                     800



○ MANAGER 없는 사원 이름


select ename from employee where manager is null;
//select ename from employee where eno in (select eno from employee where manger is null);

ENAME
--------------------
KING



○ BLAKE와 동일 부서에 속한 사원 이름, 입사일 표시 (본인제외)

select ename, hiredate from employee where ename<>'BLAKE' and dno in (select dno from employee where ename='BLAKE');

ENAME                HIREDATE
-------------------- --------
ALLEN                81/02/20
WARD                 81/02/22
MARTIN               81/09/28
TURNER               81/09/08
JAMES                81/12/03


○이름에 K 포함된 사원과 같은 부서에서 일하는 사원 번호, 이름


select eno, ename from employee where dno in (select dno from employee where ename like '%K%') and ename not like '%K%';

       ENO ENAME
---------- --------------------
      7499 ALLEN
      7521 WARD
      7654 MARTIN
      7844 TURNER
      7900 JAMES
      7934 MILLER



○부서위치 dallas인 사원 이름, 부서번호, 담당업무

select ename, dno, job from employee where dno=(select dno from department where loc='DALLAS');

ENAME                       DNO JOB
-------------------- ---------- ------------------
SMITH                        20 CLERK
JONES                        20 MANAGER
SCOTT                        20 ANALYST
ADAMS                        20 CLERK
FORD                         20 ANALYST



○KING에게 보고하는 사원 이름, 급여

select ename, salary from employee where manager=(select eno from employee where ename='KING');

ENAME                    SALARY
-------------------- ----------
JONES                      2975
BLAKE                      2850
CLARK                      2450