특수 Query

-- 특수Query
-- CASE
SELECT EMPLOYEE_ID, FIRST_NAME, PHONE_NUMBER,
    CASE SUBSTR(PHONE_NUMBER, 1, 3) -- 스위치 케이스문과 기능이 비슷함
        WHEN '515' THEN '서울'
        WHEN '590' THEN '부산'
        WHEN '659' THEN '광주'
        WHEN '603' THEN '대전'
        ELSE '기타'
    END AS "지역"
FROM EMPLOYEES;
 
SELECT EMPLOYEE_ID, FIRST_NAME, PHONE_NUMBER,
    CASE 
         WHEN SUBSTR(PHONE_NUMBER, 1, 3) = '515' THEN '서울'
         WHEN SUBSTR(PHONE_NUMBER, 1, 3) = '590' THEN '부산'
         WHEN SUBSTR(PHONE_NUMBER, 1, 3) = '659' THEN '광주'
         WHEN SUBSTR(PHONE_NUMBER, 1, 3) = '603' THEN '대전'
         ELSE '기타'
    END "지역"
FROM EMPLOYEES;
 
-- DECODE
SELECT EMPLOYEE_ID,FIRST_NAME, PHONE_NUMBER,
    DECODE(SUBSTR(PHONE_NUMBER, 1, 3), 
            '515','서울',
            '590','부산',
            '659','광주',
            '603','대전', '기타') AS "지역"
FROM EMPLOYEES;
 
/*
NVL(대상컬럼,값)
대상컴럼 != NULL -> 대상 컬럼
대상컴럼 == NULL -> 값

NVL2(대상컬럼,값1,값2)
대상컴럼 != NULL -> 값1
대상컴럼 == NULL -> 값2
*/
SELECT LAST_NAME,FIRST_NAME,NVL2(MANAGER_ID,'직원','사장') 
FROM EMPLOYEES;
-- 분석 함수(순위)
-- RANK() 순위 구하는 함수     1 2 3 4 5 6
-- DENSE_RANK()             1 2 3 3 4 5
-- ROW_NUMBER()             1 2 3 4 5 6
-- ROWNUM
 
SELECT FIRST_NAME,JOB_ID,SALARY,
    RANK() OVER (ORDER BY SALARY DESC) AS RANK,
    DENSE_RANK() OVER (ORDER BY SALARY DESC) AS DENSE_RANK,
    ROW_NUMBER() OVER (ORDER BY SALARY DESC) AS ROW_RANK
FROM EMPLOYEES
ORDER BY SALARY DESC;
 
-- 급여가 10위안의 사원을 출력
-- 정렬 제대로 안됨
SELECT ROWNUM, FIRST_NAME, JOB_ID,SALARY
FROM EMPLOYEES
WHERE ROWNUM <= 10
ORDER BY SALARY DESC;
 
-- 정렬 됨
SELECT RNUM,FIRST_NAME,JOB_ID,SALARY
FROM (SELECT ROWNUM AS RNUM, EMP.FIRST_NAME, EMP.JOB_ID,EMP.SALARY  -- 2. ROWNUM 설정
      FROM (SELECT *                  -- 1. 정렬
            FROM EMPLOYEES
            ORDER BY SALARY DESC) EMP)
WHERE RNUM >= 10 AND RNUM<=20;      -- 3. 범위 설정
 
/*
합집합 UNION
교집합 INTERSECT
차집합 MINUS
*/
-- 합집합
-- UNION
SELECT JOB_ID
FROM EMPLOYEES
WHERE JOB_ID IN ('AD_VP','FI_ACCOUNT')
UNION ALL
SELECT JOB_ID
FROM JOBS
WHERE JOB_ID IN ('AD_VP','FI_ACCOUNT');
 
-- 교집합
-- INTERSECT
SELECT EMPLOYEE_ID
FROM EMPLOYEES
INTERSECT
SELECT MANAGER_ID
FROM EMPLOYEES;
 
-- JOIN으로 표현
SELECT DISTINCT A.EMPLOYEE_ID
FROM EMPLOYEES A, EMPLOYEES B
WHERE A.EMPLOYEE_ID = B.MANAGER_ID;
 
-- 차집합
-- MINUS
SELECT MANAGER_ID
FROM EMPLOYEES
MINUS
SELECT EMPLOYEE_ID
FROM EMPLOYEES;
 
-- SELECT 절
/*

SELECT 컬럼
FROM 테이블
[ WHERE ] 조건절
[ GROUP BY ] 그룹핑
[ HAVING ] 그룹핑 조건
[ ORDER BY ] 정렬 ( ASC = 올림, DESC = 내림 )
[ START BY ] 계층형
[ CONNECT BY ] 연결

*/