Oracle 특수 쿼리 및 집합

-- 특수 Query
-- CASE DECODE

    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 as 지역
    from employees;
    
SELECT employee_id, first_name, phone_number,
    decode(substr(phone_number, 1, 3),
            '515', '서울',
            '590', '부산',
            '659', '광주',
            '603', '대전',
            '기타') as "지역"
FROM employees;

-- 분석 함수
-- 순위 Rank

-- RANK()       1 2 3 3 5 6
-- DENSE_RANK() 1 2 3 3 4 5 

-- 많이 사용    
-- ROW_NUMBER() 1 2 3 4 5 6
-- ROWNUM

-- OVER() PARTITION BY ORDER BY
-- over는 카운트에 대한 조건을 매기는것
-- 
--SELECT job_id, COUNT(*) 
--FROM employees; 만 지정하면 오류가 발생하는데 over를 통해 group화로 파티션을 묶어준다.

SELECT job_id, employee_id, COUNT(*)over(PARTITION by job_id)
FROM employees;

SELECT job_id, salary, count(*)over(PARTITION by job_id order by salary desc)
FROM employees;

SELECT first_name, 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_number
FROM employees;

-- rownum
-- 10명의 ROW만을 산출하는 경우
SELECT ROWNUM, employee_id, first_name, salary
FROM employees
where ROWNUM <= 10; -- 10명만 불러 온다.

-- 11 ~ 20의 ROW만을 산출하는 경우
SELECT ROWNUM, employee_id, first_name, salary  -- 2
FROM employees
where ROWNUM > 10 and ROWNUM <= 20; -- 1
--제대로 불려 지지 않는다

-- 서브 쿼리 사용

-- 1. 설정
-- 2. ROWNUM 완성 
-- 3. 범위를 설정
SELECT RNUM, employee_id, first_name, salary
FROM (SELECT
    ROWNUM as RNUM, employee_id, first_name, salary -- 2. ROWNUM 완성
FROM 
(SELECT employee_id, first_name, salary -- 1.데이터 설정 (원했던 데이터)
FROM employees
order by salary desc) 
)
where RNUM > 10 and RNUM <= 20;
 
/*
자동 정렬이 된다 
합집합 : UNION
교집합 : INTERSECT
차집합 : MINUS
*/

-- UNION
SELECT job_id
FROM employees
where job_id in('AD_VP', 'FI_ACCOUNT')
UNION
select job_id
from jobs
where job_id in('AD_VP', 'IT_PROG');

-- 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 employee_id  -- 매니저가 아닌 사원
FROM employees
minus
SELECT manager_id from employees;

-- SELECT 절
--SELECT 컬럼 or 함수 or 그룹함수 or SUB Query or over()
--FROM 테이블명 or sub subquery
--[ WHERE ] 조건절 in and or all like < > <> != =
--[ GROUP BY] 컬럼, ...
--[ HAVING ] 그룹핑의 조건
--[ ORDER BY ] 정렬 ASC(올림), DESC(내림)
--[ START BY ] 계층형
--[ CONNECT BY ] 연결 PRIOR(앞에 붙이면 상향, 뒤에붙이면 하향)

--JOIN
--INNER, OUTTER, SELF

--RANK()
--ROW_NUMBER()
--ROWNUM