【MySQL练习】聚合函数练习

数据库表数据：https://www.cnblogs.com/zhishu/p/16452950.html

1.where子句可否使用组函数进行过滤?

组函数：max()，min()，count()，avg()，sum()

SELECT * 
FROM employees
WHERE COUNT(salary)>0;

报错：组函数非法使用，where子句不可以使用组函数

2.查询公司员工工资的最大值，最小值，平均值，总和

SELECT MAX(salary),MIN(salary),AVG(salary),SUM(salary)
FROM employees;

3.查询各job_id的员工工资的最大值，最小值，平均值，总和

SELECT job_id,MAX(salary),MIN(salary),AVG(salary),SUM(salary)
FROM employees
GROUP BY job_id;

4.选择具有各个job_id的员工人数

SELECT job_id,COUNT(*)
FROM employees
GROUP BY job_id;

5.查询员工最高工资和最低工资的差距（DIFFERENCE）

SELECT MAX(salary),MIN(salary),(MAX(salary)-MIN(salary)) AS DIFFERENCE
FROM employees;

6.查询各个管理者手下员工的最低工资，其中最低工资不能低于6000，没有管理者的员工不计算在内

where 和 having的区别：https://www.cnblogs.com/zhishu/p/16592733.html

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 a.department_name,a.location_id,count(b.employee_id),AVG(b.salary) AS avg_sal
FROM departments a
LEFT JOIN employees b ON a.department_id = b.department_id
GROUP BY a.department_name,a.location_id
ORDER BY avg_sal DESC;

8.查询每个部门的部门名、工种名和最低工资

SELECT department_name,job_id,MIN(salary)
FROM departments d 
LEFT JOIN employees e ON e.`department_id` = d.`department_id`
GROUP BY department_name,job_id;