Oracle小练习

oracle小练习

--查询部门的平均工资
select deptno,avg(sal) from emp group by deptno;
select deptno,job,sal from emp group by job,deptno,sal;
--having 分组后的条件 having和where的区别
--查询每个部门的平均工资要求平均工资大于2000
select deptno,avg(sal) from emp group by deptno having avg(sal)>2000
--查询部门编号为10的部门编号和平均工资
select deptno,avg(sal) from emp where deptno = 10 group by deptno
--查询部门编号为10的部门编号和平均工资
select deptno,avg(sal) from emp group by deptno having deptno=10
--统计人数小于5的部门的平均工资
select avg(sal) from emp group by deptno having count(*)<5
--统计各部门的最高工资，排除最高工资小于3000的部门
select deptno,max(sal) from emp group by deptno having max(sal)>=3000