分组查询测试题

#1.查询各job_id的员工工资的最大值，最小值，平均值，总和，并按job_id升序SELECT MAX(salary),MIN(salary),AVG(salary),SUM(salary),job_idFROM employeesGROUP BY job_idORDER BY job_id ASC;

#2查询员工最高工资和最低工资的差距（DIFFRENCE）

SELECT MAX(salary)-MIN(salary) DIFFRENCEFROM employees;

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

SELECT MIN(salary),manager_idFROM employeesWHERE manager_id IS NOT NULLGROUP BY manager_idHAVING MIN(salary)>=6000;

#4.查询所有部门的编号，员工数量和工资平均值，并按平均工资降序

SELECT department_id,COUNT(*),AVG(salary)FROM employeesGROUP BY department_idORDER BY AVG(salary) DESC;

#5.选择具有各个job_id的员工个数

SELECT COUNT(*) 个数,job_idFROM employeesGROUP BY job_id;