[KOSTA 교육 5일차] 서브쿼리와 GROUP BY 조합 | LEFT OUTER JOIN 주의하기 | DECODE, CASE-WHEN 문법 | WHERE 절에서 원본 컬럼을 변경하지 않는 한가지 방법(★) | SQL 문제 풀이

4일차 교육 핵심내용 정리

1. SELECT 절에서 그룹함수를 사용한다 해서, 꼭 GROUP BY를 사용해야하는 것은 아니다.

SELECT
	MAX(EMPNO)
FROM
	EMP;

• 전체 집합도 하나의 그룹이라 생각하면 된다.

2. MAX와 AVG와 같은 그룹함수는 무한 중첩이 가능하다.

3. GROUP BY로 묶어진 GROUP 에 대한 조건은 HAVING에 넣어줘야 한다. WHERE에 넣으면 안된다.

4. HAVING은 그 내부에 그룹함수를 포함할 수 있고, 아닐 수도 있다.

5. FROM 절의 SubQuery를 Inline View라 한다.

---

SELECT 절의 SubQuery는 PIVOT 처럼, 내가 보고자 하는 정보를 한 줄에 모아서 보기 위한 용도로 사용한다.

-- SELECT 절의 서브쿼리는 PIVOT 형식으로 내가 원하는 데이터를 뽑아서 사용할 수 있다.

-- 다음과 같은 결과를 출력하시오.
-- 부서별 카운트 수
--- CNT10,  CNT20,  CNT30 
---   3       5       6
SELECT 
    (SELECT COUNT(*) FROM EMP WHERE DEPTNO = 10) as CNT10,
    (SELECT COUNT(*) FROM EMP WHERE DEPTNO = 20) as CNT20,
    (SELECT COUNT(*) FROM EMP WHERE DEPTNO = 30) as CNT30
FROM
    DUAL;

LEFT OUTER JOIN 부분이 약점이었다.

-- 각 부서별 사원수를 출력하기.
-- 조건 1. 부서별 사원수가 없더라도, 부서번호, 부서명은 출력
-- 조건 2. 부서별 사원수가 0인 경우 '없음' 출력
-- 조건 3. 부서번호 오름차순 정렬

SELECT
    d.DEPTNO,
    d.DNAME,
    CASE COUNT(e.empno) 
        WHEN 0 THEN '없음' 
        ELSE TO_CHAR(COUNT(e.empno)) 
    END as 사원수
FROM
    DEPT d
LEFT OUTER JOIN
    EMP e
ON
    d.deptno = e.deptno
GROUP BY
    d.DEPTNO, d.DNAME
ORDER BY
    d.DEPTNO ASC;

• DEPT와 EMP 를 JOIN 할 때,
  DEPT에는 40번 부서가 있지만 EMP에는 40번 부서를 가진 사람이 없는 상황이다.
• 이 때, INNER JOIN ( EQUI JOIN )을 걸게 되면,
  40번 DEPT 번호가 누락된다.
• 만약, 40번 DEPT번호를 보여주려면 DEPT를 기준으로 LEFT OUTER JOIN을 걸어줘야 한다.
• 또한, COUNT는 그룹함수이고 이에 GROUP BY를 사용했으니,
  해당하는 일반 컬럼들은 GROUP BY 절에 명시해주어야 한다.

컬럼에 대한 '조건'을 걸어 출력할 수 있다.

• DECODE ( 프로그래밍 언어에서의 SWITCH문 처럼 사용할 수 있다 )
  - 단일 값에 대한 동등 조건만 조건으로 사용할 수 있다.
• CASE - WHEN - THEN - END ( 프로그래밍 언어에서의 IF 문처럼 사용할 수 있다. )
  - 특정 값의 범위를 조건으로 줄 수도 있다.

-- '없음' 표시는 어떻게 하냐?
-- 1. CASE-WHEN-THEN
SELECT
    d.DEPTNO,
    d.DNAME,
    CASE COUNT(e.empno)
        WHEN 0 THEN '없음' 
        ELSE TO_CHAR(COUNT(e.empno))  
        -- 위의 '없음'은 문자 타입이니, 아래의 COUNT(e.empno) 또한 형을 맞춰줘야 한다.
        -- TO_CHAR() 함수를 통해 형을 변환했다.
    END as 사원수 
FROM
    DEPT d
LEFT OUTER JOIN
    EMP e
ON
    d.deptno = e.deptno
GROUP BY
    d.DEPTNO, d.DNAME
ORDER BY
    d.DEPTNO ASC;

-- 2. DECODE ( SWITCH )
-- 값만 딱 표시
SELECT
    d.DEPTNO,
    d.DNAME,
    DECODE(COUNT(e.empno),
        0, '없음', 
        TO_CHAR(COUNT(e.empno))) as 사원수
        -- DECODE( 특정 값,
        	특정값이 어떤 값 1일 때, 어떤 값A 출력
            ...
            아무것도 포함되지않을때의 값) 과 같이 사용한다.
FROM
    DEPT d
LEFT OUTER JOIN
    EMP e
ON
    d.deptno = e.deptno
GROUP BY
    d.DEPTNO, d.DNAME
ORDER BY
    d.DEPTNO ASC;

-- CASE WHEN-THEN : 단일값 ( IF )
SELECT 
        (CASE
            WHEN DEPTNO = 10 THEN '십'
            WHEN DEPTNO = 20 THEN '이십'
            WHEN DEPTNO = 30 THEN '삼십'
            ELSE '아니다' -- 어떤 값에도 포함되지 않을 때의 결과를 나타낸다.
        END) AS nn
FROM
    DEPT;

-- CASE WHEN-THEN : 범위 값
-- CASE WHEN 조건문  THEN  ~~ ELSE END
SELECT 
        (CASE
            WHEN SAL BETWEEN 1000 AND 2000 THEN '1'
            -- CASE WHEN 문에는 조건으로 특정 컬럼값의 범위를 줄 수도 있다.
            WHEN SAL BETWEEN 2000 AND 3000 THEN '2'
            ELSE '99'
        END) AS nn,
        SAL
FROM
    EMP;
    

-- 각 부서별 평균 급여가 2000이상이면 초과
-- 그렇지 않으면 미만을 출력하라.

SELECT
    d.DEPTNO,
    (CASE
        WHEN AVG(e.SAL) >= 2000 THEN '초과'
        WHEN AVG(e.SAL) < 2000 THEN '미만'
    END) as 평균급여
FROM
    DEPT d
JOIN
    EMP e
ON
    d.deptno = e.deptno
GROUP BY
    d.DEPTNO;

SELF JOIN 문법 복습

-- 사원 테이블에서
-- 각 사원의 사원번호, 사원명, 매니저번호, 매니저명을 출력하시오.
-- 조건1. 각 사원의 급여(SAL)는 매니저 급여보다 많거나 같다.

SELECT
    e.empno,
    e.ename,
    m.mgr as mgrno,
    m.ename as mgrname
FROM
    EMP e
JOIN
    EMP m
ON
    e.mgr = m.empno
WHERE
    e.sal >= m.sal;

올림, 내림, 반올림

• CEIL(value) : 소수점 첫째자리에서 올림한다.
• FLOOR(value) : 소수점 첫째자리에서 내림한다.
• ROUND(value, n) : 반올림하여 소수점 n째자리까지 표현한다.
• TRUNC(value, n) : 내림하여 소수점 n째자리까지 표현한다.

-- CEIL (올림)
SELECT
    EMPNO,
    ENAME,
    HIREDATE,
    CEIL((SYSDATE - HIREDATE)/365) as 근무년수 
    -- DATE 타입 간에는 - 연산이 가능하다.
FROM
    EMP
WHERE
   (CEIL((SYSDATE - HIREDATE)/365)) >= 30;
   
-- ROUND (반올림)
SELECT
    EMPNO,
    ENAME,
    HIREDATE,
    ROUND((SYSDATE - HIREDATE)/365) as 근무년수
FROM
    EMP
WHERE
   (ROUND((SYSDATE - HIREDATE)/365)) >= 30;

-- FLOOR (내림, 소수점 첫째 자리에서 내림)
SELECT
    EMPNO,
    ENAME,
    HIREDATE,
    FLOOR((SYSDATE - HIREDATE)/365) as 근무년수
FROM
    EMP
WHERE
   (FLOOR((SYSDATE - HIREDATE)/365)) >= 30;

-- TRUNC : 소수점 까지 고려하여 내림.
SELECT
    EMPNO,
    ENAME,
    HIREDATE,
    TRUNC((SYSDATE - HIREDATE)/365, 1) as 근무년수
FROM
    EMP
WHERE
   (TRUNC((SYSDATE - HIREDATE)/365)) >= 30;

! 사실 위의 쿼리에는 큰 문제가 있다. 왜냐하면, WHERE절에서 원본 컬럼을 변경했기 때문이다. 해당 쿼리는 HIREDATE에 대해 생성된 INDEX를 타지 못하고, 원본 테이블을 Full-Scan 한다.

• 이 문제를 어떻게 해결할 수 있는가? ( ★★★★★★ )
  : 'Inline View'를 활용하자.
  즉, 테이블을 잘라낸 이후에 '그 테이블'에 생성된 컬럼에 대해 WHERE절에 조건을 넣어주자.
  : 테이블을 한번 잘라서 포장해서 새로운 테이블로 만들어, 거기에만 쿼리를 넣어주는 형식이다.

SELECT
		*
FROM
	( 
		SELECT
		    EMPNO,
		    ENAME,
		    HIREDATE,
		    FLOOR((SYSDATE - HIREDATE)/365) as 근무년수
		FROM
		    EMP
   ) a
WHERE
	  a.근무년수 > 30;

GROUP BY ( Column1, Column2, … ColumnN ) 의 의미

• **Column1 ~ ColumnN** 까지의 튜플의 조합을 기준으로
  Unique한 값을 뽑는다.
• 아래 문제의 예시와 같은 경우,’부서 별’ 유니크한 값을 뽑아야하는데
  GROUP BY를 이렇게 사용하면 안된다.
• GROUP BY에 특히, EMPNO ( PK ) 가 들어가면, 그건 GROUP BY를 한 의미가 없다.
• 마치, 서울 시민들을 모아놓아서, 주민번호 별로 그룹화 한것과 같다.

-- 잘못된 쿼리
SELECT
		EMPNO,
		ENAME,
		DEPTNO,
		MIN(HIREDATE) as HIREDATE
FROM
		EMP e
GROUP BY
		EMPNO, ENAME, DEPTNO;

SQL 문제 풀이 나머지

-- 각 부서 별 입사일이 가장 오래된 사원을
-- 한명씩 선별해 사원번호, 사원명, 부서번호, 입사일을 출력하라.
-- Multi-Row Column SubQuery로 해결했다.

SELECT
    EMPNO,
    ENAME,
    DEPTNO,
    HIREDATE
FROM
    EMP e
WHERE
    (e.deptno, e.hiredate)
IN
    ( 
        SELECT
            DEPTNO,
            MIN(HIREDATE) as hiredate
        FROM
            EMP
        GROUP BY
            DEPTNO
    );

GROUP BY + SubQuery 조합의 문제들

EMP 테이블에서 부서 인원이 4명보다 많은 부서의 부서번호, 인원수, 급여의 합을 출력하시오

SELECT
    DEPTNO as 부서번호,
    COUNT(EMPNO) as 인원수,
    SUM(SAL) as 급여의_합
FROM
    EMP
GROUP BY
    DEPTNO
HAVING
    COUNT(EMPNO) > 4;

• 그룹에 대한 조건을 줄 때는 HAVING절을 사용해야함을 복습했다.

EMP 테이블에서 가장 많은 사원이 속해있는 부서번호와 사원수 출력

SELECT
    DEPTNO,
    사원수
FROM (
    SELECT
        DEPTNO,
        COUNT(EMPNO) as 사원수
    FROM
        EMP
    GROUP BY
        DEPTNO
    ORDER BY
        사원수 DESC ) 
WHERE
    ROWNUM <= 1;
    

-- EMP 테이블에서 가장 많은 사원이 속해있는
-- 부서번호와 사원수 출력 ( HAVING 사용 )
SELECT
    DEPTNO,
    COUNT(EMPNO) as 사원수
FROM
    EMP
GROUP BY
    DEPTNO
HAVING
    COUNT(EMPNO) = (
        SELECT
            MAX(COUNT(EMPNO)) as 사원수max
        FROM
            EMP
        GROUP BY
            DEPTNO
    );

EMP 테이블에서 가장 많은 사원을 가진 MGR(★)

• 오늘 푼, GROUP BY와 SubQuery를 응용하는 예제 중 가장 어려웠다.

SELECT
    EMPNO
FROM
    EMP
WHERE
    EMPNO = (
        SELECT
            MGR
        FROM
            EMP
        GROUP BY
            MGR
        HAVING
            COUNT(MGR) = (
                SELECT
                    MAX(COUNT(MGR))
                FROM
                    EMP
                GROUP BY
                    MGR
        )
    );

• **MAX(COUNT(MGR))** 을 통해 계산할 때, **GROUP BY**를 써주는 것을 하지 못했다.
• 부분 부분 잡아서 끼워넣고,
  확인하는게 낫다.
  아무리 실력이 뛰어나도
  한 블럭씩 해보면서 진행하는게 실수를 안한다.
• 1-Depth의 서브쿼리까지는 간단히 되더라도,
  여러 Depth의 서브쿼리를 하려 하니 문제가 어려웠다.
• 앞으로도 서브쿼리는 **Bottom-Up** 방식으로 조금씩 완성시켜가며 진행하자.

-- 이렇게도 해결할 수 있다.
SELECT
    MGR
FROM
    EMP
GROUP BY
    MGR
HAVING
    COUNT(EMPNO) = (
        SELECT
            MAX(COUNT(MGR))
        FROM
            EMP
        GROUP BY
            MGR
    );