Oracle 分组函数

-- -- nvl函数可以使分组函数强制包含含有空值的记录
-- select avg(nvl(comm,0)) from emp;--包含null值记录
-- select avg(comm) from emp;--不含null值记录


-- --distinct会消除重复记录后在使用组函数
-- select count(distinct deptno) from emp;
-- select count(deptno) from emp;

练习一

1.查询部门20的员工，每个月的工资总和及平均工资。
select sum(sal) "工资总和",avg(sal) "平均工资"
from emp
where deptno=20;

2.查询工作在CHICAGO的员工人数，最高工资及最低工资。
select max(sal) "最高工资",min(sal) "最低工资"
from emp e join dept d on e.DEPTNO=d.DEPTNO
where d.loc = 'CHICAGO';

3.查询员工表中一共有几种岗位类型。

select distinct(job) from emp;

--第六章练习2
1.查询每个部门的部门编号，部门名称，部门人数，最高工资，最低工资，工资总和，平均工资。
select d.deptno,d.dname,count(e.ename),
max(e.sal),min(e.sal),sum(sal),avg(sal)
from dept d join emp e on e.deptno=d.deptno
group by d.deptno,d.dname

--第六章练习2
2.查询每个部门，每个岗位的部门编号,部门名称，岗位名称，部门人数，最高工资，最低工资，工资总和，平均工资。
select d.deptno 部门编号,d.dname 部门名称,e.job 岗位名称,count(e.empno) 部门人数,max(e.sal),min(e.sal),sum(e.sal),avg(e.sal)
from dept d
join emp e on e.deptno=d.deptno
group by d.deptno,d.dname,e.job;


3.查询每个经理所管理的人数，经理编号，经理姓名，要求包括没有经理的人员信息。
select count(d.ename) "管理的人数", e.empno "经理编号",e.ename "经理姓名"
from emp e ,emp d
where e.empno =d.mgr
or d.mgr is null
group by e.empno,e.ename;


1.查询部门人数大于2的部门编号，部门名称，部门人数
select d.deptno,d.dname,count(e.ename)
from dept d
join emp e on d.deptno=e.deptno
group by d.dname,d.deptno
having(count(e.ename)>2);


2.查询部门平均工资大于2000，且人数大于2的部门编号，部门名称，部门人数，部门平均工资，并按照部门人数升序排序。
SELECT d.deptno,d.dname,count(e.ename),avg(e.sal)
FROM dept d ,emp e
where d.deptno = e.deptno
group by d.deptno ,d.DNAME
having (count(e.ename)>2)
AND avg(e.sal)>2000
order by 3;


select d.deptno,d.dname,count(e.ename),avg(e.sal)
from dept d
join emp e on d.deptno=e.deptno
group by d.dname,d.deptno
having(count(e.ename)>2)
order by count(e.ename) ;



-- 第六章课后习题
• 1.查询部门平均工资在2500元以上的部门名称及平均工资。
select d.dname ,avg(e.sal)
FROM dept d ,emp e
WHERE d.DEPTNO =e.DEPTNO
GROUP BY d.dname --GROUP BY 用在分组函数中
HAVING (avg(sal)>2500);

select d.dname ,avg(e.sal)
from dept d
join emp e on d.deptno=e.deptno
group by d.dname
having(avg(e.sal)>2500);


----第六章课后习题2
• 2.查询员工岗位中不是以“SA”开头并且平均工资在2500元以上的岗位及平均工资，并按平均工资降序排序。
SELECT e.job 岗位,avg(e.sal) 平均工资
from emp e
WHERE job not like 'SA%'
group by e.job
having (avg(e.sal)>2500)
order by avg(e.sal) desc;


----第六章课后习题
3.查询部门人数在2人以上的部门名称、最低工资、最高工资,并对求得的工资进行四舍五入到整数位。
select d.dname,round(min(e.sal),0),round(max(e.sal),0)
from emp e join dept d on e.DEPTNO = d.DEPTNO
GROUP BY d.dname
having count(e.ename)>2;

--第六章课后习题 4
4.查询岗位不为SALESMAN，工资和大于等于2500的岗位及每种岗位的工资和。
SELECT * from EMP;
select e.job , sum(e.sal)
from emp e
group by e.job
having e.job not like 'SALESMAN'
and sum(e.sal) >=2500;

--第六章课后习题
5.显示经理号码和经理姓名，这个经理所管理员工的最低工资，没有经理的KING也要显示，
不包括最低工资小于3000的，按最低工资由高到低排序。
select m.empno 经理编号,m.ename,min(e.sal)
from emp e left join emp m on (e.mgr=m.empno)
group by m.empno,m.ENAME
having (min(e.sal)>=3000)
order by 3 desc;


6.写一个查询，显示每个部门最高工资和最低工资的差额。
SELECT d.dname 部门名称, max(e.sal) 最高工资,min(e.sal) 最低工资, max(e.sal)-min(e.sal) 工资差额
from emp e,dept d
WHERE e.DEPTNO = d.DEPTNO
group by d.dname;