**
----------------分组查询
**
(1)查询至少有一个员工的部门编号。
select DEPARTMENT_ID
from EMPLOYEES
group by DEPARTMENT_ID
having count(*)>0;
(2)查询各个部门中不同职位的最高工资。
select DEPARTMENT_ID ,JOB_ID,max(SALARY)
from EMPLOYEES
group by DEPARTMENT_ID,JOB_ID;
(3)查询各个部门的人数及平均工资。
select DEPARTMENT_ID ,count(*) ,avg(SALARY)
from EMPLOYEES
group by DEPARTMENT_ID;
(4)统计各个职位的员工人数与平均工资。
select JOB_ID , count(*) , avg(SALARY)
from EMPLOYEES
group by JOB_ID;
(5)统计每个部门中各职位的人数与平均工资。
select DEPARTMENT_ID ,JOB_ID,count(*),avg(SALARY)
from EMPLOYEES
group by DEPARTMENT_ID,JOB_ID;
(6) 查询最低工资大于5000元的各职位。
Select JOB_ID ,avg(SALARY)
from EMPLOYEES
Group by JOB_ID
having min(SALARY)>5000;