Oracle 10g의 HR 계정을 이용한 예제

Oracle 10g에 포함된 HR(Human Resource) 계정을 이용해 해본 예제들입니다.
오라클 10g를 공부하신다면 아래 예제들을 한번씩 해보시기 바랍니다. ^ ^



1. 1998년에 입사한 사원의 정보를 얻자
 사번, 이름, 날자

SELECT EMPLOYEE_ID, LAST_NAME, HIRE_DATE
FROM EMPLOYEES
WHERE HIRE_DATE>='1998/01/01' AND HIRE_DATE<'1999/01/01'

SELECT EMPLOYEE_ID, LAST_NAME, HIRE_DATE
FROM EMPLOYEES
WHERE HIRE_DATE BETWEEN '1998/01/01' AND '1999/01/01'

SELECT EMPLOYEE_ID, LAST_NAME, HIRE_DATE
FROM EMPLOYEES
WHERE HIRE_DATE LIKE '98%'

SELECT EMPLOYEE_ID, LAST_NAME, HIRE_DATE
FROM EMPLOYEES
WHERE TO_CHAR(HIRE_DATE, 'YYYY') = '1998'

2. 직무가 SALES(영업) 부분에 속하는 사원 중에서 급여가 $10000 미만인 사

원의 정보를 얻자.

사번, 이름, 직무, 급여

SELECT EMPLOYEE_ID, LAST_NAME, JOB_ID, SALARY
FROM EMPLOYEES
WHERE JOB_ID IN('SA_MAN', 'SA_REP') AND SALARY<10000;

SELECT EMPLOYEE_ID, LAST_NAME, JOB_ID, SALARY
FROM EMPLOYEES
WHERE (JOB_ID='SA_MAN', JOB_ID='SA_REP') AND SALARY<10000;

SELECT EMPLOYEE_ID, LAST_NAME, JOB_ID, SALARY
FROM EMPLOYEES
WHERE JOB_ID LIKE 'SA_%' AND SALARY<10000;

3. 부서 아이디별로 해당 부서의 평균 급여를 얻자.
평균 급여가 많은 순으로 정렬하자.

부서아이디, 평균급여

SELECT DEPARTMENT_ID, AVG(SALARY)
FROM EMPLOYEES
WHERE DEPARTMENT_ID NOT IN(40, 50)
GROUP BY DEPARTMENT_ID
ORDER BY AVG(salary) DESC

SELECT DEPARTMENT_ID, AVG(SALARY)
FROM EMPLOYEES
GROUP BY DEPARTMENT_ID
HAVING DEPARTMENT_ID NOT IN(40, 50)
ORDER BY AVG(salary) DESC

4. 18.325 소수 둘째자리에서 반올림 처리하자.
SELECT ROUND(18.325, 1) "Round"
FROM dual;

5. 날자를 문자로 변환
SELECT TO_CHAR(sysdate, 'YYYY-MM-DD HH24:MI:SS') today
FROM dual

6. 월별 입사자의 합계를 나타내 보자.
SELECT TO_CHAR(HIRE_DATE,'MM') "월", COUNT(*)
FROM EMPLOYEES
GROUP BY TO_CHAR(HIRE_DATE,'MM')
ORDER BY "월"

7. 분기별 입사자의 합계를 나타내 보자.

SELECT TO_CHAR(HIRE_DATE,'Q') "분기", COUNT(*)
FROM EMPLOYEES
GROUP BY TO_CHAR(HIRE_DATE,'Q')
ORDER BY "분기"

SELECT case
    when TO_CHAR(hire_date, 'MM')='01' then '1'
    when TO_CHAR(hire_date, 'MM')='02' then '1'
    when TO_CHAR(hire_date, 'MM')='03' then '1'
    when TO_CHAR(hire_date, 'MM')='04' then '2'
    when TO_CHAR(hire_date, 'MM')='05' then '2'
    when TO_CHAR(hire_date, 'MM')='06' then '2'
    when TO_CHAR(hire_date, 'MM')='07' then '3'
    when TO_CHAR(hire_date, 'MM')='08' then '3'
    when TO_CHAR(hire_date, 'MM')='09' then '3'
    when TO_CHAR(hire_date, 'MM')='10' then '4'
    when TO_CHAR(hire_date, 'MM')='11' then '4'
    else '4'
  end
  , count(employee_id)
FROM EMPLOYEES
GROUP BY case
    when TO_CHAR(hire_date, 'MM')='01' then '1'
    when TO_CHAR(hire_date, 'MM')='02' then '1'
    when TO_CHAR(hire_date, 'MM')='03' then '1'
    when TO_CHAR(hire_date, 'MM')='04' then '2'
    when TO_CHAR(hire_date, 'MM')='05' then '2'
    when TO_CHAR(hire_date, 'MM')='06' then '2'
    when TO_CHAR(hire_date, 'MM')='07' then '3'
    when TO_CHAR(hire_date, 'MM')='08' then '3'
    when TO_CHAR(hire_date, 'MM')='09' then '3'
    when TO_CHAR(hire_date, 'MM')='10' then '4'
    when TO_CHAR(hire_date, 'MM')='11' then '4'
    else '4'
  end
8. 영업에 관한 직무를 다음과 같이 나타내 보자.
SA_MAN -> Sales Dept
SA_REP -> Sales Dept
기타 부서 -> Another Dept

SELECT employee_id, last_name, job_id,
  case job_id
    when 'SA_REP'
      then 'Sales Dept'
    when 'SA_MAN'
      then 'Sales Dept'
    else 'Another Dept'
  end case
FROM EMPLOYEES
WHERE job_id LIKE 'S%'

9. SELECT employee_id, department_id, salary
FROM employees
WHERE department_id IN(10, 20, 30)

위 쿼리를 통해 자신이 속한 부서의 평균급여를 추가적으로 출력한다.

SELECT employee_id
  , department_id
  , salary
  , AVG(salary) OVER(PARTITION BY department_id) "AVG_SALARY"
--AVG(expr) OVER(analytical clause)
FROM employees
WHERE department_id IN(10, 20, 30)

10. 급여를 5000 받는사람이 급여의 순위가 상위 몇번째인가

SELECT RANK(5000) WITHIN GROUP(ORDER BY salary DESC) "RANK"

FROM employees