샘플 데이터 활용 문제 - 17

핵심 패턴 복습

현재 유효한 데이터 → to_date = '9999-01-01'

---

JOIN 종류 공식 명칭 정리

공식 명칭	생략형	설명
INNER JOIN	JOIN	양쪽 모두 매칭되는 행만
LEFT OUTER JOIN	LEFT JOIN	왼쪽 전체 + 오른쪽 NULL 허용
RIGHT OUTER JOIN	RIGHT JOIN	오른쪽 전체 + 왼쪽 NULL 허용
FULL OUTER JOIN	FULL JOIN	양쪽 전체 (MySQL 미지원, PostgreSQL 지원)
CROSS JOIN	-	모든 조합 (카테시안 곱)

 

---

문제 풀이

 

사용할 DB

use employees;
show tables;

desc employees;
desc salaries;
desc titles;
desc departments;
desc dept_emp;
desc dept_manager;

 

---

1번 문제 - 현재 유효한 연봉 기준으로 연봉이 높은 순서대로 출력하세요. (emp_no, fisrt_name, last_name, salary)

select
	e.emp_no,
    e.first_name,
    e.last_name,
    s.salary
from employees e
inner join salaries s on e.emp_no = s.emp_no
where s.to_date = '9999-01-01'
order by s.salary desc;
-- join 후 where 필터 사용할 수 있음 (사후 필터)

---

2번 문제 : 사번, 이름(first_name), 성(last_name), 현재 직급(title), 현재 연봉(salary)을 한 화면에 보여주세요

-- 문제 2
select
	e.emp_no,
    e.first_name,
    e.last_name,
    t.title,
    s.salary
from employees e
join titles t on e.emp_no = t.emp_no and t.to_date = '9999-01-01'
join salaries s on e.emp_no = s.emp_no and s.to_date = '9999-01-01';

-- join 문법에서 on절 뒤에 and 를 붙여 조인 조건과 필터를 한꺼번에 걸 수 있음.
-- (사전 필터), 인라인 필터
-- 공식 명칭: 조인 조건

select
	e.emp_no,
    e.first_name,
    e.last_name,
    t.title,
    s.salary
from employees e
join titles t on e.emp_no = t.emp_no 
join salaries s on e.emp_no = s.emp_no 
where t.to_date = '9999-01-01'
	and s.to_date = '9999-01-01';
    
-- inner hoin 에서는 사전 필터 결과 값과 사후 필터 결과 값 동일하다

 1.

 

2.

---

3번 문제 : LEFT JOIN을 사용해서 현재 부서 관리자만 출력하세요. 출력: 사번, 이름(first_name), 부서명, 시작일

-- 문제 3
-- employees e
-- departments d
-- left join을 사용해서 현재 부서 관리자만 출력
-- 출력 : 사번, 이름(first_name), 부서명, 시작일

-- left join사전 필터링을 사용
select
	e.emp_no,
    e.first_name,
    d.dept_name,
    dm.from_date
from employees e
left join dept_manager dm on e.emp_no = dm.emp_no and dm.to_date = '9999-01-01'
inner join departments d on dm.dept_no = d.dept_no;

-- 사후 필터로 수정
select
	e.emp_no,
    e.first_name,
	d.dept_name,
	dm.from_date
from employees e
left join dept_manager dm on e.emp_no = dm.emp_no and dm.to_date = '9999-01-01'
inner join departments d on dm.dept_no = d.dept_no
where dm.to_date = '9999-01-01';

-- 아우터 조인을 사용할 때 (left join) 사후 필터로 적용을 하게 되면
-- left join에 의미가 없어지게 된다 즉 inner join 사용한 거와 같아지게 된다
-- 실무에서 90퍼 정도 실수를 하는 부분이 된다

-- 즉 아우터 필터를 적용할 때는 사후필터 (x) 사전 필터로 사용해서 아우터 조인에 특성을 살린다
-- left join이 조건이 맞지 않는 데이터 까지 뽑아오는 건데 where절을 걸어버리면 조건에 안맞는
-- 데이터는 날려버리기 때문에 inner join이랑 같아지므로 사전필터를 써서 데이터를 들고오는게 맞다

사후필터 수정 전

 

수정 후

 

 

---

4번 문제 :  성별이 여자이고 고용일(hire_date)이 1991년 이후인 직원을 날짜 순서대로 출력하세요.

 

SELECT *
FROM employees
WHERE gender = 'F'
  AND hire_date >= '1991-01-01'
ORDER BY hire_date ASC;

 

---

5번 문제 : 직급이 'Senior Engineer'이고 이름(first_name)이 'Ram'으로 시작하고 현재 연봉이 100,000 이상인 직원의 [사번, 이름, 연봉, 직급]을 조회하세요.

-- 5 - Senior Engineer 중 고연봉자
select 
	e.emp_no as 사번,
    e.first_name as 이름,
    s.salary as 연봉,
    t.title as 직급
from employees e
inner join titles t on e.emp_no = t.emp_no
inner join salaries s on e.emp_no = s.emp_no
where t.title = 'Senior Engineer' 
and e.first_name like 'Ram%'
and s.salary >= '100000';

---

실전문제

 

6번 문제 : first_name 이 'S'로 시작하고 직급이 'Senior Engineer'이고 직급 종료일이 '1990-01-01' 이하인 직원의 [사번, 이름, 직급, 시작일, 종료일]을 조회하세요.

--  6 - 특정 조건의 Senior Engineer
select 
	e.emp_no as 사번,
    e.first_name as 이름,
    t.title as 직급,
    t.from_date as 시작일,
    t.to_date as 종료일
from employees e
inner join titles t on e.emp_no = t.emp_no
where e.first_name like 'S%' and t.title = 'Senior Engineer'
and t.to_date <= '1990-01-01';

---

7번 문제 : 1960년대에 태어난 여성 직원을 조회하세요. (BETWEEN 사용)

-- 7. 여성 직원 중 1991년 이후 입사자
select *
from employees 
where birth_date between '1960-01-01' 
and '1969-12-31' and gender = 'F';

---

8번 문제 : 성(last_name)이 'J'로 시작하는 직원의 [사번, 이름, 성, 현재 연봉]을 출력하세요.

--  8 - 성이 J 로 시작하는 직원
select
	e.emp_no as 사번,
    e.first_name as 이름,
    e.last_name as 성,
    s.salary as 현재연봉
from employees e
inner join salaries s on e.emp_no = s.emp_no
where e.last_name like 'J%';

---

9번 문제 : 사원번호, 성별, 현재 연봉을 한글 컬럼명으로 출력하되 사원번호가 10020 이하인 직원만 사원번호 오름차순으로 출력하세요.

--  9 - 컬럼명 한글로 변경
select
	e.emp_no as 사번,
    e.gender as 성별,
    s.salary as 현재연봉
from employees e
inner join salaries s on e.emp_no = s.emp_no
where s.to_date = '9999-01-01'
  and e.emp_no <= '10020'
order by e.emp_no asc;

---

10번 문제 : 모든 현재 직원(dept_emp.to_date='9999-01-01')의 [사번, 이름, 부서명]을 조회하세요.

-- 10 - 현재 직원의 부서 소속
select 
	e.emp_no as 사번,
	e.first_name as 이름,
    dm.dept_name as 부서명
from employees e
left join dept_emp de on e.emp_no = de.emp_no 
left join departments dm on de.dept_no = dm.dept_no
where de.to_date = '9999-01-01';