5주차

====================
[Join]연산 예제 교재 43쪽
====================
조인(Join) 이란
 - 두개 이상의 테이블을 연결하여 데이터를 검색하는 방법 
 - 보통 둘 이상의 행들의 공통된 값 을 사용하여  조인
 - 공통된 속성을 조인 속성이라고 함
 - 대개 조인 속성은 두 테이블의 Primary Key 혹은  Foreign Key 가 사용됨
 - 조인은 두 테이블의 카티션프러덕트(곱집합)으로 시작됨


 SELECT * FROM emp;
 SELECT * FROM dept;

카티션 프러덕트(곱집합)
FROM절에 테이블 이름을 나열함으로써 구해짐

예
 SELECT * FROM emp, dept;
 SELECT * FROM emp, dept, salgrade;

--> 카티션 프러덕트는 FROM절에서 두 테이블을 콤마로 나열함

=====Equi Join(동일조인)=======
두 테이블의 조인속성 값이 같은 것끼리 연결함

EMP와 DEPT를 연결하여 부서번호가 같은 것만 선택한다면?

SELECT * 
FROM emp, dept;
WHERE emp.deptno = dept.deptno;

---> 필요한 속성만 열거해 보자

SELECT emp.empno, emp.ename, emp.deptno, dept.deptno, dept.dname, dept.loc
FROM emp, dept
WHERE emp.deptno = dept.deptno;

주의 !!! 다음처럼 모호한 표현은 오류가 발생함

SELECT empno, ename, deptno, deptno, dname, loc
FROM emp, dept
WHERE emp.deptno = dept.deptno;
---->deptno는 어느테이블에 있는 값인지 명시해야함

**** 다음처럼 alias를 사용하면 편리함 ***

SELECT e.empno, e.ename, e.deptno, d.deptno, d.dname, d.loc
FROM emp e, dept d
WHERE e.deptno = d.deptno;


=====Non-Equi Join(비동일조인)=======
 -조인속성값이 일치하지 않는 조인

급여와 급여가 속한 호봉등급 간의 관계를 알아보자
먼저 급여와 호봉의 관계를 알아보자

SELECT ename, sal
FROM emp;
SELECT grade,  losal, hisal
FROM salgrade;

급여와 급여가 속한 호봉의 구간은 정확하게 =이 성립하지 않음
그러나 급여가 어느 구간에 속하는지 여부를 판단하여 결과를 얻어냄

SELECT e.ename, e.sal, s.grade
FROM emp e, salgrade s
WHERE e.sal between s.losal and s.hisal;


=====Outer Join(외부조인)=======
-상대 테이블과 연결이 없는 튜플까지 포함
-NULL값과 연결시켜줌

SELECT  e.ename, d.deptno, d.dname
FROM emp e, dept d
WHERE e.deptno = d.deptno;

SELECT  e.ename, d.deptno, d.dname
FROM emp e, dept d
WHERE e.deptno(+) = d.deptno;

----> (+)의 의미는 해당필드에 대응 값이 없으면 NULL을 추가하여 연결하라는 의미

--->다음의 결과를 해석해보시오

SELECT  e.ename, d.deptno, d.dname
FROM emp e, dept d
WHERE e.deptno = d.deptno(+);


=====Self Join=======
 - 자신의 테이블에 있는 다른 튜플과 연결함
 - 자신과 카티션프로덕트를 해야 함
 - 동일한 이름을 피하기 위하여 반드시 alias를 사용해야 함
 - 예를 들어 사워과 그 사원의 매니저 정보는 모두 emp테이블에 들어 있음

일단 하나의 테이블을 카티션프러덕트를 해보자
SELECT *
FROM emp, emp;
----> 안된다(두개의 테이블이 같은 이름을 사용하는 것으로 간주됨)

SELECT *
FROM emp s , emp t;
---->된다!!!

매니저 찾기
==========
자신의 매니저번호와 일치하는 사원을 찾으면 된다

SELECT worker.ename 사원이름,   manager.ename 매니저이름
FROM emp worker, emp manager
WHERE worker.mgr = manager.empno;


JOIN 연산자 사용(권하지는 않음)

SELECT * FROM emp JOIN dept USING (deptno);
SELECT * FROM emp  JOIN dept ON (emp.deptno=dept.deptno);
SELECT * FROM emp e  JOIN dept d ON (e.deptno=d.deptno);

SELECT e.ename, e.sal, s.grade
FROM emp e, salgrade s
WHERE e.sal between s.losal and s.hisal;

을 JOIN연산자를 써서 표현하면

SELECT e.ename, e.sal, s.grade
FROM emp e JOIN  salgrade s ON (e.sal between s.losal and s.hisal);

SELECT  e.ename, d.deptno, d.dname
FROM emp e RIGHT OUTER JOIN dept d ON (e.deptno = d.deptno);

다음질의식과 같은 결과

SELECT  e.ename, d.deptno, d.dname
FROM emp e, dept d
WHERE e.deptno(+) = d.deptno;

SELECT  e.ename, d.deptno, d.dname
FROM emp e LEFT OUTER JOIN dept d ON (e.deptno = d.deptno);

=============
순위(Rank) 알아내기
=============

SELECT RANK(1300) WITHIN GROUP(ORDER BY SAL DESC) 
FROM emp;
----> 1300은 급여 중 상위 몇위에 해당될까?

SELECT RANK(1300) WITHIN GROUP(ORDER BY SAL ASC) 
FROM emp;
----> 1300은 급여 중 상위 몇위에 해당될까?

SELECT ename 이름, sal 급여, RANK() OVER(ORDER BY SAL DESC) 순위
FROM emp;
----> 각 사원 급여의 순위 (급여가 큰 사원부터)

SELECT ename 이름, sal 급여, RANK() OVER(ORDER BY SAL DESC) 순위
FROM emp order by empno;
----> 각 사원 급여의 순위 (사원번호 순)




실습 과제 

1. 부서의 평균급여 이상의 급여를 받는 사원을 찾아라;  출력 내용(이름, 급여, 부서번호, 부서의 평균급여)
   In Line View와 조인을 이용하여 해결함
*****************************************************
이름             급여   부서번호 부서평균급여
---------- ---------- ---------- ------------
JONES            2975         20         2195
BLAKE            2850         30         1560
SCOTT            3000         20         2195
KING             5000         10       2916.7
FORD             3000         20         2195

2. 본인의 상사(mgr)과 같은 부서에 근무하는 사원의 이름, 부서번호
   상사이름, 상사의 부서번호를 구하여 사원의 이름 순서로 출력하시오

이름           부서번호 상사이름     상사부서
----------   ----------  ----------      ----------
ADAMS              20 SCOTT             20
CLARK               10 KING               10
FORD                20 JONES              20
JAMES               30 BLAKE              30
MARTIN             30 BLAKE              30
MILLER              10 CLARK              10
SCOTT               20 JONES              20
SMITH               20 FORD               20
TURNER             30 BLAKE              30
WARD               30 BLAKE              30

3. 6호봉을 다음과 신설해보자
INSERT INTO salgrade values(6, 10000,20000);
SELECT * FROM salgrade;
COMMIT;
이 때, 사원의 급여와 호봉을 조인해서 다음과 같이 출력해보자
단, 6호봉에 해당하는 사원은 없다는 결과까지 추가되어야 함
다음의 출력 결과 참고

ENAME             SAL      GRADE
---------- ---------- ----------
SMITH             800          1
JAMES             950          1
ADAMS          1100          1
WARD            1250          2
MARTIN          1250          2
MILLER           1300          2
TURNER          1500         3
ALLEN            1600          3
CLARK            2450          4
BLAKE            2850          4
JONES            2975          4
SCOTT            3000          4
FORD             3000          4
KING             5000          5
                                   6
15 개의 행이 선택되었습니다.


4. 각 부서별 최대급여자의 이름, 부서번호, 급여를 구하시오. 
   (단, In Line View를 사용하고 부서번호 순서로 출력할 것)

ENAME       DEPTNO    SAL
----------      ----------  ----------
KING            10         5000
SCOTT           20        3000
FORD            20        3000
BLAKE           30        2850