[打卡]聚合函数练习题

#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;
#4.选择具有各个job_id的员工人员
SELECT job_id,COUNT(*)
from employees
GROUP BY job_id;
#5.查询员工最高工资和最低工资的差距（difference）
SELECT MAX(salary),MIN(salary),MAX(salary)-MIN(salary)difference
from employees;
#6.查询各个管理者手下员工的最低工资，其中最低工资不能低于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 department_name,location_id,COUNT(employee_id),AVG(salary)avg_sal
from employees e
RIGHT JOIN departments d
on e.`department_id`=d.`department_id`
GROUP BY department_name,location_id
ORDER BY avg_sal desc;