이번 시간에는 오라클(Oracle) 데이터베이스 환경에서 주어진 문제, **"사원 테이블에서 업무별 최고 급여를 받는 사원의 이름, 업무, 급여를 출력하는 SQL 문장을 작성하시오."**에 대한 풀이 과정을 단계별로 상세하게 설명드리겠습니다. 데이터 분석의 첫걸음이자 핵심인 SQL 쿼리 작성 능력을 향상시키는 데 도움이 될 것입니다.
📝 문제 분석: 무엇을 찾아야 할까요?
문제를 다시 한번 살펴보면, 우리는 사원 테이블에서 다음 세 가지 정보를 추출해야 합니다.
- 사원의 이름
- 사원의 업무
- 해당 업무에서 가장 높은 급여를 받는 사원의 급여
핵심은 "업무별" 최고 급여를 찾는다는 점입니다. 즉, 각기 다른 업무를 수행하는 사원 그룹 내에서 최고 연봉자를 식별해야 합니다.
🔑 문제 해결 전략: 서브쿼리와 조건절 활용
이러한 문제를 해결하기 위한 가장 효과적인 전략은 **서브쿼리(Subquery)**를 활용하는 것입니다. 서브쿼리를 통해 각 업무별 최고 급여를 먼저 계산하고, 이를 메인 쿼리의 조건절에서 활용하여 해당 급여를 받는 사원의 정보를 필터링하는 방식입니다.
💻 단계별 SQL 쿼리 작성 및 설명
이제 오라클 SQL 문장을 단계별로 작성하고 각 부분에 대한 자세한 설명을 덧붙이겠습니다.
1단계: 각 업무별 최고 급여를 찾는 서브쿼리 작성
가장 먼저, 사원 테이블에서 각 업무별로 가장 높은 급여를 찾는 서브쿼리를 작성합니다. 오라클의 MAX() 집계 함수와 GROUP BY 절을 활용하면 이를 간단하게 구현할 수 있습니다.
SELECT
job_title,
MAX(salary) AS max_salary
FROM
employees
GROUP BY
job_title;
- SELECT job_title, MAX(salary) AS max_salary: 이 부분은 우리가 추출하고자 하는 컬럼을 명시합니다. 각 업무(job_title)와 해당 업무에서 가장 높은 급여(MAX(salary))를 선택하고, 최고 급여 컬럼에 max_salary라는 별칭을 부여하여 이후 메인 쿼리에서 쉽게 참조할 수 있도록 합니다.
- FROM employees: 데이터를 가져올 테이블로 employees (사원 테이블)를 지정합니다. 실제 환경에서는 테이블 이름이 다를 수 있으니 주의해야 합니다.
- GROUP BY job_title: 이 구문은 employees 테이블의 행들을 job_title 컬럼의 값에 따라 그룹화합니다. 즉, 같은 업무를 가진 사원들끼리 하나의 그룹을 형성하게 됩니다. MAX() 함수는 각 그룹 내에서 salary 컬럼의 최댓값을 계산합니다.
2단계: 메인 쿼리 작성 및 서브쿼리 연동
이제 서브쿼리의 결과를 활용하여 최종적으로 원하는 정보를 출력하는 메인 쿼리를 작성합니다. 메인 쿼리에서는 사원 테이블에서 사원의 이름, 업무, 급여를 선택하고, WHERE 절을 사용하여 서브쿼리의 결과와 일치하는 사원만을 필터링합니다.
SELECT
e.emp_name,
e.job_title,
e.salary
FROM
employees e
WHERE
(e.job_title, e.salary) IN (SELECT job_title, MAX(salary) FROM employees GROUP BY job_title);
- SELECT e.emp_name, e.job_title, e.salary: 최종적으로 출력할 컬럼을 선택합니다. e는 employees 테이블에 부여한 별칭(alias)으로, 테이블 이름을 간결하게 사용할 수 있도록 합니다.
- FROM employees e: 데이터를 가져올 기본 테이블로 employees 테이블을 지정하고, e라는 별칭을 부여합니다.
- WHERE (e.job_title, e.salary) IN (...): 이 WHERE 절이 핵심적인 필터링 역할을 수행합니다.
- (e.job_title, e.salary): 메인 쿼리의 각 행에서 job_title과 salary 컬럼의 값을 묶어 하나의 쌍으로 만듭니다.
- IN (SELECT job_title, MAX(salary) FROM employees GROUP BY job_title): 서브쿼리의 결과를 활용하여 조건을 비교합니다. 서브쿼리에서 반환된 각 (업무, 최고 급여) 쌍과 메인 쿼리의 각 사원의 (업무, 급여) 쌍을 비교하여 일치하는 경우에만 해당 사원의 정보를 결과에 포함시킵니다.
🎯 최종 SQL 쿼리 (오라클 기준)
지금까지 설명한 내용을 종합하여 완성된 오라클 SQL 쿼리는 다음과 같습니다.
SELECT
e.emp_name,
e.job_title,
e.salary
FROM
employees e
WHERE
(e.job_title, e.salary) IN (SELECT job_title, MAX(salary) FROM employees GROUP BY job_title);
⚙️ 쿼리 실행 결과 예시
만약 employees 테이블에 다음과 같은 데이터가 있다고 가정해 보겠습니다.
| 김철수 | Sales | 3000 |
| 이영희 | Manager | 5000 |
| 박지성 | Sales | 3500 |
| 정수빈 | Analyst | 4000 |
| 최미나 | Manager | 5500 |
| 강동우 | Analyst | 4500 |
위 쿼리를 실행하면 다음과 같은 결과를 얻을 수 있습니다.
| 박지성 | Sales | 3500 |
| 최미나 | Manager | 5500 |
| 강동우 | Analyst | 4500 |
각 업무(Sales, Manager, Analyst) 별로 가장 높은 급여를 받는 사원의 이름, 업무, 급여 정보가 정확하게 출력된 것을 확인할 수 있습니다.
✨ 추가적인 고려 사항
- 동일한 최고 급여를 받는 사원이 여러 명일 경우: 위 쿼리는 각 업무별 최고 급여를 받는 사원 모두를 출력합니다. 만약 특정 조건(예: 입사일이 가장 빠른 사원)에 따라 단 한 명의 최고 급여자를 선택해야 한다면 윈도우 함수(ROW_NUMBER(), RANK(), DENSE_RANK())와 PARTITION BY 절을 함께 활용하는 более 복잡한 쿼리가 필요할 수 있습니다.
- 테이블 및 컬럼명: 실제 데이터베이스 환경에서는 테이블 이름(employees)과 컬럼 이름(emp_name, job_title, salary)이 다를 수 있으므로, 실제 스키마에 맞춰 쿼리를 수정해야 합니다.
📚 결론
지금까지 오라클 SQL을 사용하여 사원 테이블에서 업무별 최고 급여를 받는 사원의 정보를 출력하는 쿼리 작성 과정을 상세하게 살펴보았습니다. 서브쿼리와 조건절을 효과적으로 활용하면 복잡한 데이터 분석 요구 사항도 간결하고 효율적인 SQL 문장으로 해결할 수 있습니다. 꾸준한 학습과 실습을 통해 SQL 활용 능력을 더욱 향상시키시기를 바랍니다.