1일 1쿼리 - 10

-- 문제 1. 2000년 이후에 입사한 직원들 중 각 부서별 평균 연봉이 가장 높은 부서의 이름과 평균 연봉을 구하세요. (use employees)
SELECT d.dept_name, AVG(s.salary) AS avg_salary
FROM employees e
JOIN dept_emp de ON e.emp_no = de.emp_no
JOIN departments d ON de.dept_no = d.dept_no
JOIN salaries s ON e.emp_no = s.emp_no
WHERE e.hire_date >= '2000-01-01'
AND de.to_date = '9999-01-01'
AND s.to_date = '9999-01-01'
GROUP BY d.dept_no
ORDER BY avg_salary DESC
LIMIT 1;
-- 문제 2 : 현재 부서의 매니저가 아닌 직원들 중에서 가장 최근에 부서를 이동한 직원의 이름과 부서 이름을 구하세요.
SELECT e.first_name, e.last_name, d.dept_name, de.from_date
FROM employees e
JOIN dept_emp de ON e.emp_no = de.emp_no
JOIN departments d ON d.dept_no = de.dept_no
LEFT JOIN dept_manager dm ON e.emp_no = dm.emp_no
WHERE dm.emp_no IS NULL
ORDER BY de.from_date DESC
LIMIT 1;

-- 문제 3 : 각 직원의 첫 번째 연봉과 마지막 연봉의 차이를 계산하여 출력하세요.
SELECT e.first_name, e.last_name, MIN(s.salary) AS first_salary, MAX(s.salary) AS last_salary
, MAX(s.salary) - MIN(s.salary) AS salary_difference
FROM employees e
JOIN salaries s ON e.emp_no = s.emp_no
GROUP BY s.emp_no;

-- 문제 4 : 각 부서별로 성별 비율(남성 직원 수 / 여성 직원 수)을 계산하여 출력하세요.
SELECT d.dept_name, 
SUM(CASE WHEN e.gender = 'M' THEN 1 ELSE 0 END) AS male_count,
SUM(CASE WHEN e.gender = 'F' THEN 1 ELSE 0 END) AS female_count,
SUM(CASE WHEN e.gender = 'M' THEN 1 ELSE 0 END)  /
SUM(CASE WHEN e.gender = 'F' THEN 1 ELSE 0 END) AS gender_ratio
FROM employees e
JOIN dept_emp de ON e.emp_no = de.emp_no
JOIN departments d ON de.dept_no = d.dept_no
GROUP BY d.dept_no;

-- 문제 5 : 재직 중인 직원 중에서 가장 오랜 기간 재직 중인 직원의 이름과 부서 이름을 구하세요.
SELECT e.first_name, e.last_name, d.dept_name
FROM employees e
JOIN dept_emp de ON e.emp_no = de.emp_no
JOIN departments d ON de.dept_no = d.dept_no
WHERE de.to_date = '9999-01-01'
ORDER BY de.from_date
LIMIT 1;