[MySQL] 1일 1쿼리 (4): HAVING과 GROUP BY 절

[MySQL] 1일 1쿼리 (4): HAVING과 GROUP BY 절

1. 문제

2. 문제 풀이(답안)

3. 내가 푼 것

---

1. 문제

순서	문제
1	마케팅 부서의 직원들을 입사 순서대로 오름차순 정렬하시오. -- 출력해야 할 것: 직원의 이름, 고용일, 부서
2	부서별로 가장 높은 임금과 평균 임금을 함께 출력하시오. -- 이 때, 평균 임금은 소수점을 제거하고 출력하시오.
3	근무기간이 30년이상인 Staff 직원들중에서 emp_no낮은순서부터 정렬 시, 200명 중에서 가장 오래된 근무년수는?
4	가장 최근 입사한 여직원과 남직원 찾으시오
5	월급이 가장 높은 남직원 여직원을 찾으시오
6	재직중인 사원들 중 평균 연봉이 가장 큰 3명의 first_name, 평균 연봉(소숫점 제거),부서, 근무 년수 을 추출하시오.
7	전직원에게 생일 상여금 지급 하기로 하였다. 월별 지급해야할 인원수를 구하세요(퇴사자 제외 || 출력예시 월, 인원수)
8	부서별 평균급여계산 후 60000보다 높으면 'High' 낮으면 'Low' (부서, 부서평균, 급여수준 ('High' or 'Low')) 부서평균은 내림차순, 소수점 제거 해라
9	각 부서의 이름과 총 인원수를 출력하시오
10	직원들 개인의 입사 이후 현재까지의 평균 연봉을 구해서 80000이 넘는 사람들만 출력하라. (내림차순으로 상위 100명만) -- 출력 예시 (emp_no, last_name, avg)
11	전체 평균 급여를 소수점 제외 도출하고 급여가 평균을 넘는 사원의 이름을 출력하세요.
12	Engineer에서 Senior Engineer로 승진했던 직원의 직원 번호와 first_name last_name을 합친 이름을 출력하시오
13	근무하는 직원이 50000명 이상인 부서와 그 부서의 직원 수를 출력하세요.
14	각 부서별 인원을 체크 하고 재직 인원이 제일 적은 순서로 부서를 세개 출력하시오
15	각 부서의 남자 막내 사원들을 대상으로 워크숍이 예정되어 있습니다. 참석 여부를 조사하기 위해 -- 대상자를 구분하여 부서번호, 사번, 입사일, 성, 이름을 출력하시오.

---

2. 문제 풀이(답안)

1.  가장 최근 입사한 여직원과 남직원 찾으시오
select first_name , max(hire_date)
from employees
group  by gender


2. 근무기간이 30년이상인 Staff 직원들중에서 emp_no낮은순서부터 200명 중에서 가장 오래된 근무년수는?
select title,emp_no ,(year(current_date()) - year(from_date)) as 근무년수 
from titles
where to_date = '9999-01-01' and title = 'Staff'
having 근무년수 >= 30
order by 근무년수 desc
limit 200;

3. 마케팅 부서의 직원들을 입사 순서대로 오름차순 정렬하세요.
-- 출력해야 할 것: 직원의 이름, 고용일, 부서
select e.first_name, e.hire_date, de.dept_name
from employees as e
left join dept_emp as d
on e.emp_no=d.emp_no
left join departments as de
on d.dept_no=de.dept_no
where de.dept_no='d001'
order by e.hire_date desc;

4. 부서별로 가장 높은 임금과 평균 임금을 함께 출력하세요.
-- 이 때, 평균 임금은 소수점을 제거
select d.dept_name, s.salary, round(avg(s.salary)),max(s.salary)
from dept_emp as e
left join departments as d
on e.dept_no=d.dept_no
left join salaries as s
on e.emp_no=s.emp_no
group by d.dept_no;

5. 월급이 가장 높은 남직원 여직원을 찾으시오
select e.first_name,e.emp_no , max(s.salary) 
from employees as e
 left join salaries as s
on  e.emp_no  = s.emp_no
group  by gender;

6. -- 재직중인 사원들 중 평균 연봉이 가장 큰 3명의 first_name, 평균 연봉(소숫점 제거),부서, 근무 년수 을 추출하시오.
select em.first_name, round((sa.salary)) as '평균 연봉', dep.dept_name, de.from_date, year(now()) - year(sa.from_date) + 1 as '근무 년수'
from dept_emp as de
join salaries as sa
on de.emp_no = sa.emp_no
join employees as em
on de.emp_no = em.emp_no
join departments as dep
on de.dept_no = dep.dept_no
where de.to_date = '9999-01-01'
group by dep.dept_no
order by round((sa.salary)) desc
limit 3;

7. -- 전직원에게 생일 상여금 지급 하기로 하였다. 월별 지급해야할 인원수를 구하세요(퇴사자 제외 || 출력예시 월, 인원수)
select  month(birth_date) as m, count(*)
from employees as em
join dept_emp as dem
on em.emp_no = dem.emp_no
where dem.to_date != '9999-01-01'
group by month(birth_date)
order by m asc;

8. -- 부서별 평균급여계산 후 60000보다 높으면 'High' 낮으면 'Low' (부서, 부서평균, 급여수준 ('High' or 'Low')) 부서평균은 내림차순, 소수점 제거 해라
select concat('[ ', d.dept_name, ' ]') as '부서', round(avg(salary)) as '부서평균' , case when avg(salary) > 60000 then 'High' else 'Low' end as '급여수준'
from dept_emp as de
left join salaries as s
on de.emp_no = s.emp_no
left join departments as d
on de.dept_no = d.dept_no
group by de.dept_no
order by avg(salary) desc;

9. 각 부서의 이름과 총 인원수를 출력하시오
select d.dept_name, count(*) as 인원수
from employees as e
join dept_emp as de
join departments as d
on e.emp_no = de.emp_no and d.dept_no = de.dept_no
group by dept_name;

10. 직원들 개인의 입사 이후 현재까지의 평균 연봉을 구해서 80000이 넘는 사람들만 출력하라. (내림차순으로 상위 100명만)
-- 출력 예시 (emp_no, last_name, avg)
select e.emp_no, last_name, round(avg(salary), 0) as avg
from salaries as s
join employees as e on s.emp_no = e.emp_no
group by s.emp_no
having avg > 80000
order by avg desc
limit 100;

11. 전체 평균 급여를 소수점 제외 도출하고 급여가 평균을 넘는 사원의 이름을 출력하세요.
select e.first_name, e.last_name, round(avg(salary), 0) as '평균급여'
from salaries as s
join employees as e
on s.emp_no = e.emp_no
group by e.emp_no
having avg(salary) >= (select avg(salary) from salaries);

12.  Engineer에서 Senior Engineer로 승진했던 직원의 직원 번호와 first_name last_name을 합친 이름을 출력하시오
select t.emp_no, concat(e.first_name,' ',last_name) as 'name'
from titles as t
join employees as e
on t.emp_no = e.emp_no
where (t.title = 'Engineer' or t.title = 'Senior Engineer')
group by t.emp_no
having count(t.emp_no) >= 2;

13. 근무하는 직원이 50000명 이상인 부서와 그 부서의 직원 수를 출력하세요.
select d.dept_no, d.dept_name as 부서, count(de.emp_no) as 직원수
from departments as d
join dept_emp as de
on d.dept_no = de.dept_no
group by d.dept_no
having 직원수 >= 50000;

14. 각 부서별 인원을 체크 하고 재직 인원이 제일 적은 순서로 부서를 세개 출력하시오
select par.dept_name, count(dep.dept_no) as 직원수
from dept_emp as dep
join departments as par
on dep.dept_no = par.dept_no
where dep.to_date = '9999-01-01'
group by par.dept_no
order by 직원수 asc
limit 3;


15.  각 부서의 남자 막내 사원들을 대상으로 워크숍이 예정되어 있습니다. 참석 여부를 조사하기 위해
-- 대상자를 구분하여 부서번호, 사번, 입사일, 성, 이름을 출력하시오.
select d.dept_no as 부서번호, e.emp_no as 사번, max(e.hire_date) as 입사일, e.first_name as 성, e.last_name as 이름
from employees as e
join dept_emp as d
on e.emp_no = d.emp_no
where e.gender = 'm'
group by d.dept_no
order by d.dept_no asc;

---

3. 내가 푼 것