[MySQL] 1일 1쿼리 (2) : JOIN 활용하기

[MySQL] 1일 1쿼리 (2) : JOIN 활용하기

1. 문제

2. 풀이

---

1. 문제

순서	문제
1	전직원 2000년도의 연봉을 출력하세요(출력 예시 :  id , firstname, lastname,slalary)
2	'd004'  부서의 남자직원의 이름을 출력하세요 (출력예시 : firstname , M , 'd004' )
3	아직 재직중인 사람의 이름과 성 직급 입사일자를 출력해 보자.
4	부서 매니저들의 평균 연봉을 추출해 보자.
5	각 직원의 정보를 확인하기 (직원번호, 생년월일, 이름, 성,부서번호, 부서명, 직업명)
6	1990년 이상 입사자 이름과 급여 부서 직책
7	전직원 2000년도의 연봉을 출력하세요(출력 예시 :  id , firstname, lastname,slalary)
8	'd004'  부서의 남자직원의 이름을 출력하세요 (출력예시 : firstname , M , 'd004' )
9	부서 매니저의 연봉이 100,000 달러 이상인 사람 확인하시오 (출력예시 : 사원번호,입사날짜,연봉)
10	8월 15일에 고용한 직원들에게 태극기를 지급하기로 했다.  사원 번호, 고용 날짜와 last_name을 조회하시오
11	현재 근무중인 매니저들의 사번, 이름, 성별, 생년월일, 최근 급여를 나타내세요
12	1965년생 중 현재 영업팀에서 일하고, 최근 급여가 55000 이하인 사람의  사번, 생년월일, 이름, 성별, 고용일, 최근 급여를 나타내세요
13	퇴사한 기술부서 전체 인원을  출력해주세요
14	현재  재직중인 사람중 입사일이 1991-10-10 인 사람의 부서를찾아주세요
15	아직까지 일하고있는 사람중에 남자가 몇명인지 알아보자.
16	시니어 엔지니어의 월급중 6만달러가 넘는 사람이 몇명인지 알아보자
17	마케팅 부서에 속하는 사람 중 퇴사한 사람들의 명단을 만들어 보자.
18	전체 노동자의 사원 번호와 이름,연봉을 나타내주세요.

 

 

---

2. 풀이

2024.06.07

1. -- 전직원 2000년도의 연봉을 출력하세요(출력 예시 :  id , firstname, lastname,slalary)
select e.emp_no, e.first_name, e.last_name , s.salary
from employees as e
join salaries as s
on e.emp_no = s.emp_no
where s.from_date like '2000%';

2. -- 'd004'  부서의 남자직원의 이름을 출력하세요 (출력예시 : firstname , M , 'd004' )
select e.first_name , e.gender , d.dept_no 
from employees as e
join dept_emp  as d
on e.emp_no = d.emp_no
where e.gender = 'M' and d.dept_no = 'd004' ;

3. -- 아직 재직중인 사람의 이름과 성 직급 입사일자를 출력해 보자.
select em.first_name,em.last_name, tt.title, em.hire_date
from employees as em
join titles as tt
on em.emp_no = tt.emp_no
where tt.to_date = '9999-01-01';

4. -- 부서 매니저들의 평균 연봉을 추출해 보자.
select avg(sa.salary)
from dept_manager as dm
join salaries as sa
on dm.emp_no = sa.emp_no;

5. 각 직원의 정보를 확인하기 (직원번호, 생년월일, 이름, 성,부서번호, 부서명, 직업명)
select 
e.emp_no, -- 직원 번호
e.birth_date,  -- 생년월일
e.first_name,  -- 성
e.last_name,   -- 이름
e.gender, -- 성별
d.dept_no, -- 부서 번호
dp.dept_name, -- 부서 이름
t.title
from employees as e     -- employees 참고
right join dept_emp as d  -- dept_emp department에 대한 직원 번호
on e.emp_no = d.emp_no    -- employees 의 직원번호 == d_emp 의 직원번호
right join departments as dp -- 부서 이름 정보
on d.dept_no = dp.dept_no
right join titles as t
on e.emp_no = t.emp_no;

6. 1990년 이상 입사자 이름과 급여 부서 직책
select concat( e.last_name,' ',e.first_name) as '이름', e.hire_date as '입사날짜' ,s.salary as '급여', d.dept_no as '개발부서', t.title as '직책'
from employees as e
left join salaries as s
on e.emp_no = s.emp_no 
left join dept_emp as d
on e.emp_no = d.emp_no 
left join titles as t 
on e.emp_no = t.emp_no
where e.hire_date >= '1990-01-01' and d.dept_no = 'd005';

7. -- 전직원 2000년도의 연봉을 출력하세요(출력 예시 :  id , firstname, lastname,slalary)
select e.emp_no, e.first_name, e.last_name , s.salary
from employees as e
join salaries as s
on e.emp_no = s.emp_no
where s.from_date like '2000%';

8. -- 'd004'  부서의 남자직원의 이름을 출력하세요 (출력예시 : firstname , M , 'd004' )
select e.first_name , e.gender , d.dept_no 
from employees as e
join dept_emp  as d
on e.emp_no = d.emp_no
where e.gender = 'M' and d.dept_no = 'd004' ;

9. 부서 매니저의 연봉이 100,000 달러 이상인 사람 확인하시오 (출력예시 : 사원번호,입사날짜,연봉)
select d.emp_no, d.from_date, s.salary
from dept_manager as d
join salaries as s
on d.emp_no = s.emp_no
where s.salary > 100000;

10.  8월 15일에 고용한 직원들에게 태극기를 지급하기로 했다. 사원 번호, 고용 날짜와 last_name을 조회하시오
select e.emp_no, d.from_date, e.last_name
from employees as e
left join dept_emp as ddept_empdepartmentsdepartmentsdept_empdept_emp
on e.emp_no = d.emp_no
where d.from_date like '%-08-15';

----------------------------------------

11. 현재 근무중인 매니저들의 사번, 이름, 성별, 생년월일, 최근 급여를 나타내세요
select e.emp_no, e.first_name, e.last_name, e.gender, e.birth_date, s.salary
from employees as e
left join dept_manager as m
on e.emp_no = m.emp_no
left join salaries as s
on e.emp_no = s.emp_no
where m.to_date = '9999-01-01' and s.to_date = '9999-01-01';

12. 1965년생 중 현재 영업팀에서 일하고, 최근 급여가 55000 이하인 사람의 사번, 생년월일, 이름, 성별, 고용일, 최근 급여를 나타내세요
select e.emp_no, e.birth_date, e.first_name, e.last_name, e.gender, e.hire_date, s.salary
from employees as e
left join dept_emp as d
on e.emp_no = d.emp_no
left join salaries as s
on e.emp_no = s.emp_no
where e.birth_date like '1965%' and d.dept_no = 'd007' and s.salary < 55000 and s.to_date = '9999-01-01' 

13. 퇴사한 기술부서 전체 인원을  출력해주세요
select e.dept_no,e.from_date,e.to_date
from dept_emp as e
left join departments as d
on e.dept_no =d.dept_no
where e.dept_no = 'd005' and e.to_date != '9999-01-01';

14. 현재  재직중인 사람중 입사일이 1991-10-10 인 사람의 부서를찾아주세요
select e.dept_no,e.from_date,e.to_date
from dept_emp as e
left join departments as d
on e.dept_no =d.dept_no
where e.from_date = '1991-10-10' and e.to_date = '9999-01-01' ;

15. 아직까지 일하고있는 사람중에 남자가 몇명인지 알아보자
select e.gender,t.to_date
from titles as t
join employees as e
on t.emp_no = e.emp_no
where t.to_date ='9999-01-01' and e.gender = 'M';

16. 시니어 엔지니어의 월급중 6만달러가 넘는 사람이 몇명인지 알아보자
select t.title,s.salary
from titles as t
join salaries as s
on t.emp_no = s.emp_no
where t.title ='Senior Engineer' and s.salary > 60000;

17. 마케팅 부서에 속하는 사람 중 퇴사한 사람들의 명단을 만들어 보자.
select *
from dept_emp as d
left join departments as p
on d.dept_no=p.dept_no
where d.dept_no='d001' and d.to_date!='9999-01-01';

18. 전체 노동자의 사원 번호와 이름,연봉을 나타내주세요.
select*
from employees as e
left join salaries as s
on e.emp_no=s.emp_no;

---

3. 내가 푼 것

use employees;
select*from salaries;
select*from employees;
select*from dept_emp;
select*from departments;
select*from titles;
select*from dept_manager;

-- 문제 1.전직원 2000년도의 연봉을 출력하세요
select e.emp_no,e.first_name,e.last_name,s.salary
from employees as e
join salaries as s
on e.emp_no=s.emp_no
where s.from_date like '2000%';

-- 문제 2. 'd004'  부서의 남자직원의 이름을 출력하세요 
select e.first_name
from employees as e
join dept_emp as d
on e.emp_no=d.emp_no
where d.dept_no='d004';

-- 문제 3. 아직 재직중인 사람의 이름과 성 직급 입사일자를 출력해 보자.
select e.first_name, e.last_name, t.title, e.hire_date
from employees as e
join titles as t
on e.emp_no=t.emp_no
where t.to_date='9999-01-01';

-- 문제 4. 부서 매니저들의 평균 연봉을 추출해보자.
select AVG(s.salary)
from dept_manager as m
join salaries as s
on m.emp_no=s.emp_no;

-- 문제 5. 각 직원들의 정보를 확인하기
select*
from employees as e
left join dept_emp as de
on e.emp_no=de.emp_no
left join departments as d
on de.dept_no=d.dept_no;

-- 문제 6. 1990년 이상 입사자 이름과 급여 부서 직책
select e.first_name,s.salary,d.dept_no,t.title
from employees as e
left join salaries as s
on e.emp_no=s.emp_no
left join titles as t
on e.emp_no=t.emp_no
left join dept_emp as d
on e.emp_no=d.emp_no
where t.from_date>'1990-01-01';

-- 문제 7. 전 직원 2000년도 연봉을 출력해보자.
select e.first_name, s.salary
from employees as e
left join salaries as s
on e.emp_no=s.emp_no
where s.from_date>'2000-01-01' and s.from_date<'2001-01-01';

-- 문제 8. 'd004'  부서의 남자직원의 이름을 출력하세요 
select e.first_name,e.gender,d.dept_no
from employees as e
left join dept_emp as d
on e.emp_no=d.emp_no
where e.gender='M';

-- 문제 9. 부서 매니저의 연봉이 100,000 달러 이상인 사람 확인하시오 (출력예시 : 사원번호,입사날짜,연봉)
select dm.emp_no,dm.from_date,s.salary
from dept_manager as dm
left join salaries as s
on dm.emp_no=s.emp_no
where s.salary>100000;

-- 문제 10. 8월 15일에 고용한 직원들의 사원 번호, 고용 날짜와 last_name을 조회하시오

select e.emp_no,e.hire_date,e.last_name
from employees as e
where e.hire_date like '%-08-15';