17/12/5分组函数

17/12/5分组函数

min, max, sum, avg, count

练习1
1.查询部门20的员工，每个月的工资总和和平均工资。

select sum(sal), avg(sal)
from emp
where deptno = 20

2.查询工作在CHICAGO的员工人数，最高工资及最低工资。

select *, max(sal), min(sal)
from emp
join dept on emp.deptno= dept.deptno
where dept.loc = 'CHICAGO'

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

SELECT  COUNT(DISTINCT job)
FROM emp

group by语句

练习2

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

SELECT EMP.deptno, dept.dname, count(EMP.deptno), max(EMP.SAL), min(EMP.SAL),
sum(sal), avg(SAL)
from EMP
join DEPT on emp.DEPTNO = DEPT.DEPTNO
GROUP BY EMP.DEPTNO, DEPT.DNAME 

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

SELECT  DEPT.DEPTNO, DEPT.dname, emp.job, count(EMP.ENAME), max(sal), min(SAL), sum(sal), avg(sal)
FROM EMP
join dept on DEPT.DEPTNO = EMP.DEPTNO
GROUP BY DEPT.DEPTNO, DEPT.dname, emp.job

3.查询每个经理所管理的人数，经理编号，经理姓名，要求包括没有经理的人员信息。

SELECT count(woker.ENAME), MANAGER.EMPNO, MANAGER.ENAME
FROM emp woker
left join emp manager on WOKER.mgr = MANAGER.EMPNO
GROUP BY  MANAGER.EMPNO, MANAGER.ENAME

having语句

(组函数的筛选判断)

SELECT语句执行过程：

-1. 通过FROM子句中找到需要查询的表；
-2. 通过WHERE子句进行非分组函数筛选判断；
-3. 通过GROUP BY子句完成分组操作；
-4. 通过HAVING子句完成组函数筛选判断；
-5. 通过SELECT子句选择显示的列或表达式及组函数；
-6. 通过ORDER BY子句进行排序操作。

练习3

1. 查询部门人数大于2的部门编号，部门名称，部门人数。

select emp.deptno, dept.dname, count(ename)
from emp
join dept on emp.deptno = dept.deptno 
group by emp.deptno, dept.dname
having count(ename) > 2

2.查询平均工资大于2000，且人数大于2的部门编号，部门名称，部门人数，部门平均工资，并按照部门人数升序排序。

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

组函数的嵌套

列：显示平均薪水的最大值

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

注：与单行函数不同，组函数只能嵌套两层
课后作业：

1. 查询部门平均工资在2500以上的部门名称及平均工资。

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

2.查询员工岗位中不是以“SA”开头并且平均工资在2500元以上的岗位及平局工资，并按平均工资降序排序。

select job, avg(sal) 
from emp
where job not like 'SA%'
group by job
having avg(sal)>2500
order by avg(sal) desc

3.查询部门人数在2人以上的部门名称、最低工资、最高工资、并对求得的工资进行四舍五入到整数位。

select 
dept.dname,round(min(sal),0), round(max(sal),0)
from emp
join dept on emp.deptno = dept.deptno
group by dept.dname
having count(ename)>2

4.查询岗位不为SALESMAN，工资和大于等于2500的岗位及每种岗位的工资和。

select job, sum(sal)
from emp
group by job
having job <> 'SALESMAN' and sum(sal)>= 2500

5.显示经理号码和经理姓名，这个经理所管理员工的最低工资，没有经理的KING也要显示，不包括最低工资小于3000的，按最低工资由高到低排序。

select manager.empno, manager.ename, min(woker.sal)
from emp woker
left join emp manager on woker.MGR = manager.EMPNO
group by manager.empno, manager.ename
having min(woker.sal)>=3000
order by min(woker.sal) DESC

6.写一个查询，显示每个部门最高工资和最低工资的差额。

select dept.dname, max(sal)-min(sal)
from emp
join dept on emp.deptno = dept.deptno
group by dept.dname