190403

-- 문자를 날짜로 변경하기
SELECT TO_DATE('2019/01/31', 'YYYY/MM/DD'),
          TO_DATE('2019-01-31', 'YYYY-MM-DD'),
          TO_DATE ('01/31/2019', 'MM/DD/YYYY')
FROM DUAL;
 
SELECT TRUNC(TO_DATE('2019/08/19', 'YYYY/MM/DD') - SYSDATE)
FROM DUAL;
 
-- 2008년 3월 1일 - 2008년 3월 15일 사이에 입사한 사원의 아이디, 이름, 입사일 조회하기
SELECT EMPLOYEE_ID, FIRST_NAME, HIRE_DATE
FROM EMPLOYEES
WHERE HIRE_DATE >= TO_DATE('2008/03/01', 'YYYY/MM/DD')
AND HIRE_DATE <= TO_DATE('2008/03/15 23:59:59', 'YYYY/MM/DD HH24:MI:SS'); -- 23:59:59 안 쓰면 0:00:00시 까지라 3/15 아침 10시 입사자 데이터가 안 나옴
-- WHERE HIRE_DATE >= '2008/03/01'
-- AND HIRE_DATE < '2008/03/16';
 
-- 숫자 <-> 문자
-- 9: 소수점 이상 -> 숫자가 있으면 표현, 없으면 무시
--    소수점 이하 -> 숫자가 있으면 표현, 없으면 0을 표현
-- 0: 소수점 이상 -> 숫자가 있으면 표현, 없으면 0을 표현
--    소수점 이하 -> 숫자가 있으면 표현, 없으면 0을 표현
SELECT EMPLOYEE_ID, FIRST_NAME, TO_CHAR(SALARY, '99,999') -- 엑셀의 #과 같은 개념. 9 또는 0을 씀
        , TO_CHAR(COMMISSION_PCT, '9.99')
        , TO_CHAR(COMMISSION_PCT, '0.00')
FROM EMPLOYEES
WHERE COMMISSION_PCT IS NOT NULL;
 
SELECT EMPLOYEE_ID, FIRST_NAME, SALARY
FROM EMPLOYEES
WHERE SALARY > '15000'; -- 숫자로 묵시적 변환해줌 WHERE SALARY > TO_NUMBER('15,000, 99,999');
 
-- 기타 함수
-- NVL(컬럼, 값)
-- 지정된 컬럼의 값이 만약 NULL이라면 지정된 값으로 변환해서 제공
-- 지정된 컬럼의 값이 NULL이 아니면 원래 값을 제공
SELECT NVL(30, 0), NVL(NULL,0) -- 널에 대한 값 제공
FORM DUAL;
 
-- 사원의 이름, 급여, 커미션을 조회하기, 커미션이 없는 사원들은 0으로 표시하기
SELECT FIRST_NAME, SALARY, NVL(COMMISSION_PCT, 0)
FROM EMPLOYEES;
 
-- 사원의 이름, 급여, 커미션, 총급여(보너스가 포함된 급여)를 조회하기
SELECT FIRST_NAME, SALARY, COMMISSION_PCT, SALARY * (1+NVL(COMMISSION_PCT, 0)) AS 총급여
FROM EMPLOYEES;
 
-- NVL2(컬럼, 값1, 값2) 값1, 2는 같은 타입이어야 한다.
--       지정된 컬럼의 값이 만약 NULL이라면 값2 대체해서 제공한다.
--       지정된 컬럼의 값이 NULL이 아니면 값1로 대체해서 제공한다.
 
-- 사원의 이름, 급여, 커미션을 받는지 여부를 "유", "무"로 조회하기
SELECT FIRST_NAME, SALARY, NVL2(COMMISSION_PCT, '유', '무') AS 유무
FROM EMPLOYEES;
 
-- CASE ~ WHEN ~ END문
-- IF ELSE IF ELSE문의 역할을 수행한다.
-- CASE
--         WHEN 비교식 THEN 값1
--         WHEN 비교식 THEN 값2
--         WHEN 비교식 THEN 값3
--         ELSE 값4
-- END      
      
-- 부서번호가 50번인 부서는 'A팀', 60, 70번인 부서는 'B팀', 80번인 부서는 'C팀'으로 조회하기
SELECT FIRST_NAME, DEPARTMENT_ID,
         CASE
            WHEN DEPARTMENT_ID = 50 THEN 'A팀'
            WHEN DEPARTMENT_ID IN (60, 70) THEN 'B팀'
            WHEN DEPARTMENT_ID = 80 THEN 'C팀'
            END AS TEAM
FROM EMPLOYEES
WHERE DEPARTMENT_ID IN (50, 60, 70, 80)
ORDER BY TEAM;
 
-- 사원들의 급여 수준을 상중하로 조회하기
-- 사원 이름, 급여, 급여 수준을 조회하고, 급여 수준 순으로 정렬하기
-- 급여 수준 상:12000달러 이상, 중: 5000달러 이상, 하:5000달러 미만 
SELECT FIRST_NAME, SALARY, 
          CASE WHEN SALARY >= 12000 THEN '상'
                 WHEN SALARY >= 5000 THEN '중'
                 ELSE '하'
          END AS 급여수준
FROM EMPLOYEES;
 
-- 사원 이름, 급여, 급여 인상분을 조회하고, 급여 인상분 순으로 정렬하기
-- 인상 비율:12000달러 이상 5%, 중:5000달러 이상 10%, 하:5000달러 미만 15%
SELECT FIRST_NAME, SALARY
        , CASE WHEN SALARY >= 12000 THEN 1.05*SALARY
                 WHEN SALARY >= 5000 THEN 1.1*SALARY
                 ELSE 1.15*SALARY
        END AS 급여인상분
FROM EMPLOYEES
ORDER BY 급여인상분 DESC;
 
-- DECODE 함수
-- IF ~ ELSE IF ~ ELSE를 간편하게 구현할 수 있는 함수
-- DECODE(컬럼 혹은 표현식, 비교값1, 결과값1
--                                  비교값2, 결과값2
--                                             결과값3) <-- ELSE와 같음
SELECT FIRST_NAME, DEPARTMENT_ID,
         DECODE(DEPARTMENT_ID, 50, 'A팀',
                                          60, 'B팀',
                                          70, 'B팀',
                                          80, 'C팀') AS TEAM
FROM EMPLOYEES
WHERE DEPARTMENT_ID IN (50, 60, 70, 80)
ORDER BY TEAM;
 
-- 테이블의 조인
-- 조회할 데이터가 2개 이상의 테이블을 사용해야지만 획득되는 경우
-- 조인 방법: FROM 절에 데이터 획득에 필요한 테이블을 나열하기만 하면 된다. 조인 조건을 제공해야 된다(N개의 테이블을 조인하면 N-1개의 조인 조건이 있어야 한다). 조인 조건은 조인된 행들 중에서 의미있게 연결된 행만 선택하게 한다.
 
-- 사원의 아이디, 사원의 이름, 부서 아이디, 부서 이름으로 조회하기
-- 단, 직종 최저 급여가 3000달러 미만인 정보만 조회하기
SELECT A.EMPLOYEE_ID, A.FIRST_NAME, A.DEPARTMENT_ID, B.DEPARTMENT_NAME
FROM EMPLOYEES A, DEPARTMENTS B
WHERE A.DEPARTMENT_ID = B.DEPARTMENT_ID;
 
SELECT A.EMPLOYEE_ID, A.FIRST_NAME, A.JOB_ID, A.SALARY, B.JOB_TITLE, B.MIN_SALARY, B.MAX_SALARY
FROM EMPLOYEES A, JOBS B  -- 조인
WHERE A.JOB_ID = B.JOB_ID AND B.MIN_SALARY < 3000;  -- 조인 조건
 
-- 사원의 아이디, 사원 이름, 부서 이름을 조회하기
-- 단, 100번 사원이 부서 담당자로 지정된 부서에 소속된 사원만 조회하기
SELECT A.EMPLOYEE_ID, A.FIRST_NAME, B.DEPARTMENT_NAME
FROM EMPLOYEES A, DEPARTMENTS B
WHERE A.DEPARTMENT_ID = B.DEPARTMENT_ID AND B.MANAGER_ID = 100;
 
-- 부서 아이디, 부서명, 그 부서가 위치한 지역의 도시명을 조회하기
SELECT D.DEPARTMENT_ID, D.DEPARTMENT_NAME, L.CITY
FROM DEPARTMENTS D, LOCATIONS L
WHERE D.LOCATION_ID = L.LOCATION_ID;
 
-- 부서 아이디, 부서명, 부서 담당자 아이디, 부서 담당자(사원)의 이름, 부서 담당자(사원)의 연락처를 조회하기
SELECT D.DEPARTMENT_ID, D.DEPARTMENT_NAME, D.MANAGER_ID, E.FIRST_NAME, E.PHONE_NUMBER
FROM DEPARTMENTS D, EMPLOYEES E
WHERE D.MANAGER_ID = E.EMPLOYEE_ID;
 
-- 급여를 15000달러 이상 받는 사원들의 아이디, 사원 이름, 급여, 소속 부서 아이디, 소속 부서명을 조회하기
SELECT E.EMPLOYEE_ID, E.FIRST_NAME, E.SALARY, E.DEPARTMENT_ID, D.DEPARTMENT_NAME
FROM EMPLOYEES E, DEPARTMENTS D
WHERE E.DEPARTMENT_ID = D.DEPARTMENT_ID AND E.SALARY >= 15000;
 
SELECT FIRST_NAME
FROM EMPLOYEES
WHERE SALARY >= 15000;
 
-- 직종 최저 급여가 10000달러 이상인 직종에 근무 중인 사원의 아이디, 사원의 이름, 직종 아이디, 직종 제목 조회하기
SELECT E.EMPLOYEE_ID, E.FIRST_NAME, E.JOB_ID, J.JOB_TITLE
FROM JOBS J, EMPLOYEES E
WHERE J.JOB_ID = E.JOB_ID AND J.MIN_SALARY >= 10000;
 
-- 사원의 아이디, 사원 이름, 직종 아이디, 직종 제목, 소속 부서 아이디, 소속 부서명 조회하기
SELECT E.EMPLOYEE_ID, E.FIRST_NAME, E.JOB_ID, J.JOB_TITLE, E.DEPARTMENT_ID, D.DEPARTMENT_NAME
FROM EMPLOYEES E, JOBS J, DEPARTMENTS D
WHERE E.JOB_ID=J.JOB_ID AND E.DEPARTMENT_ID = D.DEPARTMENT_ID;
 
-- 등가, 비등가, 셀프, 아우터 조인
 
-- 모든 사원의 이름, 부서 번호, 부서 이름을 조회하기
SELECT EMPLOYEE_ID, E.DEPARTMENT_ID, D.DEPARTMENT_NAME 
FROM EMPLOYEES E, DEPARTMENTS D
WHERE E.DEPARTMENT_ID = D.DEPARTMENT_ID;
 
-- 소속 부서가 80번인 사원의 아이디, 직종 아이디, 직종 제목, 최소 급여, 최대 급여를 조회하기
SELECT E.EMPLOYEE_ID, E.JOB_ID, J.JOB_TITLE, J.MIN_SALARY, J.MAX_SALARY
FROM EMPLOYEES E, JOBS J
WHERE E.JOB_ID = J.JOB_ID AND E.DEPARTMENT_ID = 80;
 
-- 커미션을 받는 사원의 이름, 부서 이름, 도시명을 조회하기
SELECT E.FIRST_NAME, D.DEPARTMENT_NAME, L.CITY
FROM EMPLOYEES E, DEPARTMENTS D, LOCATIONS L
WHERE E.DEPARTMENT_ID = D.DEPARTMENT_ID AND D.LOCATION_ID = L.LOCATION_ID AND E.COMMISSION_PCT IS NOT NULL;
 
-- Toronto에서 근무하는 사원의 이름, 부서명을 조회하기
SELECT E.FIRST_NAME, D.DEPARTMENT_NAME
FROM EMPLOYEES E, DEPARTMENTS D, LOCATIONS L
WHERE E.DEPARTMENT_ID = D.DEPARTMENT_ID AND D.LOCATION_ID = L.LOCATION_ID AND L.CITY LIKE 'Toronto';
 
-- 145번 사원이 담당자로 지정된 부서에서 근무하는 
-- 사원의 이름, 부서명, 직종 제목, 최소급여, 최대급여를 조회하기
SELECT E.FIRST_NAME, DEPARTMENT_NAME, J.JOB_TITLE, J.MIN_SALARY, J.MAX_SALARY
FROM EMPLOYEES E, DEPARTMENTS D, JOBS J
WHERE E.DEPARTMENT_ID = D.DEPARTMENT_ID AND J.JOB_ID = E.JOB_ID AND D.MANAGER_ID = 145;
 
-- 101번 사원의 이름, 근무했던 직종 아이디, 근무했던 부서 아이디, 근무했던 부서명을 조회하기
SELECT E.FIRST_NAME, J.JOB_ID, J.DEPARTMENT_ID, D.DEPARTMENT_NAME
FROM EMPLOYEES E, DEPARTMENTS D, JOB_HISTORY J
WHERE J.DEPARTMENT_ID = D.DEPARTMENT_ID AND E.EMPLOYEE_ID = J.EMPLOYEE_ID AND E.EMPLOYEE_ID = 101;
 
-- 부서들 중에서 소재지가 Canada인 부서의 아이디, 부서명, 도시명을 조회하기
SELECT D.DEPARTMENT_ID, D.DEPARTMENT_NAME, L.CITY
FROM DEPARTMENTS D, LOCATIONS L, COUNTRIES C
WHERE D.LOCATION_ID = L.LOCATION_ID AND L.COUNTRY_ID = C.COUNTRY_ID AND C.COUNTRY_NAME LIKE 'Canada';
 
-- 2007년에 'SA_MAN'으로 근무했던 사원의 아이디, 이름, 현재 근무 중인 직종 아이디, 급여를 조회하기
SELECT E.EMPLOYEE_ID, E.FIRST_NAME, E.JOB_ID, E.SALARY
FROM EMPLOYEES E, JOB_HISTORY J
WHERE E.EMPLOYEE_ID = J.EMPLOYEE_ID 
AND TO_CHAR(J.START_DATE, 'YYYY') <= 2007 
AND TO_CHAR(J.END_DATE, 'YYYY') >= 2007
AND J.JOB_ID = 'SA_MAN';
 
-- 110번 부서에서 근무했던 경력이 있는 사원의 아이디, 이름, 부서명을 조회하기
SELECT E.EMPLOYEE_ID, E.FIRST_NAME, D.DEPARTMENT_NAME
FROM EMPLOYEES E, DEPARTMENTS D, JOB_HISTORY J
WHERE J.DEPARTMENT_ID = D.DEPARTMENT_ID AND E.EMPLOYEE_ID = J.EMPLOYEE_ID AND J.DEPARTMENT_ID = 110;