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
