34_ DB 실습 문제

HR 스키마 문제 정답 SQL 정리

테이블

• hr.EMPLOYEES
• hr.DEPARTMENTS

문제별로 정확한 SQL을 정리

 

문제 1

부서별로 직원들의 평균 급여를 구하시오.

SELECT DEPARTMENT_ID, AVG(SALARY) AS AVG_SALARY
FROM hr.EMPLOYEES
GROUP BY DEPARTMENT_ID;

 

문제 2

부서별 직원 수가 5명 이상인 부서의 부서 ID와 총 급여

SELECT DEPARTMENT_ID, SUM(SALARY) AS TOTAL_SALARY
FROM hr.EMPLOYEES
GROUP BY DEPARTMENT_ID
HAVING COUNT(*) >= 5;

 

문제 3

직무별 최고 급여를 받는 직원의 이름과 급여

방법 1) 서브쿼리 사용(가장 정석)

SELECT e.JOB_ID, e.FIRST_NAME, e.LAST_NAME, e.SALARY
FROM hr.EMPLOYEES e
WHERE e.SALARY = (
    SELECT MAX(SALARY)
    FROM hr.EMPLOYEES
    WHERE JOB_ID = e.JOB_ID
);

 

방법 2) ANALYTIC 함수 사용 (가장 깔끔)

SELECT JOB_ID, FIRST_NAME, LAST_NAME, SALARY
FROM (
    SELECT e.*, 
           RANK() OVER (PARTITION BY JOB_ID ORDER BY SALARY DESC) AS RK
    FROM hr.EMPLOYEES e
)
WHERE RK = 1;

 

문제 4

시퀀스를 사용하여 새로운 직원 삽입

시퀀스 생성:

CREATE SEQUENCE EMP_SEQ
START WITH 300
INCREMENT BY 1;

삽입:

INSERT INTO hr.EMPLOYEES
(EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL, HIRE_DATE, JOB_ID, SALARY, DEPARTMENT_ID)
VALUES
(EMP_SEQ.NEXTVAL, 'Jane', 'Doe', 'JDOE', SYSDATE, 'IT_PROG', 6000, 60);

문제 5

커미션 받는 직원 평균 급여 계산, 평균이 5000 이상일 때만 출력

SELECT AVG(SALARY) AS AVG_SALARY
FROM hr.EMPLOYEES
WHERE COMMISSION_PCT IS NOT NULL
HAVING AVG(SALARY) >= 5000;

주의: HAVING은 GROUP BY가 없어도 가능함(Oracle)

 

문제 6

매니저별 직원 수

SELECT MANAGER_ID, COUNT(*) AS EMP_COUNT
FROM hr.EMPLOYEES
WHERE MANAGER_ID IS NOT NULL
GROUP BY MANAGER_ID;

 

문제 7

부서 평균 급여보다 높은 직원들

SELECT e.FIRST_NAME, e.LAST_NAME, e.SALARY, e.DEPARTMENT_ID
FROM hr.EMPLOYEES e
JOIN (
    SELECT DEPARTMENT_ID, AVG(SALARY) AS AVG_SAL
    FROM hr.EMPLOYEES
    GROUP BY DEPARTMENT_ID
) a
ON e.DEPARTMENT_ID = a.DEPARTMENT_ID
WHERE e.SALARY > a.AVG_SAL;

 

문제 8

입사 연도별 직원 수

SELECT EXTRACT(YEAR FROM HIRE_DATE) AS HIRE_YEAR,
       COUNT(*) AS EMP_COUNT
FROM hr.EMPLOYEES
GROUP BY EXTRACT(YEAR FROM HIRE_DATE)
ORDER BY HIRE_YEAR;

 

문제 9

급여 상위 10% 직원

SELECT FIRST_NAME, LAST_NAME, SALARY
FROM (
    SELECT e.*,
           NTILE(10) OVER (ORDER BY SALARY DESC) AS NT
    FROM hr.EMPLOYEES e
)
WHERE NT = 1;

 

문제 10

부서별 최소, 최대 급여. 단 최대 급여가 10000 이상인 부서만

SELECT DEPARTMENT_ID,
       MIN(SALARY) AS MIN_SAL,
       MAX(SALARY) AS MAX_SAL
FROM hr.EMPLOYEES
GROUP BY DEPARTMENT_ID
HAVING MAX(SALARY) >= 10000;