(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;
```
395
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
