[MySQL] 1일 1쿼리 (5) : Self join 및 기존 개념 복습

[MySQL] 1일 1쿼리 (5) : Self join 및 기존 개념 복습

1. 문제

2. 문제 풀이

---

1. 문제

순서	문제
1	재직중이고 현재 연봉이 100000 이상인 직원수가 가장 많은 3개 부서를 출력하세요 -- 부서, 고소득직원수
2	부서중 직원수가 가장 많은 부서 하나만 찾아주세요(부서이름과, 직원 수 나오게)
3	재직 중인 사원들 중, 가장 오래 재직중인 직원 5명의 이름, 직함, 연봉을 출력하시오.
4	Development 부서중 년차가 가장 높은 사람 5 명 출력해 퇴시키시오(재직중인인원중)
5	현재 재직중인 사람들의 부서 이름,소수점없는 평균연봉을 구하시오
6	부서 이동이 있었던 사원의 사번, 부서번호, 성, 이름, 이동 전,후 부서의 소속 기간을 출력하시오.
7	생일에 입사한 사람을 모두 조회하라 -- 출력예시 employees.*
8	낮은 번호대로 각 부서의 역대 매니저와 현 매니저의 수를 구하고 그 매니저들의 평균 급여를 구해라. -- 출력 예시 - 부서 번호, 부서 이름, 인원수, 평균 급여
9	현직자 중 업종을 출력하고 업종별 임금 평균과 최대 임금을 출력하시오.
10	각 직원(중복x)마다 부서와 직급을 구하라 (단, 오름차순,내림차순은 무시한다)
11	재직중인 senior Engineer 들의 부서와 first_name 그리고 가장 최근 연봉을 출력하세요
12	직급이 staff 인 직원의 평균 연봉을 구하세요
13	staff들 중 first_name이 Georgi인 동명이인을 찾고 나이가 많은 사람 순으로 정렬하시오. (출력 : title, first_name, last_name, brith_date)
14	연봉이 10만 달러 이상인 사람의 사번, 이름, 성별, 직급을 출력하시오

---

2. 문제 풀이(답안)

-- 문제 1. 재직중이고 현재 연봉이 100000 이상인 직원수가 가장 많은 3개 부서를 출력하세요
-- 부서, 고소득직원수
select d.dept_name, count(*) 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
where de.to_date='9999-01-01' and s.salary>100000
group by d.dept_name
order by count(*) desc
limit 3;

-- 문제 2.부서중 직원수가 가장 많은 부서 하나만 찾아주세요(부서이름과, 직원 수 나오게)
select d.dept_name,count(*)
from dept_emp as de
left join departments as d
on de.dept_no=d.dept_no
group by d.dept_name
order by count(*) desc
limit 1;

-- 문제 3: 재직 중인 사원들 중, 가장 오래 재직중인 직원 5명의 이름, 직함, 연봉을 출력하시오.
select e.first_name,t.title,s.salary,e.hire_date
from employees as e
left join titles as t
on e.emp_no=t.emp_no
join salaries as s
on e.emp_no=s.emp_no
group by e.first_name
order by e.hire_date
limit 0,5;


-- 문제 4. Development 부서중 년차가 가장  5 명 출력해 퇴시키시오(재직 중 인원중)
select e.first_name, year(now()) - year(de.from_date) as 년차, d.dept_name
from dept_emp as de
left join departments as d
on de.dept_no=d.dept_no
left join employees as e
on de.emp_no=e.emp_no
where de.dept_no='d005' and de.to_date='9999-01-01'
order by 년차 desc
limit 5;

-- 문제 5.-- 현재 재직중인 사람들의 부서 이름,소수점없는 평균연봉을 구하시오
select e.first_name,d.dept_name,round(avg(s.salary)) as 연봉
from employees as e
left join dept_emp as de
on e.emp_no=de.emp_no
join salaries as s
on e.emp_no=s.emp_no
join departments as d
on de.dept_no=d.dept_no
where de.to_date='9999-01-01'
group by d.first_name
order by 연봉 desc;

-- 문제 6. 부서 이동이 있었던 사원의 사번, 부서번호, 성, 이름, 이동 전,후 부서의 소속 기간을 출력하시오.
select d.emp_no, d.dept_no, e.first_name, e.last_name, d.from_date, d.to_date
from dept_emp as d
join employees as e
on d.emp_no = e.emp_no 
where d.emp_no in(
    select emp_no
    from dept_emp
    group by emp_no
    having count(emp_no) > 1
) 
order by emp_no;

-- 문제 7. 생일에 입사한 사람을 모두 조회하라
select *
from employees as e
where month(e.birth_date)=month(e.hire_date)
and day(e.birth_date)=day(e.hire_date);

-- 문제 8. 낮은 번호대로 각 부서의 역대 매니저와 현 매니저의 수를 구하고 그 매니저들의 평균 급여를 구해라.
-- 출력 예시 - 부서 번호, 부서 이름, 인원수, 평균 급여
select d.dept_no,d.dept_name, count(distinct dm.to_date='9999-01-01') as '현 매니저 수',
avg(s.salary) as '평균 연봉'
from dept_manager as dm
left join employees as e
on dm.emp_no=e.emp_no
left join departments as d
on dm.dept_no=d.dept_no
left join salaries as s
on dm.emp_no=s.emp_no
where dm.emp_no=e.emp_no
group by dm.dept_no;

-- 문제 9. 현직자 중 업종을 출력하고 업종별 임금 평균과 최대 임금을 출력하시오. 
select t.title as '업종', floor(avg(s.salary)) as '업종별 평균', max(s.salary) as '업종최고급여'
from titles as t
left join employees as e
on e.emp_no = t.emp_no
left join salaries as s
on t.emp_no = s.emp_no
where s.to_date >= 9999-01-01  
group by title;

-- 문제 10. 각 직원(중복x)마다 부서와 직급을 구하라 (단, 오름차순,내림차순은 무시한다)
select departments.dept_name as 부서, concat(employees.first_name, " ",employees.last_name) as 이름,
titles.title as 직급 from departments
join dept_emp
on departments.dept_no = dept_emp.dept_no
join employees
on dept_emp.emp_no = employees.emp_no
join titles
on employees.emp_no = titles.emp_no
group by 이름;

-- 문제 11. 재직중인 senior Engineer 들의 부서와 first_name 그리고 가장 최근 연봉을 출력하세요
select ti.title, de.dept_name, em.first_name, max(sa.salary)
from dept_emp as dm
join departments as de
on dm.dept_no = de.dept_no
join titles as ti
on dm.emp_no = ti.emp_no
join employees as em
on em.emp_no = dm.emp_no
join salaries as sa
on dm.emp_no = sa.emp_no
where dm.to_date = '9999-01-01' and ti.title = 'senior Engineer'
group by dm.emp_no;

--문제 12. 직급이 staff 인 직원의 평균 연봉을 구하세요
select avg(sa.salary)
from salaries as sa
join titles as ti
on sa.emp_no = ti.emp_no
where ti.title = 'staff';

-- 문제13. staff들 중 first_name이 Georgi인 동명이인을 찾고 나이가 많은 사람 순으로 정렬하시오. (출력 : title, first_name, last_name, brith_date)
select t.title, e.first_name, e.last_name, e.birth_date
from employees as e
join titles as t
on e.emp_no = t.emp_no
where e.first_name = 'Georgi' and t.title ='Staff'
order by e.birth_date;

-- 문제14. 연봉이 10만 달러 이상인 사람의 사번, 이름, 성별, 직급을 출력하시오
select e.emp_no, concat(first_name, last_name) as 이름, e.gender, s.salary, t.title
from employees as e
left join titles as t
on e.emp_no = t.emp_no
join salaries as s
on t.emp_no = s.emp_no
group by s.salary
having s.salary > 100000;