3. 在MySQL样本数据库上完成下列查询： (1) 查询每个部门(departments)的编号(dept_no),名称(dept_name),在该部门工作过的雇员(employees)人数,最低工资(salary),平均工资,最高工资及工资总额； (2) 查询每个部门(departments)的编号(dept_no),名称(dept_name),及各个时间段(from_date,to_date)担任该部门经理(dept_manager)的雇员的编号(emp_no)和姓名(first_name+last_name)，并按时间段先后显式； (3) 查询每位雇员的编号(emp_no),姓名(first_name+last_name),及各个时间段(from_date,end_data)的工资额(salary),并按时间段先后显式； (4) 查询每位雇员的编号(emp_no),姓名(first_name+last_name),及各个时间段(from_date,end_data)的工作部门名称(dept_name),并按时间段先后显式； (5) 查询每位雇员的编号(emp_no),姓名(first_name+last_name),及任职过的部门数； (6) 查询每位雇员的编号(emp_no),姓名(first_name+last_name),及各个时间段(from_date,end_data)担任的职务(title),并按时间段先后显式；

(1) 查询每个部门(departments)的编号(dept_no),名称(dept_name),在该部门工作过的雇员(employees)人数,最低工资(salary),平均工资,最高工资及工资总额；

SELECT d.dept_no, d.dept_name, COUNT(e.emp_no) AS num_of_employees, MIN(s.salary) AS min_salary, AVG(s.salary) AS avg_salary, MAX(s.salary) AS max_salary, SUM(s.salary) AS total_salary
FROM departments d
LEFT JOIN dept_emp de ON d.dept_no = de.dept_no
LEFT JOIN employees e ON de.emp_no = e.emp_no
LEFT JOIN salaries s ON e.emp_no = s.emp_no
GROUP BY d.dept_no, d.dept_name;

(2) 查询每个部门(departments)的编号(dept_no),名称(dept_name),及各个时间段(from_date,to_date)担任该部门经理(dept_manager)的雇员的编号(emp_no)和姓名(first_name+last_name)，并按时间段先后显式；

SELECT d.dept_no, d.dept_name, dm.from_date, dm.to_date, e.emp_no, CONCAT(e.first_name, ' ', e.last_name) AS emp_name
FROM departments d
LEFT JOIN dept_manager dm ON d.dept_no = dm.dept_no
LEFT JOIN employees e ON dm.emp_no = e.emp_no
ORDER BY dm.from_date ASC;

(3) 查询每位雇员的编号(emp_no),姓名(first_name+last_name),及各个时间段(from_date,end_data)的工资额(salary)，并按时间段先后显式；

SELECT e.emp_no, CONCAT(e.first_name, ' ', e.last_name) AS emp_name, s.from_date, s.to_date, s.salary
FROM employees e
LEFT JOIN salaries s ON e.emp_no = s.emp_no
ORDER BY s.from_date ASC;

(4) 查询每位雇员的编号(emp_no),姓名(first_name+last_name),及各个时间段(from_date,end_data)的工作部门名称(dept_name)，并按时间段先后显式；

SELECT e.emp_no, CONCAT(e.first_name, ' ', e.last_name) AS emp_name, dp.from_date, dp.to_date, d.dept_name
FROM employees e
LEFT JOIN dept_emp dp ON e.emp_no = dp.emp_no
LEFT JOIN departments d ON dp.dept_no = d.dept_no
ORDER BY dp.from_date ASC;

(5) 查询每位雇员的编号(emp_no),姓名(first_name+last_name),及任职过的部门数；

SELECT e.emp_no, CONCAT(e.first_name, ' ', e.last_name) AS emp_name, COUNT(DISTINCT dp.dept_no) AS num_of_departments
FROM employees e
LEFT JOIN dept_emp dp ON e.emp_no = dp.emp_no
GROUP BY e.emp_no, emp_name;

(6) 查询每位雇员的编号(emp_no),姓名(first_name+last_name),及各个时间段(from_date,end_data)担任的职务(title)，并按时间段先后显式；

SELECT e.emp_no, CONCAT(e.first_name, ' ', e.last_name) AS emp_name, tt.from_date, tt.to_date, t.title
FROM employees e
LEFT JOIN titles t ON e.emp_no = t.emp_no
LEFT JOIN titles tt ON t.emp_no = tt.emp_no AND tt.from_date < t.from_date
GROUP BY e.emp_no, tt.from_date, tt.to_date, t.title
ORDER BY tt.from_date ASC;