Database_61_SQL 활용 실습 4

    -- PRACTICE 4
-- 사원의 이름, 입사일 및 급여 검토일을 표시하시오. 급여 검토일은 여섯 달이 경과한 후 첫번째 월요일입니다. 
    -- 열 레이블을 REVIEW로 지정하고 날짜는 “2020.03.31 월요일”과 같은 형식으로 표시되도록 지정
SELECT last_name, hire_date, salary, TO_CHAR(NEXT_DAY(ADD_MONTHS(hire_date, 6), '월요일'), 'YYYY-MM.DD DAY') AS review
FROM employees;

-- 이름, 입사일 및 업무 시작 요일을 표시하고 열 레이블을 DAY로 지정하시오. 월요일을 시작으로 해서 요일을 기준으로 결과를 정렬
SELECT last_name, hire_date, TO_CHAR(hire_date, 'DAY') AS day
FROM employees;

-- 사원의 이름과 커미션을 표시하는 질의를 작성하시오. 커미션을 받지 않는 사원일 경우 “No Commission”을 표시 
    -- 열 레이블은 COMM으로 지정
SELECT last_name, NVL(TO_CHAR(commission_pct), 'NO Commission') AS comm
FROM employees;

-- 하나의 레이블로 사원의 이름과 급여 총액을 별표(*)로 나타내는 질의를 작성하시오. 
    -- 각 별표는 1,000달러를 나타냅니다. 
    -- 급여를 기준으로 데이터를 내림차순으로 정렬하고 열 레이블을 EMPLOYEES_AND_THEIR_SALARIES로 지정
SELECT RPAD(last_name, 8) || ' ' || RPAD(' ', salary/1000+1, '*') AS employees_and_their_salaries
FROM employees
ORDER BY salary DESC;

-- DECODE 함수와 CASE 구문을 사용하여 다음 데이터에 따라 JOB_ID 열의 값을 기준으로 모든 사원의 등급을 표시
SELECT last_name, job_id, DECODE( job_id, 'AD_PRES', 'A',
                                        'ST_MAN', 'B',
                                        'IT_PROG', 'C',
                                        'SA_REP', 'D',
                                        'ST_CLERK', 'E',
                                        '0') AS grade
FROM employees;

SELECT last_name, job_id, CASE job_id WHEN  'AD_PRES' THEN 'A'
                                        WHEN 'ST_MAN' THEN 'B'
                                        WHEN 'IT_PROG' THEN 'C'
                                        WHEN 'SA_REP' THEN 'D'
                                        WHEN 'ST_CLERK' THEN 'E'
                                        ELSE '0'
                                        END AS grade
FROM employees;