LV 2
1. 조건에 맞는 사원 정보 조회하기
HR_DEPARTMENT, HR_EMPLOYEES, HR_GRADE 테이블에서 2022년도 한해 평가 점수가 가장 높은 사원 정보를 조회하려 합니다. 2022년도 평가 점수가 가장 높은 사원들의 점수, 사번, 성명, 직책, 이메일을 조회하는 SQL문을 작성. 2022년도의 평가 점수는 상,하반기 점수의 합을 의미하고, 평가 점수를 나타내는 컬럼의 이름은 SCORE로 작성.
1. WITH 사용
with sum_score as (
select g.emp_no,
sum(g.score) as score
from hr_grade g
where g.year = 2022
group by g.emp_no
)
select s.score,
e.emp_no,
e.emp_name,
e.position,
e.email
from sum_score s join hr_employees e on s.emp_no = e.emp_no
where s.score = (select max(score) from sum_score)
오답노트
1. 서브쿼리에 최고 점수 받은 사원만 필터링을 함
★ 2. with sum_score as(...)
- 임시 테이블(Common Table Expression, CTE) 생성 시작
- 복잡한 쿼리를 읽기 쉽고 재사용성 있게 만들기 위해 사용
- 여기서는 2022년 한 해의 점수를 합산한 결과를 임시 테이블처럼 만들어주고 그 결과 바탕으로 최고 점수 받은 사원을 차기 위해 사용
- 기본 구조
별명: 임시 테이블의 이름(CTE 이름)
괄호 안에 원하는 서브쿼리를 작성
이후 메인 쿼리에서 이 별명을 테이블처럼 사용
WITH 별명 AS (
-- 서브쿼리(임시 테이블)
SELECT ...
FROM ...
WHERE ...
)
SELECT ...
FROM 별명
WHERE ...;
3. SELECT g.EMP_NO, SUM(g.SCORE) AS SCORE
- 사원별로 2022년 상/하반기 점수가 따로 되어 있어서 이를 합산함
4. 평가 테이블에서 데이터 가져옴
5. 중요 GROUP BY는 집계 기준이 되는 컬럼을 지정함. 근데 MAX(컬럼) 자체가 집계 함수이므로, 올 수 없음.
* 집계 함수 : count, sum, avg, max, min, group_concat(그룹내 문자열 결함), array_agg(그룹 내 값을 배열로 반환)
** 예) SELECT GROUP_CONCAT(이름) FROM 테이블 GROUP BY 부서 // 예) SELECT ARRAY_AGG(이름) FROM 테이블 GROUP BY 부서
2. limit 써서 간단하게
select sum(score) as score,
e.emp_no,
emp_name,
position,
email
from hr_employees e join hr_grade g on e.emp_no = g.emp_no
group by g.emp_no
order by score desc
limit 1오답노트
order by 를 해서 맨 앞의 값 뽑아오기
3. having 써서
select sum(g.score) as score,
e.emp_no,
e.emp_name,
e.position,
e.email
from hr_employees e join hr_grade g on e.emp_no = g.emp_no
where g.year = 2022
group by e.emp_no
having sum(g.score) =
(
select max(sum_score)
from (
select sum(score) as sum_score
from hr_grade
where year = 2022
group by emp_no) a
)오답노트
★ HAVING 절은 GROUP BY로 그룹화된 결과에 조건을 걸 때 사용
즉, 집계함수(예: SUM, COUNT, AVG, MAX, MIN 등)를 이용한 결과에 대해 필터링이 필요할 때
WHERE: 그룹화(집계) 전에 개별 행을 필터링할 때 사용
(예: 특정 부서의 사원만 먼저 추리기)
HAVING: 그룹화(집계) 후, 집계된 결과(그룹)에 조건을 걸 때 사용
(예: 부서별 평균 급여가 3000 이상인 부서만 보기)
★ HAVING 안에 서브쿼리 분석
안쪽 서브쿼리 : 2022년 각 사원의 상하반기 점수 합계(sum_score)를 구함
SELECT SUM(score) AS sum_score
FROM hr_grade
WHERE year = 2022
GROUP BY emp_no
바깥쪽 서브쿼리: sum_score 중 최고 점수를 구함
SELECT MAX(sum_score)
FROM ( ... ) a
having : 점수 합계가 최고 점수와 같은 사람만 필터링
HAVING SUM(g.score) = (최고 점수)