YH-0.0.4-SNAPSHOT

부서 매니저였던 사원들의 현재 직급을 출력하시오SELECT e.emp_no, CONCAT(e.first_name, ' ', e.last_name), t.titleFROM dept_manager AS mJOIN employees AS e ON e.emp_no = m.emp_noJOIN titles AS t ON e.emp_no = t.emp_noWHERE m.to_date != '9999-01-01'AND t.to_date = '9999-01-01';

WITH tbl_ranking AS ( SELECT RANK() OVER(partition by d.dept_no ORDER BY s.salary DESC) AS ranking, CONCAT(e.first_name, ' ', e.last_name) AS name, s.salary, d2.dept_name FROM ( SELECT emp_no, MAX(salary) AS salary FROM salaries GROUP BY emp_no ) AS s JOIN employees AS e ON e.emp_no = s.emp_no JOIN dept_emp AS d ON e.emp_no = d.emp_no JOIN departments AS d2 ON d2.dept_no = d.dept_..

부서 이동 경험이 있는 사원들의 정보와 현재 부서를 조회하세요.SELECT e.emp_no, CONCAT(e.first_name, ' ', e.last_name) AS name, e.hire_date, d.dept_name, de.to_dateFROM dept_emp AS de JOIN departments AS d ON d.dept_no = de.dept_noJOIN employees AS e ON de.emp_no = e.emp_noWHERE to_date = '9999-01-01' AND e.emp_no IN ( SELECT e.emp_no FROM dept_emp AS d JOIN employees AS e ON ..

문제최신 연봉이 부서 최신 연봉의 평균보다 높은 사원을 조회하세요.WITH LatestSalaries AS ( SELECT emp_no, salary, ROW_NUMBER() OVER (PARTITION BY emp_no ORDER BY from_date DESC) AS rn FROM salaries),avgSalary AS ( SELECT b.dept_no, AVG(a.salary) AS avg_salary FROM (SELECT emp_no, salary FROM LatestSalaries WHERE rn = 1) AS a JOIN dept_emp AS b ON a.emp..

문제재직 중인 개발(Development)부서의 사원 목록과 가장 최신 연봉 정보를 출력SELECT a.emp_no, CONCAT(a.first_name, ' ', a.last_name) AS emp_name, b.salaryFROM employees AS aJOIN dept_emp AS c ON a.emp_no = c.emp_noJOIN departments AS d ON c.dept_no = d.dept_noJOIN salaries AS b ON a.emp_no = b.emp_noWHERE d.dept_name = 'Development' AND c.to_date > now() AND b.from_date = (..

문제to_date가 1998년도인 연봉 상위 10% 사원의 정보 조회WITH salary_ranked AS ( SELECT a.emp_no, a.first_name, a.last_name, b.salary, percent_rank() OVER (ORDER BY b.salary DESC) AS percentile FROM employees AS a JOIN ( SELECT emp_no, salary FROM salaries WHERE YEAR(to_date) = 1998 -- 연도 필터링 ..

문제1부서별 직무의 사원수를 계산하는 쿼리문을 작성하세요.select c.dept_name, d.title, count(a.emp_no) as employee_count from employees as a join dept_emp as b on a.emp_no = b.emp_nojoin departments as c on b.dept_no = c.dept_nojoin titles as d on a.emp_no = d.emp_nogroup by c.dept_no, d.titleorder by b.dept_no;