employees database

-- 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%';
-- 'd004'  부서의 남자직원의 이름을 출력하세요 (출력예시 : firstname , M , 'd004' )
select e.emp_no, e.first_name, e.last_name
from employees as e
join dept_emp as demp on e.emp_no = demp.emp_no
join departments as dept on demp.dept_no = dept.dept_no
where dept.dept_no = 'd004' and e.gender = 'M';

-- 2조 문제
-- 각 직원의 정보를 확인하기 (직원번호, 생년월일, 이름, 성,부서번호, 부서명, 직책명)
select e.emp_no, e.birth_date, e.first_name, e.last_name,dept.dept_no,dept.dept_name,t.title
from employees as e
join titles as t on e.emp_no = t.emp_no
join dept_emp as demp on t.emp_no = demp.emp_no
join departments as dept on demp.dept_no = dept.dept_no;
-- 1990년 이상 입사자 이름과 급여 부서 직책
select e.emp_no, e.first_name, e.last_name,dept.dept_no,dept.dept_name,t.title,MAX(s.salary), demp.from_date
from employees as e
join titles as t on e.emp_no = t.emp_no
join salaries as s on t.emp_no = s.emp_no
join dept_emp as demp on s.emp_no = demp.emp_no
join departments as dept on demp.dept_no = dept.dept_no
where demp.from_date like '199%'
group by emp_no;

-- 3조 문제
-- dept_emp 와 employees 테이블 에 모든 데이터를  emp_no를 기준으로 조인하여 조회
-- 단(employees의 birth_date가 '05'를 포함하고,성별은 '남성'인 경우만.)
select *
from dept_emp as demp
join employees as e on demp.emp_no = e.emp_no
where e.birth_date like '%-05-%' and e.gender = 'M';
-- 모든 마케팅 부서 직원의 이름을 조회
select *
from employees as e
join dept_emp as demp on e.emp_no = demp.emp_no
join departments as dept on demp.dept_no = dept.dept_no
where dept.dept_name = 'Marketing';

-- 4조
-- 아직 재직중인 사람의 이름과 성 직급 입사일자를 출력해 보자.
select e.first_name, e.last_name, demp.from_date, t.title
from employees as e
join dept_emp as demp on e.emp_no = demp.emp_no
join titles as t on demp.emp_no = t.emp_no
where demp.to_date = '9999-01-01';
-- 부서 매니저들의 평균 연봉을 추출해 보자.
select *, avg(salary)
from salaries as s
join dept_manager as dm on s.emp_no = dm.emp_no
group by dm.emp_no;

-- 5조 문제 
-- 문제1.  
-- 부서 매니저의 연봉이 100,000 달러 이상인 사람 확인하시오 (출력예시 : 사원번호,입사날짜,연봉) 
select e.emp_no, dm.from_date, max(s.salary) as 연봉
from dept_manager as dm 
join salaries as s on dm.emp_no = s.emp_no
join employees as e on s.emp_no = e.emp_no
where salary >= 100000
group by e.emp_no;
-- 문제2. 
-- 8월 15일에 고용한 직원들에게 태극기를 지급하기로 했다. 사원 번호, 고용 날짜와 last_name을 조회하시오
select e.hire_date, e.last_name
from employees as e
where e.hire_date like '%-08-15';

-- 6조 문제 
-- 문제 1. 현재 재직중인 사람중 입사일이 1991-10-10 인 사람의 부서를 찾아주세요
select e.emp_no,e.first_name,d.from_date,dept.dept_name
from employees as e
join dept_emp as d on e.emp_no = d.emp_no
join  departments as dept on d.dept_no = dept.dept_no
where d.from_date = '1991-10-10';
-- 문제 2. 시니어 엔지니어의 월급중 6만달러가 넘는 사람이 몇명인지 찾아주세요.
select *,max(salary)
from titles as t
join salaries as s on t.emp_no = s.emp_no
where t.title = 'Senior Engineer' and salary > 60000
group by s.emp_no;

 

-- 1조
-- 문제 1 매니저들의 최고 급여를 추출하세요.
-- (오름차순 추가)
select *, max(salary) as 최고급여
from salaries as s
join dept_manager as dm on s.emp_no = dm.emp_no
group by dm.emp_no
order by 최고급여 desc;
-- 문제 2 퇴사자와 재직자를 구분 하며 퇴사자의 경우 재지년수를 출력하세요(출력예시 = first_name, last_name, 근무상태(재직중 or 퇴사자경우 근무연수))
select e.first_name, e.last_name,year(current_date) - year(from_date) as 근무년수,
case 
when demp.to_date = '9999-01-01'
then '재직중'
when demp.to_date != '9999-01-01'
then '퇴사자'
end as 근무상태
from employees as e
join dept_emp as demp on e.emp_no = demp.emp_no;

-- 강인한 2조
-- 문제 1 : 직원의 이름, 근무연수, 부서를 출력하시오.
-- 단 , 직원의 이름은 퍼스트네임, 라스트네임을 합쳐쳐서 표에 나타내고 근무연수는 35년 이상 직원의 직원들만 포함시키시오. 
-- (사원 번호 내림차순 추가)
select e.emp_no,concat(e.first_name, e.last_name) as 이름,year(current_date) - year(demp.from_date) as 근무년수, dept.dept_name 
from employees as e
join dept_emp as demp on e.emp_no = demp.emp_no
join departments as dept on demp.dept_no = dept.dept_no
where year(current_date) - year(demp.from_date) >= 35
order by e.emp_no;
-- 문제 2 부서 이름과 그 부서에 현재근무하는 직원에 정보를 출력 하세요 
select dept.dept_name, concat(e.first_name, e.last_name) as name, e.emp_no,e.birth_date, e.gender,e.hire_date
from departments as dept
join dept_emp as demp on dept.dept_no = demp.dept_no
join employees as e on demp.emp_no = e.emp_no
where demp.to_date = '9999-01-01'
group by e.emp_no
order by dept.dept_name;