7.19 JOIN

-- 타입변환, MULTI-ROW FUNCTION
-- 그룹화와 관련된 함수

-- ROLLUP 함수
-- SELECT
-- FROM
-- WHERE
-- GROUP BY ROLLUP (그룹화할 열 지정)

SELECT DEPTNO, JOB, COUNT(*), MAX(SAL), SUM(SAL), AVG(SAL)
FROM EMP
GROUP BY DEPTNO, JOB
ORDER BY DEPTNO, JOB;

SELECT DEPTNO, JOB, COUNT(*), MAX(SAL), SUM(SAL), AVG(SAL)
FROM EMP
GROUP BY ROLLUP(DEPTNO, JOB)
ORDER BY DEPTNO, JOB;

-- CUBE 함수
-- SELECT
-- FROM
-- WHERE
-- GROUP BY CUBE (그룹화할 열 지정)

SELECT DEPTNO, JOB, COUNT(*), MAX(SAL), SUM(SAL), AVG(SAL)
FROM EMP
GROUP BY CUBE(DEPTNO, JOB)
ORDER BY DEPTNO, JOB;
-- n개의 열을 지정하면 2^n 개 조합이 출력됩니다.

-- GROUPING SETS 함수
-- 여러 그룹화 대상 열의 값을 각각 같은 수준으로 출력합니다.
SELECT DEPTNO, JOB, COUNT(*)
FROM EMP
GROUP BY GROUPING SETS(DEPTNO, JOB);

-- GROUPING 함수
-- 그룹화 할 열을 선택하면 어느 대상 열이 그룹화되어 집계되었는지 반환(0, 1)
SELECT DEPTNO, JOB, COUNT(*), MAX(SAL), SUM(SAL), AVG(SAL),
        GROUPING(DEPTNO),
        GROUPING(JOB)
FROM EMP
GROUP BY CUBE(DEPTNO, JOB)
ORDER BY DEPTNO, JOB;

-- DECODE를 넣어 GROUPING 함수를 사용한 경우

SELECT DECODE(GROUPING(DEPTNO), 1, 'ALL_DEPT', DEPTNO) AS DEPTNO, 
       DECODE(GROUPING(JOB), 1, 'ALL_JOB', JOB) AS JOB, 
        COUNT(*), MAX(SAL), SUM(SAL), AVG(SAL)
FROM EMP
GROUP BY CUBE(DEPTNO, JOB)
ORDER BY DEPTNO, JOB;

-- GROUPING_ID 함수 (여러 컬럼 지정가능, BIT 연산)
SELECT DEPTNO, JOB, COUNT(*), MAX(SAL), SUM(SAL), AVG(SAL),
        GROUPING(DEPTNO),
        GROUPING(JOB),
        GROUPING_ID(DEPTNO, JOB)
FROM EMP
GROUP BY CUBE(DEPTNO, JOB)
ORDER BY DEPTNO, JOB;
--이진수   10진수
--
-- 0 0 =>   0   아무것도 아님
-- 0 1 =>   1   두번째 조건 만족
-- 1 0 =>   2   첫번째 조건 만족
-- 1 1 =>   3   조건 2개 만족

SELECT * FROM EMP;
DESC EMP;
-- Ex1. 사원들의 입사연도를 기준으로 부서별로 몇 명이 입사했는지 출력하세요.
-- HIREDATE -> HIRE_YEAR(타입 관련 함수 활용)
    SELECT TO_CHAR(HIREDATE,'YYYY') AS HIRE_YEAR, DEPTNO, COUNT(*) AS CNT 
    FROM EMP
    GROUP BY DEPTNO,HIRE_YEAR;

-- Ex2. 추가수당(COMM) 받는 사원과 받지 않는 사원의 수를 출력하세요.
    SELECT  NVL2(COMM, 'O', 'X') AS EXIST_ISCOMM, COUNT(*) AS CNT 
    FROM EMP 
    GROUP BY EXIST_ISCOMM;

-- Ex3. 입사연도, 연도별 사원수, 급여 합계, 평균 급여가 출력되도록 ROLLUP
--      부서와 입사연도로 그룹을 지어주고, 부서별로 소계와 총계를 출력하세요.
    SELECT DEPTNO, TO_CHAR(HIREDATE,'YYYY') AS HIRE_YEAR,
    COUNT(*) AS CNT, 
    MAX(SAL) AS MAX_SEL, SUM(SAL) AS SUM_SAL, AVG(SAL) AS AVG_SAL
    FROM EMP
    GROUP BY ROLLUP(DEPTNO,HIRE_YEAR)
    ORDER BY DEPTNO;

-- JOIN
-- 여러 테이블을 하나의 테이블처럼 사용
--SELECT 컬럼1....
--FROM 테이블1
--WHERE 조건식
--GROUP BY 그룹식
--HAVING 그룹조건식
--ORDER BY 정렬식

DESC EMP;
DESC DEPT;
SELECT * FROM DEPT;

SELECT * FROM EMP, DEPT; -- 56 ROW = 14 * 4
-- 데카르트 곱 : 모든 원소의 순서쌍을 곱한 것
-- CROSS JOIN 
-- 조인 조건이 없을 때 

SELECT *
FROM EMP, DEPT
WHERE EMP.DEPTNO = DEPT.DEPTNO
ORDER BY EMPNO;

-- 테이블 별칭 주기

SELECT *
FROM EMP E, DEPT D
WHERE E.DEPTNO = D.DEPTNO
ORDER BY EMPNO;

-- 등가 조인 EQUAL JOIN, 내부 조인 INNER JOIN, 단순 조인 SIMPLE JOIN
SELECT EMPNO, ENAME, DEPTNO, DNAME, LOC
FROM EMP E, DEPT D
WHERE E.DEPTNO = D.DEPTNO;
-- 양 쪽 모두에 있는 컬럼명을 테이블명 없이 지정할 경우 : 열의 정의가 애매합니다.
SELECT EMPNO, ENAME, D.DEPTNO, DNAME, LOC
FROM EMP E, DEPT D
WHERE E.DEPTNO = D.DEPTNO;
-- 컬럼명이 겹치는 경우, 테이블명을 명시적으로 지정해줘야 함.
-- 실무에서는 번거롭더라도, 테이블명 모두 기재합니다.
SELECT E.EMPNO, E.ENAME, D.DEPTNO, D.DNAME, D.LOC
FROM EMP E, DEPT D
WHERE E.DEPTNO = D.DEPTNO
    AND SAL >= 3000; -- 조건식을 사용하고 싶은 경우 AND
    
Q. INNER JOIN 했을 때 급여가 2500 이하, 사원번호가 9999이하인 사원의 정보를 출력해보세요.
SELECT E.EMPNO, E.ENAME, E.SAL, D.DEPTNO, D.DNAME, D.LOC
FROM EMP E, DEPT D
WHERE E.DEPTNO = D.DEPTNO
    AND E.SAL <=2500 
    AND E.EMPNO <= 9999;
    
-- NON-EQUAL JOIN
-- 조건이 같은 조건이 아닌 경우
SELECT * FROM SALGRADE;

SELECT * 
FROM EMP E, SALGRADE S
WHERE E.SAL BETWEEN S.LOSAL AND S.HISAL;
-- 급여 범위를 지정하는 조건식으로 NON-EQUAL JOIN

-- 자체 조인 SELF JOIN
SELECT E1.EMPNO, E1.ENAME, E1.MGR, E2.EMPNO AS MGR_EMPNO, E2.ENAME AS MGR_ENAME
FROM EMP E1, EMP E2
WHERE E1.MGR = E2.EMPNO;

-- 외부 조인 OUTER JOIN
-- 왼쪽 외부 조인 LEFT OUTER JOIN
SELECT E1.EMPNO, E1.ENAME, E1.MGR, E2.EMPNO AS MGR_EMPNO, E2.ENAME AS MGR_ENAME
FROM EMP E1, EMP E2
WHERE E1.MGR = E2.EMPNO(+)
ORDER BY E1.EMPNO;
-- 오른쪽 외부 조인 RIGHT OUTER JOIN
SELECT E1.EMPNO, E1.ENAME, E1.MGR, E2.EMPNO AS MGR_EMPNO, E2.ENAME AS MGR_ENAME
FROM EMP E1, EMP E2
WHERE E1.MGR(+) = E2.EMPNO
ORDER BY E1.EMPNO;

-- ISO 표준 / ANSI 표준 문법
-- NATURAL JOIN
SELECT *
FROM EMP E NATURAL JOIN DEPT D;
-- JOIN ~ USING
SELECT *
FROM EMP E JOIN DEPT D USING(DEPTNO);
-- JOIN ~ ON
SELECT *
FROM EMP E JOIN DEPT D ON (E.DEPTNO = D.DEPTNO);
-- OUTER JOIN(LEFT)
SELECT E1.EMPNO, E1.ENAME, E1.MGR, E2.EMPNO AS MGR_EMPNO, E2.ENAME AS MGR_ENAME
FROM EMP E1 LEFT OUTER JOIN EMP E2 ON(E1.MGR = E2.EMPNO)
ORDER BY E1.EMPNO;
-- OUTER JOIN(RIGHT)
SELECT E1.EMPNO, E1.ENAME, E1.MGR, E2.EMPNO AS MGR_EMPNO, E2.ENAME AS MGR_ENAME
FROM EMP E1 RIGHT OUTER JOIN EMP E2 ON(E1.MGR = E2.EMPNO)
ORDER BY E1.EMPNO;
-- OUTER JOIN(FULL)
SELECT E1.EMPNO, E1.ENAME, E1.MGR, E2.EMPNO AS MGR_EMPNO, E2.ENAME AS MGR_ENAME
FROM EMP E1 FULL OUTER JOIN EMP E2 ON(E1.MGR = E2.EMPNO)
ORDER BY E1.EMPNO;
-- 2가지 문법(ORACLE, 표준 ANSI문법)

-- EX1. 급여가 2000 초과인 부서 정보, 사원 정보를 출력하세요.
-- DEPT 테이블과 JOIN 해서, 부서 이름을 같이 출력
DESC DEPT;
SELECT E.DEPTNO, D.DNAME, E.EMPNO, E.ENAME, E.SAL
FROM EMP E, DEPT D 
WHERE E.DEPTNO = D.DEPTNO
    AND E.SAL>2000;
SELECT E.DEPTNO, D.DNAME, E.EMPNO, E.ENAME, E.SAL
FROM EMP E JOIN DEPT D ON(D.DEPTNO = E.DEPTNO AND E.SAL>2000);
-- EX2.부서별 평균 급여, 최대 급여, 최소급여, 사원수
-- DEPT 테이블의 부서이름과 같이
SELECT D.DEPTNO, D.DNAME, ROUND(AVG(E.SAL),0) AS AVG_SAL, 
    MAX(E.SAL) AS MAX_SAL, MIN(E.SAL) AS MIN_SAL, COUNT(E.SAL) AS CNT
FROM EMP E, DEPT D
WHERE E.DEPTNO = D.DEPTNO
GROUP BY D.DEPTNO,D.DNAME;
SELECT DEPTNO, D.DNAME, ROUND(AVG(E.SAL),0) AS AVG_SAL, 
    MAX(E.SAL) AS MAX_SAL, MIN(E.SAL) AS MIN_SAL, COUNT(E.SAL) AS CNT
FROM EMP E JOIN DEPT D USING(DEPTNO)
GROUP BY DEPTNO,D.DNAME;
-- EX3. 모든 부서 정보와 사원 정보를
-- 부서번호, 사원이름 순으로 정렬하여 출력해보세요.(OUTER JOIN)
SELECT D.DEPTNO, D.DNAME, E.EMPNO, E.ENAME, E.JOB, E.SAL
FROM EMP E, DEPT D
WHERE E.DEPTNO(+) = D.DEPTNO
ORDER BY D.DEPTNO, E.ENAME;

SELECT D.DEPTNO, D.DNAME, E.EMPNO, E.ENAME, E.JOB, E.SAL
FROM EMP E RIGHT OUTER JOIN DEPT D ON(E.DEPTNO = D.DEPTNO)
ORDER BY D.DEPTNO, E.ENAME;
-- 모든 부서 정보(DEPTNO) 사원 정보(EMP) 급여등급정보(SALGRADE)
-- 직속상관정보(MGR)
-- 부서번호, 사원번호 순으로 정렬해서 결과와 같이 출력해보세요.
-- 비등가 조인에서 LEFT OUTER JOIN:컬럼명 BETWEEN 컬럼명(+) AND 컬럼명(+)
SELECT D.DEPTNO, D.DNAME, 
E.EMPNO, E.ENAME, E.MGR, E.SAL, E.DEPTNO, 
S.LOSAL, S.HISAL, S.GRADE, E1.EMPNO AS MGR_EMPNO, E1.ENAME AS MGR_ENAME
FROM EMP E, DEPT D, SALGRADE S, EMP E1
WHERE E.MGR = E1.EMPNO(+) 
AND E.DEPTNO(+) = D.DEPTNO 
AND E.SAL BETWEEN S.LOSAL(+) AND S.HISAL(+)
ORDER BY D.DEPTNO, E.EMPNO;

-- 표준문법
SELECT D.DEPTNO, D.DNAME, 
E.EMPNO, E.ENAME, E.MGR, E.SAL, E.DEPTNO, 
S.LOSAL, S.HISAL, S.GRADE, E1.EMPNO AS MGR_EMPNO, E1.ENAME AS MGR_ENAME
FROM DEPT D LEFT OUTER JOIN EMP E ON(D.DEPTNO = E.DEPTNO)
            LEFT OUTER JOIN SALGRADE S ON(E.SAL BETWEEN S.LOSAL AND S.HISAL)
            LEFT OUTER JOIN EMP E1 ON(E.MGR = E1.EMPNO)
ORDER BY D.DEPTNO, E.EMPNO;