[오라클DB] CASE & DECODE

--DECODE
SELECT * FROM STUDENT;
SELECT STU_NAME,DECODE(STU_GENDER,'M','남','F','여','기타') AS 성별
FROM STUDENT;

SELECT * FROM EMP;

SELECT DEPTNO, DECODE(DEPTNO,'10','NEW YORK'
                            ,'20','DALLAS'
                            ,'30','CHICAHO'
                            ,'40','BOSTON'
                            ,'NONE') AS 위치
FROM EMP;


--CASE ~WHEN
SELECT CASE WHEN STU_GENDER = 'M' THEN '남'
            WHEN STU_GENDER = 'F' THEN '여'
            ELSE '알수없음'
       END AS GENDER, STU_NAME
FROM STUDENT;

--1. EMP 테이블에서 부서번호가 10인 사원수와 부서번호가 30인 사원수를 각각 출력하라.

SELECT 
    COUNT(DECODE(DEPTNO, 10, 1)) DEPT10,
    COUNT(DECODE(DEPTNO, 30, 1)) DEPT30
FROM EMP;

--2. EMP 테이블에서 가장 많은 사원이 속해있는 부서번호와 사원수를 출력하라.
SELECT COUNT(*), DEPTNO
FROM EMP
GROUP BY DEPTNO
HAVING COUNT(*) = (
                SELECT MAX(COUNT(*))
                FROM EMP
                GROUP BY DEPTNO
);




SELECT * FROM EMP;
SELECT * FROM DEPT;
SELECT * FROM SALGRADE;