第6章：分组函数

-----------------练习1-----------------------
1.查询部门20的员工，每个月的工资总和及平均工资。
select deptno 部门编号, sum(sal)工资总和,avg(sal)平均工资
from emp
group by deptno
having deptno=20;
2.查询工作在CHICAGO的员工人数，最高工资及最低工资。

select d1.员工人数,d1.最高工资,d1.最低工资,d1.deptno 部门编号,d2.loc 工作地点
from
(select deptno , count(*)员工人数,max(sal)最高工资,min(sal)最低工资
from emp
group by deptno) d1,dept d2
where d1.deptno=d2.deptno and d2.loc=‘CHICAGO’;

3.查询员工表中一共有几种岗位类型。
select count(DISTINCT job)岗位数 from emp;

-----------------思考-----------------------------------------------------
查询每个部门的平均工资？
select deptno 部门编号,avg(sal)平均工资
from emp
group by deptno;

-----------------练习2-----------------------------------------------------
1.查询每个部门的部门编号，部门名称，部门人数，最高工资，最低工资，工资总和，平均工资。

select d1.部门编号 ,d2.dname 部门名称,d1.部门人数,d1.最高工资,d1.最低工资,d1.工资总和,d1.平均工资
from
(select e.deptno 部门编号 ,count(*) 部门人数,max(sal)最高工资,min(sal)最低工资,sum(sal)工资总和,avg(sal)平均工资
from emp e
group by deptno) d1,dept d2
where d1.部门编号=d2.deptno;

2.查询每个部门，每个岗位的部门编号，部门名称，岗位名称，部门人数，最高工资，最低工资，工资总和，平均工资。
select e.,d.loc 部门名称
from
(select deptno 部门编号,job 岗位名称,count()部门人数,max(sal)最高工资,min(sal)最低工资,sum(sal)工资总和,avg(sal)平均工资
from emp
group by deptno,job) e,dept d
where e.部门编号=d.deptno -------两表关联限制，避免笛卡尔积
order by d.deptno;
3.查询每个经理所管理的人数，经理编号，经理姓名，要求包括没有经理的人员信息。

select e.empno 经理编号,e.ename 经理姓名,count(b.ename)经理所管理的人数
from emp e,emp b
where e.empno(+)=b.mgr
group by e.empno,e.ename;

-----------------练习3-----------------------------------------------------
1.查询部门人数大于2的部门编号，部门名称，部门人数。
select d.deptno,d.dname,count()
from dept d,emp e
where e.deptno=d.deptno
group by d.deptno,d.dname
having count()>2;
2.查询部门平均工资大于2000，且人数大于2的部门编号，部门名称，部门人数，部门平均工资，并按照部门人数升序排序。
select d.deptno,d.dname,count(),avg(sal)
from dept d,emp e
where e.deptno=d.deptno
group by d.deptno,d.dname
having count()>2 and avg(sal)>2000
order by count(*) asc;

-----------------课后作业----------------------------------------------------
1.查询部门平均工资在2500元以上的部门名称及平均工资。
select d.dname,avg(sal)
from emp e,dept d
where e.deptno=d.deptno
group by d.dname
having avg(sal)>=2500;
2.查询员工岗位中不是以“SA”开头并且平均工资在2500元以上的岗位及平均工资，并按平均工资降序排序。
select job,avg(sal)
from emp
group by job
having avg(sal)>=2500
and job not like ‘SA%’ ;
3.查询部门人数在2人以上的部门名称、最低工资、最高工资,并对求得的工资进行四舍五入到整数位。
select d.dname,min(sal),min(sal),max(sal),count()
from dept d,emp e
where e.deptno=d.deptno
group by d.dname
having count()>2;

4.查询岗位不为SALESMAN，工资和大于等于2500的岗位及每种岗位的工资和。
select job,sum(sal)
from emp
group by job
having job<>‘SALESMAN’ and sum(sal)>=2500;
5.显示经理号码和经理姓名，这个经理所管理员工的最低工资，没有经理的KING也要显示，不包括最低工资小于3000的，按最低工资由高到低排序。
select b.empno 经理号码,e.ename 经理所管理员工 ,b.ename 经理姓名,min(e.sal)最低工资
from emp e,emp b
where e.mgr(+)=b.empno
group by b.empno,e.ename,b.ename
having min(e.sal)>=3000
order by min(e.sal)desc

6.写一个查询，显示每个部门最高工资和最低工资的差额
select d.deptno,max(sal)-min(sal)工资的差额
from emp e,dept d
where e.deptno=d.deptno
group by d.deptno