MySQL/第八章、练习题.sql

#第八章、练习题

#1、WHERE子句可否使用组函数进行过滤？
#不能

#2、查询公司员工工资的最大值，最小值，平均值，总和
SELECT MAX(salary) max_sal,MIN(salary) min_sal,AVG(salary) avg_sal,SUM(salary) sum_sal
FROM employees;

#3、查询各job_id的员工工资的最大值，最小值，平均值，总和
SELECT job_id,MAX(salary) max_sal,MIN(salary) min_sal,AVG(salary) avg_sal,SUM(salary) sum_sal
FROM employees
GROUP BY job_id;

#4、选择具有各个job_id的员工人数
SELECT job_id,COUNT(job_id)
FROM employees
GROUP BY job_id;

#老师做的
SELECT job_id,COUNT(*)
FROM employees
GROUP BY job_id;

#5、查询员工最高工资和最低工资的差距（DIFFERENCE）
SELECT MAX(salary)-MIN(salary) AS DIFFERENCE
FROM employees;

#6、查询各个管理者手下员工的最低工资，其中最低工资不能低于6000，没有管理者的员工不计算在内
#我做的，不对
SELECT mgr.last_name,COUNT(emp.last_name),MIN(emp.salary)
FROM employees mgr JOIN employees emp
ON emp.employee_id = mgr.manager_id
GROUP BY mgr.manager_id
HAVING MIN(emp.salary) > 6000;

#老师做的
SELECT manager_id,MIN(salary)
FROM employees
WHERE manager_id IS NOT NULL
GROUP BY manager_id
HAVING MIN(salary) >= 6000;


#7、查询所有部门的名字，location_id，员工数量和平均工资，并按平均工资降序
SELECT DISTINCT e.department_id,d.department_name,d.location_id,COUNT(last_name),AVG(salary)
FROM employees e,departments d
WHERE e.department_id = d.department_id
GROUP BY e.department_id
ORDER BY AVG(salary) DESC;

#老师做的
SELECT d.department_name,d.location_id,COUNT(employee_id),AVG(salary)
FROM departments d LEFT JOIN employees e
ON d.department_id = e.department_id
GROUP BY department_name,location_id
ORDER BY AVG(salary) DESC;

-- SELECT e.employee_id,salary,d.department_name,d.location_id
-- FROM employees e RIGHT JOIN departments d
-- ON e.department_id = d.department_id;


#8、查询每个工种、每个部门的部门名、工种名和最低工资
SELECT e.department_id,d.department_name,e.job_id,MIN(e.salary)
FROM employees e RIGHT JOIN departments d
ON e.department_id = d.department_id
GROUP BY department_id,job_id

#老师做的
SELECT d.department_name,e.job_id,MIN(salary)
FROM departments d LEFT JOIN employees e
ON e.department_id = d.department_id
GROUP BY department_name,job_id