[Database] Ch05.SubGroup으로 데이터 분류 - 문제 풀이

[Database] Ch05.SubGroup으로 데이터 분류 - 문제 풀이

Intro

직접 풀어봤으며, oracle sql developer 의 질의 결과와 직접 스프레드 시트에 값을 샘플링하여 비교했습니다. SQL 정석 문제입니다.

 

5.2 Group By절 - 예제 5

1. 각 부서(dept_id)별 평균 급여를 계산해서 보여주시오. 

sql

닫기 줄바꿈

select dept_id, avg(salary)
from s_emp
group by dept_id;
--order by dept_id;

2. 각 부서(dept_id)별로 직책이 사원인 직원들의 평균 급여를 계산해서 보여주시오. 

sql

닫기 줄바꿈

select dept_id as "부서", avg(salary) as "평균 급여"
from s_emp
where title = '사원'
group by dept_id;

 5.3 select 절에서의 group함수의 잘못된 사용

1. 각 지역(region_id)별로 몇 개의 부서가 있는지 나타내시오.

sql

닫기 줄바꿈

select region_id, count(id)  -- 3
from s_dept
group by region_id;

생각 정리

--select count(*) -- 1 그룹함수 안의 컬럼은 select문에 작성 가능. 
--select region_id, count(region_id)  -- 2 테이블 설계 상 (region_id, id(dept)) 쌍의 값이 중복될수없기 때문에 2처럼 써도되지만, 의미상 모호함.  

 

2.각 부서별로 평균 급여를 구하되, 평균 급여가 2000이상인 부서만 나타내시오.

sql

닫기 줄바꿈

-- 부서별 평균 급여를 먼저 구해야함. 그리고 그룹에 대한 조건임having 

select dept_id as "평균 급여 2000이상인 부서",avg(salary)
from s_emp
group by dept_id
having avg(salary) > 2000
-- order by dept_id;

5.5 HAVING 절

[예제]  직책별로 인원이 2명이상일 때, 직책, 평균 연봉, 인원수 출력

sql

닫기 줄바꿈

select title as "직책", avg(salary) as "평균 연봉", count(*) as "인원수"
from s_emp
group by title
having count (*) > 2;

HAVING 절 예제7

1. 각 직책별로 급여의 총합을 구하되, 직책이 부장인 사람을 제외하시오. 
단, 급여 총합이 8000(만원) 이상인 직책만 나타내며, 급여 총합에 대한 오름차순으로 정렬하시오. 

sql

닫기 줄바꿈

select title, sum(salary) as "급여 총합"-- 직책은 group by의 조건 컬럼이므로, select에 올 수 있음. 
from s_emp
where title <> '부장' -- 먼저 실행되니까 부장 먼저 제거. 
group by title
having sum(salary) >= 8000
order by sum(salary); -- order by에 표현식이 들어갈수있음

2. 각 부서별로 직책이 사원인 직원들에 대해서만 평균 급여를 구하시오.

sql

닫기 줄바꿈

select dept_id, avg(salary)
from s_emp
where title = '사원'
group by dept_id ;
--order by dept_id ;

 

 

5.6 Group을 SubGroup으로 세분화하기

1. 각 부서내에서 각 직책별로 몇 명의 인원이 있는지를 나타내시오.

sql

닫기 줄바꿈

-- 부서안의 직책. 먼저 부서가 그룹 기준(if문조건식)온다음에, 직책이 와야함. 
select dept_id, title, count(title)
from s_emp
group by dept_id, title;
--order by dept_id, title;

 

 

 2. 각 부서내에서 몇 명의 직원이 근무하는지를 나타내시오.

sql

닫기 줄바꿈

select dept_id, count(id) as "인원수"
from s_emp
group by dept_id
order by dept_id;

 

3. 각 부서별로 급여의 최솟값과 최댓값을 나타내시오.

단, 최솟값과 최댓값이 같은 부서는 출력하지 마시오. 

sql

닫기 줄바꿈

-- 그룹에 대한 조건식 최솟값과 최댓값이 같은 경우 -> HAVING 
select dept_id, min(salary) as "급여 최솟값", max(salary) as "급여 최댓값"
from s_emp
group by dept_id -- 부서별 그룹화  -- 부속질의문 같은데
having min(salary) <> max(salary)
order by dept_id; -- 부서를 제외하는 방법. 같지 않은 경우면 출력하게 하면 됨.