728x90
반응형
[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. 내가 푼 것
728x90
반응형
'MySQL > 1일 1쿼리' 카테고리의 다른 글
[MySQL] 1일 1쿼리 (6) : 서브 쿼리 및 기본 개념 활용 (0) | 2024.06.12 |
---|---|
[MySQL] 1일 1쿼리 (5) : Self join 및 기존 개념 복습 (0) | 2024.06.11 |
[MySQL] 1일 1쿼리 (3) : 함수 응용하기 (0) | 2024.06.07 |
[MySQL] 1일 1쿼리 (2) : JOIN 활용하기 (0) | 2024.06.05 |
[MySQL] 1일 1쿼리 (1) : 영화 평점표 만들기 (0) | 2024.06.04 |