SQL. to_number / to_char / to_date / 일반함수 / 산술 연산 / nvl / nvl2 / decode / case / when / else / end as / rank / dense_rank / row_number / count / sum / avg / max / min / group by / having / join / 조인 / 동등조인 / 외부조인 / 라이트 조인 / 레프트 조인 / 자체조인 / 집합 연산자 / union

--문자 '123'을 숫자 123으로 가져오기 : TO_NUMBER()
SELECT TO_NUMBER('123')
FROM DUAL;

--숫자 지정 형식 : TO_CHAR()
SELECT TO_CHAR(SALARY, 'L9999999'),
    TO_CHAR(SALARY, '$9999999'),
    TO_CHAR(SALARY, '99999.99'),
    TO_CHAR(SALARY, '9,999,999')
FROM EMPLOYEES;

--문자 데이터 값을 날짜 데이터 값으로 변환하는 예: TO_DATE()
SELECT TO_DATE('20171007', 'YYMMDD')
FROM DUAL;

--1. 일반함수
SELECT *
FROM EMPLOYEES
ORDER BY COMMISSION_PCT;

--2. 산술 연산 적용
SELECT SALARY * COMMISSION_PCT
FROM EMPLOYEES
ORDER BY COMMISSION_PCT;

--3. 2번의 NULL값을 계산하기 위해 1로 변환해서 산술 연산 적용
SELECT SALARY * NVL(COMMISSION_PCT, 1)
FROM EMPLOYEES
ORDER BY COMMISSION_PCT;

--4. NVL2 (열 이름, 1열 이름, 2열 이름)
--열 이름1 이 NULL이 아니면 열 이름2를 출력
SELECT
    FIRST_NAME,
    LAST_NAME,
    SALARY,
    COMMISSION_PCT,
    NVL2(COMMISSION_PCT, SALARY * (1 + COMMISSION_PCT), SALARY) AS TOTAL_COMPENSATION
FROM EMPLOYEES;

--5. DECODE = IF문
SELECT
    FIRST_NAME,
    LAST_NAME,
    DEPARTMENT_ID,
    SALARY,
    DECODE(DEPARTMENT_ID, 60, SALARY*1.1, SALARY),
    DECODE(DEPARTMENT_ID, 60, '10%인상', '미인상')
FROM EMPLOYEES;

SELECT
    FIRST_NAME,
    LAST_NAME,
    DECODE
        (DEPARTMENT_ID,
        10,'과학',
        100,'수학',
        110,'영어',
        '기타')
    AS 과목명
FROM EMPLOYEES;

--6. CASE
SELECT
    EMPLOYEE_ID,
    FIRST_NAME,
    SALARY,
    CASE
        WHEN SALARY >= 9000 THEN '상위급여'
        WHEN SALARY BETWEEN 6000 AND 8999 THEN '중위급여'
        ELSE '하위급여'
    END AS 급여동급
FROM EMPLOYEES
WHERE JOB_ID = 'IT_PROG';

-- RANK() : 순위 중복이 있되, 다음 등수 건너뜀
-- DENSE_RANK() : 순위 중복이 있되, 다음 등수 건너뛰지 않음
-- ROW_NUMBER() : 순위 중복 없음
SELECT
    EMPLOYEE_ID,
    SALARY,
        RANK()      OVER(ORDER BY SALARY DESC),
        DENSE_RANK()OVER(ORDER BY SALARY DESC),
        ROW_NUMBER()OVER(ORDER BY SALARY DESC)
FROM EMPLOYEES;

SELECT
    E.EMPLOYEE_ID,
    E.DEPARTMENT_ID,
    D.DEPARTMENT_NAME,
    SALARY,
        RANK()      OVER(PARTITION BY E.DEPARTMENT_ID ORDER BY SALARY DESC),
        DENSE_RANK()OVER(PARTITION BY E.DEPARTMENT_ID ORDER BY SALARY DESC),
        ROW_NUMBER()OVER(PARTITION BY E.DEPARTMENT_ID ORDER BY SALARY DESC)
FROM EMPLOYEES E, DEPARTMENTS D
WHERE E.DEPARTMENT_ID = D.DEPARTMENT_ID
ORDER BY D.DEPARTMENT_ID, E.SALARY DESC;

-- COUNT : 행 개수를 셈, NULL값도 행으로 셀수있음
SELECT
    COUNT(*)-COUNT(COMMISSION_PCT)
FROM EMPLOYEES;

SELECT
    COUNT(COMMISSION_PCT)
FROM EMPLOYEES
WHERE COMMISSION_PCT IS NOT NULL;

-- SUM : 합계 / AVG : 평균
SELECT
    SUM(SALARY),
    ROUND(AVG(SALARY),0),
    ROUND(SUM(SALARY)/COUNT(SALARY),0)
FROM EMPLOYEES;

-- MAX. MIN. 최댓값. 최솟값
SELECT
    MAX(SALARY),
    MIN(SALARY),
    MAX(FIRST_NAME),
    MIN(FIRST_NAME)
FROM EMPLOYEES;

-- GROUP BY : 그룹으로 묶기
SELECT
    JOB_ID 직무,
    SUM(SALARY) 직무별_총급여,
    AVG(SALARY) 직무별_평균급여
FROM EMPLOYEES
WHERE EMPLOYEE_ID >= 10
GROUP BY JOB_ID
ORDER BY 직무별_총급여 DESC, 직무별_평균급여;

SELECT
    JOB_ID JOB_ID_대그룹,
    MANAGER_ID MANAGER_ID_중그룹,
    SUM(SALARY) 그룹핑_총급여,
    AVG(SALARY) 그룹핑_평균급여
FROM EMPLOYEES
WHERE EMPLOYEE_ID >= 10
GROUP BY JOB_ID, MANAGER_ID
ORDER BY 그룹핑_총급여 DESC, 그룹핑_평균급여;

-- HAVING : GROUP BY 절의 조건식 FROM -> WHERE
SELECT
    JOB_ID 직무,
    SUM(SALARY) 직무별_총급여,
    AVG(SALARY) 직무별_평균급여
FROM EMPLOYEES
WHERE EMPLOYEE_ID >= 10
GROUP BY JOB_ID
HAVING SUM(SALARY) > 30000
ORDER BY 직무별_총급여 DESC, 직무별_평균급여;

-- SELECT 열이름, 그룹함수(열이름)
-- FROM 테이블 이름
-- [WHERE FROM의 조건식]
-- GROUP BY 열이름
-- [HAVING GROUP BY의 조건식]
-- [ORDER BY 열 이름];


-- 동등 조인 : 똑같은 데이터끼리 연결하기
SELECT *
FROM
    EMPLOYEES A, DEPARTMENTS B
WHERE
    A.DEPARTMENT_ID = B.DEPARTMENT_ID;

-- 동등 조인 : 조인 조건 추가
SELECT
    A.EMPLOYEE_ID, A.DEPARTMENT_ID,
    B.DEPARTMENT_NAME,
    C.LOCATION_ID, C.CITY
FROM 
    EMPLOYEES A, DEPARTMENTS B, LOCATIONS C
WHERE 
    A.DEPARTMENT_ID = B.DEPARTMENT_ID -- 조인1
    -- A : EMPLOYEES의 DEPARTMENT_ID   
    -- B : DEPARTMENTS의 DEPARTMENT_ID
AND
    B.LOCATION_ID = C.LOCATION_ID; -- 조인 2 : AND를 이용해서 조인 조건 추가
    -- B : DEPARTMENTS의 LOCATION_ID
    -- C : LOCATIONS의 LOCATION_ID
    
-- 외부 조인 : 모든 데이터를 연결하기, 데이터가 부족한쪽에 +를 붙여 조인한다
-- 왼쪽에 A(+) = B가 붙으면 라이트 조인, A = B(+) 레프트 조인
SELECT
    A.EMPLOYEE_ID,
    A.FIRST_NAME, 
    A.LAST_NAME,
    B.DEPARTMENT_ID,
    B.DEPARTMENT_NAME
FROM
    EMPLOYEES A,
    DEPARTMENTS B
WHERE
    A.DEPARTMENT_ID = B.DEPARTMENT_ID(+)
ORDER BY
    A.EMPLOYEE_ID;


-- 자체 조인 : 자기 자신의 데이터와 연결하기
SELECT
    A.EMPLOYEE_ID,
    A.FIRST_NAME, 
    A.LAST_NAME,
    A.MANAGER_ID,
    B.FIRST_NAME || ' ' || B.LAST_NAME MANAGER_NAME
FROM
    EMPLOYEES A,
    EMPLOYEES B
WHERE
    A.MANAGER_ID = B.EMPLOYEE_ID
ORDER BY
    A.EMPLOYEE_ID;
    
-- 집합 연산자 : 집합으로 연결하기
SELECT DEPARTMENT_ID
FROM EMPLOYEES
--UNION : 중복되는 값 제외
UNION ALL -- : 중복되는 값 포함 
--INTERSECT
SELECT DEPARTMENT_ID
FROM DEPARTMENTS
ORDER BY DEPARTMENT_ID;

-- MINUS 
SELECT DEPARTMENT_ID
FROM DEPARTMENTS
MINUS
SELECT DEPARTMENT_ID
FROM EMPLOYEES;

-- JOIN문
SELECT
    E.FIRST_NAME,
    E.LAST_NAME,
    D.DEPARTMENT_NAME
FROM
    EMPLOYEES E
JOIN departments D ON e.department_id = d.department_id;

-- JOIN문 안쓰고 출력해보기 1.
SELECT
    E.FIRST_NAME,
    E.LAST_NAME,
    D.DEPARTMENT_NAME
FROM
    EMPLOYEES E, departments D
WHERE E.DEPARTMENT_ID = D.DEPARTMENT_ID;