Oracle数据库：分组函数练习

–练习1
–1.查询部门20的员工，每个月的工资总和及平均工资。
select sum(sal),avg(sal) from emp where deptno=20;
–2.查询工作在CHICAGO的员工人数，最高工资及最低工资。
select count(*),max(sal),min(sal) from emp e,dept d
where e.deptno=d.deptno and d.loc=‘CHICAGO’;
–3.查询员工表中一共有几种岗位类型。
select count(distinct(job)) from emp;
–练习2
–1.查询每个部门的部门编号，部门名称，部门人数，最高工资，最低工资，工资总和，平均工资。
select d.deptno 部门编号,
d.dname 部门名称,
count(e.empno) 部门人数,–查询员工表中的相同部门下的员工数
max(e.sal) 最高工资,
min(e.sal) 最低工资,
sum(e.sal) 工资总和,
avg(e.sal) 平均工资
from emp e, dept d
where e.deptno = d.deptno
group by d.deptno, d.dname
–2.查询每个部门，每个岗位的部门编号，部门名称，岗位名称
–，部门人数，最高工资，最低工资，工资总和，平均工资。

–3.查询每个经理所管理的人数，经理编号，经理姓名，要求包
–括没有经理的人员信息。
select count(e.empno),m.empno,m.ename from emp e,emp m
where e.mgr(+)=m.empno
group by m.empno,m.ename;
–练习3
–1.查询部门人数大于2的部门编号，部门名称，部门人数。
select d.dname,count(e.empno) from emp e,dept d
where e.deptno=d.deptno
group by d.dname;
–2.查询部门平均工资大于2000，且人数大于2的部门编号，部门
–名称，部门人数，部门平均工资，并按照部门人数升序排序。
select d.deptno,d.dname,count(e.empno),avg(e.sal) from emp e,dept d
where e.deptno=d.deptno
group by d.deptno,d.dname
having avg(e.sal)>=2000 and count(e.empno)>=2;
–课后作业
–1.查询部门平均工资在2500元以上的部门名称及平均工资。
select d.dname,avg(e.sal) from emp e,dept d
where d.deptno=e.deptno
group by d.deptno,d.dname
having avg(e.sal)>2500;
–2.查询员工岗位中不是以“SA”开头并且平均工资在2500
–元以上的岗位及平均工资，并按平均工资降序排序。
select ename,job,avg(sal) from emp
where ename not like ‘SA%’
group by job,ename
having avg(sal)>2500
order by avg(sal) desc;
–3.查询部门人数在2人以上的部门名称、最低工资、最高
–工资,并对求得的工资进行四舍五入到整数位。
select d.dname,round(min(e.sal)),round(max(e.sal)) from emp e,dept d
where e.deptno=d.deptno
group by e.deptno,d.dname
having count()>2;
–4.查询岗位不为SALESMAN，工资和大于等于2500的岗位及
–每种岗位的工资和。
select job,sum(sal) from emp
where job<>‘SALESMAN’
group by job
having sum(sal)>2500;
–5.显示经理号码和经理姓名，这个经理所管理员工的最低
–工资，没有经理的KING也要显示，不包括最低工资小于
–3000的，按最低工资由高到低排序。
select m.empno,m.ename,min(e.sal) from emp e,emp m
where e.mgr(+)=m.empno
group by m.empno,m.ename
having min(e.sal)<1000;
–6.写一个查询，显示每个部门最高工资和最低工资的差额。
select deptno,(max(sal)-min(sal)) from emp
group by deptno;
–课后作业
–1.查询入职日期最早和最晚的日期。
select max(hiredate) 最晚日期, min(hiredate) 最早日期 from emp;
–2.查询职位以SALES开头的所有员工平均工资、最低工资、最高工资、工资和。
select avg(sal) 平均工资,
min(sal) 最低工资，max(sal) 最高工资，sum(sal) 工资和
from emp
where ename not like ‘SALES%’;
–3.查询部门30有多少个员工。
select count(deptno) from emp where deptno=30;
–4.查询部门30有多少个员工领取奖金。
select count(comm) from emp where deptno=30;
–5.查询所有员工的部门数量。
select distinct(deptno) 部门数量 from emp;
–6.查询部门20的员工，每个月的工资总和及平均工资。
select sum(sal),avg(sal) from emp where deptno=20;
–7.查询工作在CHICAGO的员工人数，最高工资及最低工资。
select max(e.sal) 最高工资, min(e.sal) 最低工资
from emp e, dept d
where e.deptno = d.deptno
and d.loc = ‘CHICAGO’;
–8.查询员工表中一共有几种岗位类型。
select count(distinct(job)) 岗位类型 from emp;
–9.查询每个部门的平均工资？
select avg(sal) from emp group by deptno;
–10.查询每个部门的编号，平均工资
select deptno,avg(sal) from emp group by deptno;
–11.查询每个部门每个岗位的工资总和。
select deptno,job,sum(sal) from emp group by deptno,job;
–12.查询每个部门的部门编号，部门名称，部门人数，最高工资
–，最低工资，工资总和，平均工资。
select d.deptno 部门编号,
d.dname 部门名称，count(d.deptno) 部门人数,
max(e.sal) 最高工资,
min(sal) 最低工资,
sum(sal) 工资总和，avg(sal) 工资总和
from emp e, dept d
where e.deptno = d.deptno
group by d.deptno, d.dname;
–13.查询每个部门，每个岗位的部门编号，部门名称，岗位名称
–，部门人数，最高工资，最低工资，工资总和，平均工资。
select d.deptno 部门编号,
d.dname 部门名称,
e.job 岗位名称， count(d.deptno) 部门人数,
max(sal) 最高工资,
min(sal) 最低工资,
sum(sal) 工资总和,
avg(sal) 平均工资
from emp e, dept d
where e.deptno = d.deptno
group by d.deptno, d.dname, e.job;
–14查询每个经理所管理的人数，经理编号，经理姓名，要求包
–括没有经理的人员信息。
select count() 管理人数,m.empno 经理编号,m.ename 经理姓名
from emp e,emp m
where e.mgr=m.empno(+)–员工表的经理等于经理表中的员工编码
–注意：在本题中，如果m.mgr是经理的经理。emp e 是员工表，emp m是经理表。
group by m.empno,m.ename
–15.查询每个部门最高工资大于2900的部门编号，最高工资.
select deptno, max(sal) from emp
group by deptno
having max(sal) > 2900;
–16.查询部门人数大于2的部门编号、部门名称、部门人数
–查询部门人数时注意要关联员工表计算
select d.dname 部门名称, d.deptno 部门编号, count(e.empno) 部门人数
from dept d,emp e
where d.deptno=e.deptno
group by d.deptno, d.dname
having count(e.empno) > 2;
–17.查询部门平均工资大于2000，且人数大于2的部门编号，部门
–名称，部门人数，部门平均工资，并按照部门人数升序排序。
select d.deptno 部门编号,
d.dname 部门名称,
count(e.empno) 部门人数,
avg(e.sal) 部门平均工资
from emp e, dept d
where d.deptno = e.deptno
group by d.deptno, d.dname
having avg(e.sal) > 2000 and count(e.empno) > 2
order by count(e.empno);
–18.查询部门平均工资在2500元以上的部门名称及平均工资。
select d.dname 部门名称, avg(e.sal) 平均工资
from emp e, dept d
where d.deptno = e.deptno
group by d.deptno, d.dname
having avg(sal)>2500;
–19.查询员工岗位中不是以“SA”开头并且平均工资在2500元以上的岗位及平均工资，
–并按平均工资降序排序。
select job 工作岗位, avg(sal) 平均工资
from emp
where job not like ‘SA%’
group by job
having avg(sal) > 2500
order by 平均工资 desc;
–20.查询部门人数在2人以上的部门名称、最低工资、最高工资,
–并对求得的工资进行四舍五入到整数位。
select d.dname 部门名称,
round(min(e.sal)) 最低工资,
round(max(e.sal)) 最高工资,
count(d.deptno) 部门数量
from emp e, dept d
where e.deptno = d.deptno
group by d.deptno, d.dname
having count(d.deptno) > 2;
–21.查询岗位不为SALESMAN，工资和大于等于2500的岗位及每种岗位的工资和。
select job 岗位,sum(sal) 工资和 from emp
where job<>‘SALESMAN’
group by job
having sum(sal)>=2500;
–22.显示经理号码和经理姓名，这个经理所管理员工的最低工资，
–没有经理的KING也要显示，不包括最低工资小于3000的，按最低工资由高到低排序。
select m.ename 经理姓名, m.mgr 经理号码,min(e.sal) 最低工资
from emp e
left join emp m
on (e.empno = m.mgr)
where e.sal > 3000
group by m.mgr,m.ename
order by 最低工资 desc;
–23.写一个查询，显示每个部门最高工资和最低工资的差额。
select d.dname 部门名称, (max(e.sal) - min(e.sal)) 工资差额
from emp e, dept d
where d.deptno = e.deptno
group by d.deptno, d.dname;