1일 1쿼리 - 0610

1조 1일 1쿼리 문제 (24.06.10)

-- 재직중인 사원들 중 평균 연봉이 가장 큰 3명의 first_name, 평균 연봉(소숫점 제거),부서, 근무 년수 을 추출하시오.
select em.first_name, round((sa.salary)) as '평균 연봉', dep.dept_name, de.from_date, year(now()) - year(sa.from_date) + 1 as '근무 년수'
from dept_emp as de
join salaries as sa
on de.emp_no = sa.emp_no
join employees as em
on de.emp_no = em.emp_no
join departments as dep
on de.dept_no = dep.dept_no
where de.to_date = '9999-01-01'
group by dep.dept_no
order by round((sa.salary)) desc
limit 3;



-- 전직원에게 생일 상여금 지급 하기로 하였다. 월별 지급해야할 인원수를 구하세요(퇴사자 제외 || 출력예시 월, 인원수)
select  month(birth_date) as m, count(*)
from employees as em
join dept_emp as dem
on em.emp_no = dem.emp_no
where dem.to_date != '9999-01-01'
group by month(birth_date)
order by m asc;

 

 

노력하는 2조

-- 2024-06-10 일일 일쿼리
--------------------------------------------- 
-- 부서별 평균급여계산 후 60000보다 높으면 'High' 낮으면 'Low' (부서, 부서평균, 급여수준 ('High' or 'Low')) 부서평균은 내림차순, 소수점 제거 해라
select concat('[ ', d.dept_name, ' ]') as '부서', round(avg(salary)) as '부서평균' , case when avg(salary) > 60000 then 'High' else 'Low' end 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
group by de.dept_no
order by avg(salary) desc;

---------------------------------------------
-- 문제 2 -- 각 부서의 이름과 총 인원수를 출력하시오
select d.dept_name, count(*) as 인원수
from employees as e
join dept_emp as de
join departments as d
on e.emp_no = de.emp_no and d.dept_no = de.dept_no
group by dept_name;

 

3조

-- 문제 1. 직원들 개인의 입사 이후 현재까지의 평균 연봉을 구해서 80000이 넘는 사람들만 출력하라. (내림차순으로 상위 100명만)
-- 출력 예시 (emp_no, last_name, avg)
select e.emp_no, last_name, round(avg(salary), 0) as avg
from salaries as s
join employees as e on s.emp_no = e.emp_no
group by s.emp_no
having avg > 80000
order by avg desc
limit 100;

-- 문제 2
-- 전체 평균 급여를 소수점 제외 도출하고 급여가 평균을 넘는 사원의 이름을 출력하세요.
select e.first_name, e.last_name, round(avg(salary), 0) as '평균급여'
from salaries as s
join employees as e
on s.emp_no = e.emp_no
group by e.emp_no
having avg(salary) >= (select avg(salary) from salaries);

 

 

 

-- 4조 - 06.10  1일 1쿼리 
-- 각 부서별 인원을 체크 하고 재직 인원이 제일 적은 순서로 부서를 세개 출력하시오

select par.dept_name, count(dep.dept_no) as 직원수
from dept_emp as dep
join departments as par
on dep.dept_no = par.dept_no
where dep.to_date = '9999-01-01'
group by par.dept_no
order by 직원수 asc
limit 3;

-- 각 부서의 남자 막내 사원들을 대상으로 워크숍이 예정되어 있습니다. 참석 여부를 조사하기 위해
-- 대상자를 구분하여 부서번호, 사번, 입사일, 성, 이름을 출력하시오.


select d.dept_no as 부서번호, e.emp_no as 사번, max(e.hire_date) as 입사일, e.first_name as 성, e.last_name as 이름
from employees as e
join dept_emp as d
on e.emp_no = d.emp_no
where e.gender = 'm'
group by d.dept_no
order by d.dept_no asc; 

 

[5조]

-- 문제1. Engineer에서 Senior Engineer로 승진했던 직원의 직원 번호와 first_name last_name을 합친 이름을 출력하시오
select t.emp_no, concat(e.first_name,' ',last_name) as 'name'
from titles as t
join employees as e
on t.emp_no = e.emp_no
where (t.title = 'Engineer' or t.title = 'Senior Engineer')
group by t.emp_no
having count(t.emp_no) >= 2;

-- 문제2. 근무하는 직원이 50000명 이상인 부서와 그 부서의 직원 수를 출력하세요.
select d.dept_no, d.dept_name as 부서, count(de.emp_no) as 직원수
from departments as d
join dept_emp as de
on d.dept_no = de.dept_no
group by d.dept_no
having 직원수 >= 50000;

 

6조 1일 1쿼리 문제

 

(1) 마케팅 부서의 직원들을 입사 순서대로 오름차순 정렬하세요.
-- 출력해야 할 것: 직원의 이름, 고용일, 부서
select e.first_name, e.hire_date, de.dept_name
from employees as e
left join dept_emp as d
on e.emp_no=d.emp_no
left join departments as de
on d.dept_no=de.dept_no
where de.dept_no='d001'
order by e.hire_date desc;

(2) 근무기간이 30년이상인 Staff 직원들중에서 emp_no낮은순서부터 200명 중에서 가장 오래된 근무년수는?
select title,emp_no ,(year(current_date()) - year(from_date)) as 근무년수 
from titles
where to_date = '9999-01-01' and title = 'Staff'
having 근무년수 >= 30
order by 근무년수 desc
limit 200;