Day 41. [Oracle SQL]

CASE 함수

: CASE 함수는 DECODE 함수의 기능을 확장한 함수

 

CASE expression WHEN comparison exp1 THEN return exp1

      [WHEN comparison exp2 THEN return exp2

      ELSE else expression]

END

 

DECODE 사용시 주의사항

	DECODE	CASE WHEN
기능	- 조건단위 분기 - IF 문 대응	- 조건단위 분기 - SWITCH CASE 문 대응
좋은 점	- 코드의 함축성	- 전형적인 구문으로 가독성 향상 - 비정형 비교 및 범위비교가 쉽다.
불편한 점	- 여러 조건이 한꺼번에 뭉쳐질 때 복잡하다. - 범위 비교시 Sign 함수 함께 사용으로 성능에 영향 - 비정형 비교 시 코드생성이 어려워짐 - 가독성이 낮다.	- 복잡해 질수록 라인 수 증가
사족	- 마지막 값 누락에 의해 실수 발생

 

 

 

 

-- 교수 테이블에서 소속 학과에 따라 보너스를 다르게 계산하여 춗력하여라.\
-- 학과 번호별로 보너스는 다음과 같이 계산핚다.
-- 학과 번호가 101이면 보너스는 급여의 10%, 102이면 20%, 201이면 30%, 나머지 학과는 0%이다

SELECT
    PROFNO, NAME, SAL, DEPTNO,
    DECODE(DEPTNO,
        101, SAL * 0.1,
        102, SAL * 0.2,
        201, SAL * 0.3,
        0
    ) AS BONUS,
    CASE DEPTNO
        WHEN 101 THEN SAL * 0.1
        WHEN 102 THEN SAL * 0.2
        WHEN 201 THEN SAL * 0.3
        ELSE 0
    END AS BONUS2
FROM PROFESSOR;

SELECT
STUDNO,
NAME,
CASE 
    WHEN M IN (3, 4, 5) THEN '봄'
    WHEN M BETWEEN 6 AND 8 THEN '여름'
    WHEN M BETWEEN 9 AND 11 THEN '가을'
    ELSE '겨울'
END SEASON
FROM (
    SELECT STUDNO, NAME, BIRTHDATE, TO_CHAR(BIRTHDATE, 'MM') AS M
    FROM STUDENT
);

SELECT STUDNO, NAME, USERID
FROM STUDENT
WHERE USERID >= 'DDDD';

SELECT DEPTNO, COUNT(DEPTNO)
FROM STUDENT
WHERE DEPTNO IS NOT NULL
GROUP BY DEPTNO
HAVING COUNT(DEPTNO) <= 5;

SELECT DEPTNO
FROM STUDENT
GROUP BY DEPTNO;

SELECT DISTINCT DEPTNO, COUNT(DEPTNO)
FROM STUDENT;

-- 101번 학과 교수중에서 보직수당을 받는 교수의 수를 출력하여라

SELECT COUNT(COMM), COUNT(*)
FROM PROFESSOR
WHERE DEPTNO = 101;

SELECT AVG(WEIGHT), SUM(WEIGHT), SUM(WEIGHT) / COUNT(WEIGHT)
FROM STUDENT
WHERE DEPTNO = 101;

-- 102번학과 학생중에서 최대키와 최소키를 출력하여라
SELECT MAX(HEIGHT), MIN(HEIGHT)
FROM STUDENT
WHERE DEPTNO = 102;

SELECT DEPTNO, POSITION, COUNT(*)
FROM PROFESSOR
GROUP BY DEPTNO, POSITION;

-- 교수테이블에서 학과별로 교수수와 보직수당을 받는 교수수를 출력하여라
SELECT DEPTNO, COUNT(*), COUNT(COMM)
FROM PROFESSOR
GROUP BY DEPTNO
ORDER BY 1;

-- 학과별로 소속교수들의 평균 급여, 최소 급여, 최대 급여를 출력하여라
SELECT DEPTNO, AVG(SAL), MIN(SAL), MAX(SAL)
FROM PROFESSOR
GROUP BY DEPTNO
ORDER BY 1;

SELECT DEPTNO, SAL
FROM PROFESSOR
ORDER BY 1;

-- 젂체 학생을 소속 학과별로 나누고, 같은 학과 학생은 다시 학년
-- 별로 그룹핑하여, 학과와 학년별 인원수, 평균 몸무게를 춗력하
-- 여라, 단, 평균 몸무게는 소수점 이하 첪번째 자리에서 반올림 핚다.
SELECT DEPTNO, GRADE, COUNT(*), ROUND(AVG(WEIGHT))
FROM STUDENT
GROUP BY DEPTNO, GRADE
ORDER BY 1, 2;

--소속 학과별로 교수 급여 합계와 모든 학과 교수들의 급여 합계를 춗력하여라
SELECT DEPTNO, SUM(SAL)
FROM PROFESSOR
GROUP BY ROLLUP(DEPTNO);

SELECT SUM(SAL)
FROM PROFESSOR;

-- ROLLUP 연산자를 이용하여 학과 및 직급별 교수수, 학과별 교수수, 전체 교수수를 출력하여라
SELECT DEPTNO, POSITION, COUNT(*)
FROM PROFESSOR
GROUP BY ROLLUP(DEPTNO, POSITION);
--GROUP BY CUBE(DEPTNO, POSITION);

-- 학생 수가 4명이상인 학년에 대해서 학년, 학생 수, 평균 키, 평균
-- 몸무게를 춗력하여라. 단, 평균 키와 평균 몸무게는 소수점 첪 번
-- 째 자리에서 반올림 하고, 춗력순서는 평균 키가 높은 순부터 내림차순으로 춗력하여라.
SELECT GRADE, COUNT(*), ROUND(AVG(HEIGHT)) AS AVGHEIGHT, ROUND(AVG(WEIGHT)) AS AVGWEIGHT
FROM (SELECT * FROM STUDENT WHERE GRADE IS NOT NULL)
GROUP BY GRADE
--WHERE COUNT(*) >= 4
HAVING COUNT(*) >= 4
ORDER BY 3 DESC;

--과별 학생의 평균 몸무게 중 최대 평균 몸무게를 출력하여
SELECT DEPTNO, AVG(WEIGHT)
FROM STUDENT
WHERE DEPTNO IS NOT NULL
GROUP BY 1;

SELECT DEPTNO, A_WEIGHT
FROM
    (SELECT DEPTNO, AVG(WEIGHT) A_WEIGHT
    FROM STUDENT
    GROUP BY DEPTNO) A,
    (SELECT MAX(AVG(WEIGHT)) B_WEIGHT
    FROM STUDENT
    GROUP BY DEPTNO) B
WHERE B.B_WEIGHT = A.A_WEIGHT;

SELECT * 
FROM STUDENT, DEPARTMENT;

/*
A   B
1   1
2   3
3   5
4

-- 내부 조인 : NULL 비포함 (계속 하던거)
-- 외부 조인 : NULL 포함

*/


SELECT *
FROM
    (
    SELECT ROWNUM AS A
    FROM DICT
    WHERE ROWNUM <=4
    ) A,
    (SELECT (ROWNUM - 1) * 2 + 1 AS B
    FROM DICT
    WHERE ROWNUM <= 3
    ) B
WHERE A.A = B.B;

SELECT *
FROM STUDENT S, DEPARTMENT D
WHERE S.DEPTNO = D.DEPTNO;

-- 전인하 학생의 학번, 이름, 학과이름 그리고 학과 위치를 출력하여라

SELECT 
    STUDNO, NAME, DNAME, COLLEGE, LOC
FROM 
    STUDENT S, DEPARTMENT D
WHERE 
    NAME = '전인하' 
    AND S.DEPTNO = D.DEPTNO;

SELECT
    STUDNO, NAME, DNAME, LOC
FROM
    STUDENT
NATURAL JOIN DEPARTMENT
WHERE
    NAME = '전인하';
    
SELECT * FROM STUDENT;
SELECT * FROM PROFESSOR;

-- 학번, 학생이름, 교수번호, 담당교수이름
SELECT S.STUDNO, S.NAME AS S_NAME, P.PROFNO, P.NAME AS P_NAME
FROM STUDENT S, PROFESSOR P
WHERE S.PROFNO = P.PROFNO;

SELECT STUDNO, S.NAME, PROFNO, P.NAME
FROM STUDENT S
JOIN PROFESSOR P
USING(PROFNO);