4주차-쌍비교와 상관관계질의

=========================
    다중열 서브쿼리 (65쪽)
=========================

****다중 열 서브쿼리란 
서브쿼리의 결과값이 두개 이상의 컬럼을 반환하는 서브쿼리

부서 번호가 20이며 JOB이 CLERK        인 사원과
부서 번호가 30이며 JOB이 SALESMAN 인 사원을 구하시오

다음과 같이 착각하기 쉽다!!
===========================
NONPAIRWISE COMPARISON(비쌍비교)
===========================

SELECT ename, deptno, job
FROM EMP
WHERE deptno IN (20,30)
      AND
      job IN ('CLERK', 'SALESMAN');

ENAME   DEPTNO JOB                                                                                     
-------   ------     ------------------                                                                      
SMITH     20      CLERK                                                                                   
ALLEN     30      SALESMAN                                                                                
WARD     30      SALESMAN                                                                                
MARTIN   30      SALESMAN                                                                                
TURNER   30      SALESMAN                                                                                
ADAMS   20       CLERK                                                                                   
JAMES    30       CLERK   

----> 결과 값 해석
(JAMES, 30, CLERK) 은 원래 질의식에 웨배됨 
부서 번호가 (20 혹은 30) 이며 JOB이 (CLERK  혹은 SALESMAN) 인 사원이 출력 됨 
  

====================
PAIRWISE COMPARISON (쌍비교)
====================
두개의 비교쌍을 묶어주어야 함!

SELECT ename, deptno, job
FROM EMP
WHERE (deptno, job) IN ((20, 'CLERK'), (30, 'SALESMAN'));

ENAME   DEPTNO JOB                                                                                     
------- ------ ------------------                                                                      
SMITH   20      CLERK                                                                                   
ALLEN   30      SALESMAN                                                                                
WARD    30      SALESMAN                                                                                
MARTIN  30      SALESMAN                                                                                
TURNER  30      SALESMAN                                                                                
ADAMS   20      CLERK  
------------------------------                                                                                 
JAMES   30      CLERK   -------> 제외됨                                                                              
                                                                   

---

TAEHO>SELECT ename, deptno, job
  2  FROM EMP
  3  WHERE (deptno,job) IN ((20,'CLERK'),(30,'SALESMAN'));

ENAME          DEPTNO JOB
---------- ---------- ---------
SMITH              20 CLERK
ALLEN              30 SALESMAN
WARD               30 SALESMAN
MARTIN             30 SALESMAN
TURNER             30 SALESMAN
ADAMS              20 CLERK

6 개의 행이 선택되었습니다.

TAEHO>

---

주의!! 다음의 질의식은 오류!

SELECT ename, deptno, job
FROM EMP
WHERE (deptno, job) = (20, 'CLERK')
     OR (deptno, job) =  (30, 'SALESMAN');





==========================
FROM 절에 서브쿼리 사용하기 (66쪽)
==========================

뷰는 저장장치에 내에 물리적으로 존재하진 않지만 사용자에게 있는 것처럼 보여지는 가상의 테이블
뷰에 대한 학습은 추후에 자세히 함.
오늘은 아주 간단한 예제만 보도록 하자.
**** 20번 부서에 속하는 직원 들의 평균급여 구하기****

우선!!! 뷰를 만들려면 뷰 생성 권한이 있어야 함.
=========================
system 계정에서 사용자에게 권한 부여
grant create view to lhk7083;

conn lhk7083

CREATE VIEW EMP_SAL_20 AS 
SELECT SAL 
FROM EMP 
WHERE DEPTNO=20;

SELECT *
FROM 
EMP_SAL_20;

SELECT AVG(SAL)
FROM 
EMP_SAL_20;

그런데 이 하나의  질의처리를 위해 뷰를 낭비할 필요가 있을까?
질의처리용으로 임시 뷰를 만들고 처리 후에는 없어져도 좋다
이럴 때 In Line View를 사용하면 좋다

In Line View란?
FROM 절에 서브쿼리 사용하여 생성한 가상의 테이블, 즉, 임시로 생성한 뷰

----> 20번 부서에 속하는 직원 들의 급여로 구성된 가상의 테이블을 만들고
이로부터 평균을 구함


SELECT AVG(SAL)
FROM (SELECT SAL FROM EMP WHERE DEPTNO=20);

---

TAEHO>SELECT AVG(SAL)
  2  FROM (SELECT SAL FROM EMP WHERE DEPTNO=20);

  AVG(SAL)
----------
      2175

---

그러나!!!!
다음과 같이 하는 것이 오히려 바람직!

SELECT AVG(SAL)
FROM  EMP 
WHERE DEPTNO=20;


==========================
상관관계 서브쿼리 사용하기 (66쪽)
==========================
상관관계 서브쿼리 (Correlated Subquery) 란 
바깥쪽 쿼리의 컬럼 중의 하나가 안쪽 서브쿼리의 조건에 이용되는 처리 방식
서브 쿼리에서 주 쿼리의 행을 참조하고 이 값을 다시 주 쿼리로 반환
튜플변수(테이블 이름의 별명으로 표기함)를 사용

Alias(Rename) 의 사용 42쪽
---------------------------------
select e.ename 이름, e.sal 급여
from emp e;
--------------------------------

상관관계 서브쿼리 예제 1) 

부하직원이 있는 사원의 정보

SELECT empno, ename, sal
FROM emp e
WHERE EXISTS (SELECT empno
              FROM emp
              WHERE mgr= e.empno);

---

TAEHO>SELECT empno , ename, sal
  2  FROM emp e
  3  WHERE EXISTS(SELECT empno
  4  FROM emp
  5  WHERE mgr=e.empno);

     EMPNO ENAME             SAL
---------- ---------- ----------
      7566 JONES            2975
      7698 BLAKE            2850
      7782 CLARK            2450
      7788 SCOTT            3000
      7839 KING             5000
      7902 FORD             3000

6 개의 행이 선택되었습니다.

---

상관관계 서브쿼리 예제 2) 

자기의 부하직원들의 평균급여보다 급여를 많이 받는 사원의 정보를 출력

SELECT empno, ename, sal
FROM emp e
WHERE sal > (SELECT AVG(sal) 
      FROM emp 
                 WHERE e.empno = mgr);

---

TAEHO>SELECT empno,ename,sal
  2  FROM emp e
  3  WHERE sal > (SELECT AVG(sal)
  4  FROM emp
  5  WHERE e.empno=mgr);

     EMPNO ENAME             SAL
---------- ---------- ----------
      7698 BLAKE            2850
      7782 CLARK            2450
      7788 SCOTT            3000
      7839 KING             5000
      7902 FORD             3000

---

=============================
집합연산자의 사용 67쪽
=============================
SELECT distinct deptno FROM emp;
SELECT deptno FROM dept;

---

TAEHO>SELECT distinct deptno FROM emp;

    DEPTNO
----------
        30
        20
        10

TAEHO>SELECT deptno FROM dept;

    DEPTNO
----------
        10
        20
        30
        40

TAEHO>

---

합집합
---------

SELECT deptno FROM emp
    UNION
SELECT deptno FROM dept;

SELECT deptno FROM emp
    UNION ALL
SELECT deptno FROM dept;

교집합
-----------
SELECT deptno FROM emp
    INTERSECT
SELECT deptno FROM dept;

차집합
---------
SELECT deptno FROM dept
    MINUS
SELECT deptno FROM emp;



*****************************************************
실습 과제 :

1. 부서별로 가장 급여를 많이 받는 사원의 정보를 출력하는 예제

방법 1 (쌍비교)

SELECT empno,ename,sal,deptno  
FROM emp
WHERE sal IN (SELECT  MAX(sal)
              FROM emp
              GROUP BY deptno);     -----> 안된다. Why? 

update emp set deptno=10 where  empno=7902;    수행하고 해보라
비쌍비교의 문제점을 숙지하고 쌍비교 질의식으로 작성

---

TAEHO>select empno, ename, sal, deptno from emp
  2  where (deptno, sal) in (select deptno, max(sal) from emp group by deptno);

     EMPNO ENAME             SAL     DEPTNO
---------- ---------- ---------- ----------
      7698 BLAKE            2850         30
      7788 SCOTT            3000         20
      7839 KING             5000         10

---

방법 2
상관관계 서브쿼리를 이용하여 작성



2. 쌍비교와 비쌍비교의 차이점 알아보기

비쌍비교 질의식 
--------------------------------------------------------------
SELECT ename, deptno, job
FROM EMP
WHERE deptno IN (20,30)
      AND
      job IN ('CLERK', 'SALESMAN')
--------------------------------------------------------------

쌍비교 질의식 
--------------------------------------------------------------
SELECT ename, deptno, job
FROM EMP
WHERE (deptno, job) IN ((20, 'CLERK'), (30, 'SALESMAN');
--------------------------------------------------------------

위 두 질의식이 차이를 보여주는 사원을 찾는 질의식을 작성하시오?

---

비쌍비교

TAEHO>SELECT ename, deptno, job
  2  FROM emp
  3  WHERE deptno in (20,30)
  4  and
  5  job in ('CLERK','SALESMAN');

ENAME          DEPTNO JOB
---------- ---------- ---------
SMITH              20 CLERK
ALLEN              30 SALESMAN
WARD               30 SALESMAN
MARTIN             30 SALESMAN
TURNER             30 SALESMAN
ADAMS              20 CLERK
JAMES              30 CLERK

7 개의 행이 선택되었습니다.

 

---

쌍비교
TAEHO>SELECT ename, deptno , job
  2  FROM EMP
  3  where ( deptno, job ) in ((20,'CLERK'),(30,'SALESMAN'));

ENAME          DEPTNO JOB
---------- ---------- ---------
SMITH              20 CLERK
ALLEN              30 SALESMAN
WARD               30 SALESMAN
MARTIN             30 SALESMAN
TURNER             30 SALESMAN
ADAMS              20 CLERK

6 개의 행이 선택되었습니다.

==================================================

TAEHO>SELECT ename, deptno, job
  2  FROM emp
  3  WHERE deptno IN (20,30)
  4  MINUS
  5  job IN (;
job IN (
*
5행에 오류:
ORA-00928: 누락된 SELECT 키워드


TAEHO>SELECT ename, deptno, job
  2  FROM emp
  3  WHERE deptno IN (20,30)
  4  and
  5  job in ('CLERK','SALESMAN')
  6  minus
  7  SELECT ename, deptno, job
  8  FROM emp
  9  WHERE ( DEPTNO, JOB) IN ((20, 'CLERK'),(30,'SALESMAN'));

ENAME          DEPTNO JOB
---------- ---------- ---------
JAMES              30 CLERK

---

3. 자기 상사(mgr)과 같은 부서에 근무하는 직원의 이름과  부서번호를 구하여 
    다음과 같이 사원의 이름 순서로 출력하시오
    (상사의 이름까지 출력하는 것은 다음주 조인 연산 시간에!!

이름         부서번호
---------- ----------
ADAMS      20
ALLEN        30
CLARK        10
FORD         20
JAMES        30
MARTIN      30
MILLER       10
SCOTT        20
SMITH        20
TURNER      30
WARD        30

---

TAEHO>SELECT ename, deptno
  2  from emp e
  3  where exists(select empno from emp
  4  where empno=e.mgr
  5  and
  6  deptno=e.deptno)
  7  order by ename;

ENAME          DEPTNO
---------- ----------
ADAMS              20
ALLEN              30
CLARK              10
JAMES              30
LEE_HK             10
MARTIN             30
MILLER             10
SCOTT              20
TURNER             30
WARD               30

10 개의 행이 선택되었습니다.