组函数和分组查询

– 组函数
select count() from emp;
select count() from dept;
–查询工资最高员工信息
– var v_sal = select max(sal) from emp;
select * from emp where sal = (select max(sal) from emp)
–查询工资最低员工信息
select * from emp where sal = (select min(sal) from emp);
–求公司员工的平均工资
select avg(sal) from emp
–每个月公司的开支
select sum(sal) from emp;

–求出每个部门的雇员数量
select deptno,count(*) total from emp group by deptno order by total desc;

–求出每个部门的平均工资
select deptno,trunc(avg(sal),2) from emp group by deptno

SELECT deptno,COUNT(empno) FROM emp group by deptno;

–按部门分组，并显示部门名称，及每个部门的员工数
select d.dname,count(*) from emp e,dept d where e.deptno = d.deptno group by d.dname;

–要求显示出平均工资大于2000的部门编号和平均工资
select deptno, avg(sal) from emp group by deptno having avg(sal) > 2000;

–求出每个部门的最低工资的雇员信息
select * from emp where sal in(select min(sal) from emp group by deptno);
–any 关键字 如果结果条件中有多个值，并且使用等号时，用any关键标识
select * from emp where sal = any(select min(sal) from emp group by deptno);

– >any 比结果集中最小值大(800,950,1300) > 800都显示
SELECT * FROM emp WHERE sal > ANY(SELECT MIN(sal) FROM emp GROUP BY deptno) ;
– >all 比结果集中最大值大(800,950,1300) > 1300大的都显示
SELECT * FROM emp WHERE sal > ALL(SELECT MIN(sal) FROM emp GROUP BY deptno) ;

– < ANY:比里面最大的值要小 (800,950,1300) < 1300
SELECT * FROM emp WHERE sal < ANY(SELECT MIN(sal) FROM emp GROUP BY deptno) ;
– <ALL:比最小的值要小 (800,950,1300) < 800
SELECT * FROM emp WHERE sal < ALL(SELECT MIN(sal) FROM emp GROUP BY deptno) ;