[MySQL] 1일 1쿼리 (3) : 함수 응용하기

[MySQL] 1일 1쿼리 (3) : 함수 응용해보기

1. 문제
2. 풀이 (답안)

---

1. 문제

순서	문제
1	40 년차 이상인 직원들 명예 퇴직을 위해 조회하시오
2	급여가 1500000 이 넘는 인원중 아직 재직중인 인원의 이름을 찾아내시오
3	성별이 여자인 직원의 last_name 뒤에 2글자만 뽑아보시오
4	년차가 20년차 이상인 직원들의 년차별 월급 평균을 구하시오
5	현재 근무중인 근로자들의 직급(title)별 평균연봉을 나타내세요. 직급, 평균연봉(소수점 제외) 로 표시
6	마케팅부 30년차 근로자들 중 연봉이 80000 이상이면 O 아니면 X로 표기하세요. 이름(first_name + last_name), 년차, 연봉, 체크(O,X) 로 표시
7	직원들의 성(last_name)을 모두 대문자로 바꾸고, 이름 앞에 Mr/Ms를 붙여보자.
8	39년차 이상의 직원들의 목록을 (이름+성)의 형태로 출력해보자.
9	매니저들의 최고 급여를 추출하세요.
10	퇴사자와 재직자를 구분 하며 퇴사자의 경우 재지년수를 출력하세요(출력예시 = first_name, last_name, 근무상태(재직중 or 퇴사자경우 근무연수))
11	부서별 인원과 그 부서별 평균 급여를 출력하세요.
12	salaries테이블의 salary(연봉)을 월급으로 나누고(환율1365) -- dept_name, dept_no, emp_no, from_date,to_date 조회하시오(조인필요) -- 단 (from,to _date)는 salary의 테이블, 월급은 1,000,000 이상부터 나머지는 테이블 관계 X
13	직원의 이름, 근무연수, 부서를 출력하시오. -- 단 , 직원의 이름은 퍼스트네임, 라스트네임을 합쳐쳐서 표에 나타내고 근무연수는 35년 이상 직원의 직원들만 포함시키시오.
14	아파트 이름과 그 아파트에 현재사는 사람을 출력하세요
15	first_name의 두 번째 알파벳이 a인 직원의 평균연봉을 구하세요.
16	각 부서별 직원의 수와 평균 연봉을 구하세요. -- 표시 예시(title, '직원수' ,avg)
17	회사 CEO가 근속 20년이 넘은 직원들을 대상으로 14일의 안식 휴가와 감사패를 지급하기로 결정했습니다. 해당하는 직원들의 근속 년수와 풀네임을(한 컬럼) 출력하시오.
18	재직 중인 사람의 이름과 년차수를 출력 하세요

 

2. 풀이 (답안)

2024.06.07 목요일 1일 1쿼리



1. 40 년차 이상인 직원들 명예 퇴직을 위해 조회하시오
select*from employees;
select first_name, hire_date,
    year(Current_date)-year(hire_date)+1 as '연차'
    from employees
    where (year(Current_date)-year(hire_date)+1)>39;


2. 급여가 1500000 이 넘는 인원중 아직 재직중인 인원의 이름을 찾아내시오
select e.first_name, e.emp_no,s.salary
from employees as e
left join salaries as s
on e.emp_no = s.emp_no 
where salary > 150000 and to_date = '9999-1-01';


3. 성별이 여자인 직원의 last_name 뒤에 2글자만 뽑아보시오
select right(last_name,2) as '이름' , gender 
from employees
where gender = 'F';


4. 년차가 20년차 이상인 직원들의 년차별 월급 평균을 구하시오
select avg(salary) , from_date, current_date
from salaries 
where (year(current_date) - year(from_date)) > 20
group by (year(current_date) - year(from_date));


5. 현재 근무중인 근로자들의 직급(title)별 평균연봉을 나타내세요.
직급, 평균연봉(소수점 제외) 로 표시
select t.title as '직급', cast(avg(s.salary)as decimal(6)) as '평균연봉'
from titles as t
left join salaries as s
on t.emp_no = s.emp_no
where s.to_date = '9999-01-01' and t.to_date = '9999-01-01'
group by t.title;


6. 마케팅부 30년차 근로자들 중 연봉이 80000 이상이면 O 아니면 X로 표기하세요.
이름(first_name + last_name), 년차, 연봉, 체크(O,X) 로 표시
select concat(e.first_name,' ',e.last_name) as '이름', (year(current_date())-year(e.hire_date))+1 as '년차', s.salary as '연봉',
    case when s.salary >= 80000 then 'O' else 'X' end as '체크'
from employees as e
left join salaries as s
on e.emp_no = s.emp_no
join dept_emp as d
on e.emp_no = d.emp_no
where d.dept_no = 'd001' and d.to_date = '9999-01-01' and s.to_date = '9999-01-01' and ((year(current_date())-year(e.hire_date))+1) = 30;


7. 직원들의 성(last_name)을 모두 대문자로 바꾸고, 이름 앞에 Mr/Ms를 붙여보자.
select concat('Mr/Ms ',upper(last_name)) as '이름' from employees;


8. 39년차 이상의 직원들의 목록을 (이름+성)의 형태로 출력해보자.
select emp_no,concat(first_name," ",last_name) as '이름' 
    from employees
    where (year(current_date)-year(hire_date)+1)>39;
    
    
9.-- 매니저들의 최고 급여를 추출하세요.

select em.emp_no, dp.dept_name ,em.first_name, Max(salary)
from dept_emp as de
join employees as em
on de.emp_no = em.emp_no
join salaries as sa
on sa.emp_no = de.emp_no
join departments as dp
on de.dept_no = dp.dept_no
join dept_manager as dem
on dem.emp_no = de.emp_no
where dem.to_date = '9999-01-01'
group by de.emp_no;


10. 퇴사자와 재직자를 구분 하며 퇴사자의 경우 재지년수를 출력하세요
(출력예시 = first_name, last_name, 근무상태(재직중 or 퇴사자경우 근무연수))

select emp.first_name,emp.last_name,
case when to_date != '9999-01-01' then  (year(current_date())-year(to_date)) else '재직중' end as '근무연속'
from dept_emp as dep_e
join employees as emp
on dep_e.emp_no = emp.emp_no;


11. 부서별 인원과 그 부서별 평균 급여를 출력하세요.
select d.dept_name, count(de.emp_no) as '직원수', avg(s.salary)
from employees as e
left join dept_emp as de
on e.emp_no = de.emp_no
left join departments as d
on d.dept_no = de.dept_no
left join salaries as s
on s.emp_no = e.emp_no
group by d.dept_name;


12.  salaries테이블의 salary(연봉)을 월급으로 나누고(환율1365)
-- dept_name, dept_no, emp_no, from_date,to_date 조회하시오(조인필요)
-- 단 (from,to _date)는 salary의 테이블, 월급은 1,000,000 이상부터 나머지는 테이블 관계 X
select s.emp_no,substring((s.salary/121365),1,10) as '월급(년)',
        de.dept_no,de.dept_name,s.from_date,s.to_date
from salaries as s
left join dept_emp as e
on s.emp_no=e.emp_no
inner join departments as de
on e.dept_no=de.dept_no
where substring((s.salary/121365),1,10)>1000000;


13. 직원의 이름, 근무연수, 부서를 출력하시오.
-- 단 , 직원의 이름은 퍼스트네임, 라스트네임을 합쳐쳐서 표에 나타내고 근무연수는 35년 이상 직원의 직원들만 포함시키시오. 
select concat(e.first_name, ' ', e.last_name) as '이름', (year(current_date) - year(hire_date)) as '근속연차', dm.dept_name as '부서' 
from employees as e
left join dept_emp as d
on e.emp_no = d.emp_no
left join departments as dm
on d.dept_no = dm.dept_no
where (year(current_date) - year(hire_date) >= 35);


14. 아파트 이름과 그 아파트에 현재사는 사람을 출력하세요
select dept_name,employees.first_name,employees.last_name from departments
inner join dept_emp
on dept_emp.dept_no = departments.dept_no
inner join employees
on dept_emp.emp_no = employees.emp_no
where year(dept_emp.to_date) - (year(current_date()) >=0)
group by dept_emp.dept_no;


15.  first_name의 두 번째 알파벳이 a인 직원의 평균연봉을 구하세요.
select e.first_name, avg(s.salary) as '평균연봉'
from employees e
join salaries s on e.emp_no = s.emp_no
where substring(e.first_name, 2, 1) = 'a'
group by e.first_name;


16. 각 부서별 직원의 수와 평균 연봉을 구하세요.
-- 표시 예시(title, '직원수' ,avg)
select t.title, count(s.emp_no) as '직원수', avg(salary) as avg
from salaries as s
join titles as t on s.emp_no = t.emp_no
and s.to_date = t.to_date
group by t.title;


17. 회사 CEO가 근속 20년이 넘은 직원들을 대상으로 14일의 안식 휴가와 감사패를  지급하기로 결정했습니다. 
해당하는 직원들의 근속 년수와 풀네임을(한 컬럼) 출력하시오.

select concat(first_name, ' ', last_name) as '대상자',
 year(current_date) - year(hire_date) as 근속년수 
from employees
where year(current_date) - year(hire_date) >= 20;


18. 재직 중인 사람의 이름과 년차수를 출력 하세요
select em.first_name, em.last_name, year(current_date)  - year(em.hire_date) as '년차'
from employees as em
join dept_emp as de
on em.emp_no = de.emp_no
where de.to_date = '9999-01-01';