SELECT d.dept_name, ROUND(AVG(s.salary), 2) as avg_salary
FROM salaries s
JOIN dept_emp de ON s.emp_no = de.emp_no
JOIN departments d ON de.dept_no = d.dept_no
WHERE s.to_date = '9999-01-01' AND de.to_date = '9999-01-01'
GROUP BY d.dept_name
ORDER BY avg_salary DESC;
2. Топ-10 найвищеоплачуваних співробітників
SELECT e.first_name, e.last_name, s.salary, d.dept_name
FROM employees e
JOIN salaries s ON e.emp_no = s.emp_no
JOIN dept_emp de ON e.emp_no = de.emp_no
JOIN departments d ON de.dept_no = d.dept_no
WHERE s.to_date = '9999-01-01' AND de.to_date = '9999-01-01'
ORDER BY s.salary DESC
LIMIT 10;
3. Кількість співробітників за роками найму
SELECT
YEAR(hire_date) as hire_year,
COUNT(*) as employee_count
FROM
employees
GROUP BY
hire_year
ORDER BY
hire_year;
4. Менеджери департаментів
SELECT
d.dept_name,
CONCAT(e.first_name, ' ', e.last_name) as manager_name,
dm.from_date as start_date,
CASE
WHEN dm.to_date = '9999-01-01' THEN 'Current'
ELSE dm.to_date
END as end_date
FROM
dept_manager dm
JOIN
departments d ON dm.dept_no = d.dept_no
JOIN
employees e ON dm.emp_no = e.emp_no
ORDER BY
d.dept_name, dm.from_date;