Oracle经典练习题—分组函数

基础知识

1.Count：

如果数据库表的没有数据，count(*)返回的不是null ，而是0

查看共有多少个部门 Select count(dname) from dept;

Avg（平均） ，max（最大），min（最小），sum（求和）

Select avg(comm),max(comm),min(comm),sum(comm) from bonus;

GROUP BY 子句

 出现在SELECT 列表中的字段或者出现在order by  后面的字段，如果不是包含在分组函数 中，那么该字段必须同时在GROUP BY 子句中出现。  包含在  GROUP BY 子句中的字段则不必须出现在SELECT 列表中。  可使用where 字句限定查询条件  可使用Order by 子句指定排序方式    如果没有GROUP BY 子句，SELECT 列表中不允许出现字  段（单行函数）与分组函数混用的情况。   

 select empno, sal from emp; //合法  

select avg(sal) from emp; //合法

 select empno, initcap(ename), avg(sal) from emp; //非法

不允许在  WHERE  子句中使用分组函数。

 select deptno, avg(sal)  from emp  where avg(sal) > 2000;  group by deptno;  

HAVING 子句

select deptno, job, avg(sal)  from emp   where hiredate >= to_date('1981-05-01','yyyy-mm-dd')  group by deptno,job  having avg(sal) > 1200  order by deptno,job;

分组函数嵌套

select max(avg(sal))  from emp  group by deptno;

练习

--1.分组统计各部门下工资>500 的员工的平均工资

select avg(sal)from emp where sal>500 group by deptno;

--2.统计各部门下平均工资大于500 的部门

select deptno from emp group by deptno having avg(sal)>500;

--3.算出部门30 中得到最多奖金的员工奖金      

select max(comm)from emp where deptno=30;

--4.算出部门30 中得到最多奖金的员工姓名       

select ename from emp where comm=(select max(comm)from emp where deptno=30)and deptno=30;

--5. 算出每个职位的员工数和最低工资             

select job,count(*),min(sal)from emp group by job;

--6.算出每个部门,，每个职位的平均工资和平均奖金(平均值包括没有奖金) ，如果平均奖金大于300，显示“奖 金不错”，如果平均奖金100 到300，显示“奖金一般”，如果平均奖金小于100，显示“基本没有奖金”， 按部门编号降序，平均工资降序排列

select deptno,
       job,
       avg(sal),
       avg(nvl(comm, 0)),
       case
         when avg(nvl(comm, 0)) > 300 then
          '奖金不错'
         when avg(nvl(comm, 0)) >= 100 then
          '奖金一般'
         else
          '基本没有奖金'
       end
  from emp
 group by deptno, job
 order by deptno desc, avg(sal) desc;

--7.列出员工表中每个部门的员工数，和部门no   

select deptno,count(*) from emp group by deptno;

--8.得到工资大于自己部门平均工资的员工信息

select * from emp e1,(select deptno,avg(sal) as avgsal from emp group by deptno) e2 where e1.deptno=e2.deptno and e1.sal>e2.avgsal;

--9.分组统计每个部门下，每种职位的平均奖金（也要算没奖金的人）和总工资(包括奖金)

select deptno,job,avg(nvl(comm,0)),sum(nvl(comm,0)+sal) from emp group by deptno,job;