Oracle——4.分组函数

目录

组函数

练习1

分组数据

练习2

having

练习3

函数嵌套

练习4

子查询

多行子查询

作业

---

组函数

分组函数 也叫作 聚合函数

avg 平均数
count 个数
max 最大数
min 最小数
sum 求和

-- avg(列名) 表示这个列中 所有符合条件的信息，来计算平均值
select avg(sal) from emp where deptno=10
-- max,min 计算这个列中的最大值和最小值
select max(sal),min(sal) from emp
-- sum(列名) 表示这个列的总和是多少
select sum(sal) from emp
-- * 表示所有的列
-- 可以把*修改为 某个列名
-- 还可以修改为 简单的数字
-- count（列名） 表示多少条数据，或多少个数据
select count(*) from emp where deptno=10
select count(empno) from emp where deptno=10
select count(1) from emp where deptno=10

练习1

1.查询部门20的员工，每个月的工资总和及平均工资
2.查询工作在CHICAGO的员工人数，最高工资及最低工资
3.查询员工表中一共有几种岗位类型

------------------练习1---------------------
-- 1.查询部门20的员工，每个月的工资总和及平均工资
select sum(sal),avg(sal) from emp where deptno=20
-- 2.查询工作在CHICAGO的员工人数，最高工资及最低工资
select count(1),max(sal),min(sal) from emp e,dept d 
where e.deptno=d.deptno and loc='CHICAGO'
-- 3.查询员工表中一共有几种岗位类型
-- distinct去掉重复数据
select count(distinct job) from emp

---

分组数据

group by 列名，表示针对于某个列 进行分组

如果进行了分组（group by）那么 在select查询中，只能出现 分组的列信息和聚合函数（不能多写）
 

-- 分组数据
-- 一共有多少人
select count(1) from emp
-- 每个部门有多少人
-- group by 列名，表示针对于某个列 进行分组
-- 如果进行了分组（group by）那么 在select查询中，只能出现 分组的列信息和聚合函数
select deptno,count(1) from emp
group by deptno
-- 针对于多个列进行分组
select deptno,job,count(1) from emp
group by deptno,job

select deptno,count(1) from emp
where sal>1200
group by deptno

练习2

1.查询每个部门的部门编号，部门名称，部门人数，最高工资，最低工资，工资总和，平均工资。
2.查询每个部门，每个岗位的部门编号，部门名称，岗位名称，部门人数，最高工资，最低工资，工资总和，平均工资
3.查询 每个经理所管理的人数，经理编号，经理姓名，要求包括没有经理的人员信息
 

----------------------练习2------------------------
-- 1.查询每个部门的部门编号，部门名称，部门人数，最高工资，最低工资，工资总和，平均工资。
select e.deptno,dname,count(1),max(sal),min(sal),sum(sal),avg(sal) from emp e,dept d
where e.deptno=d.deptno
group by e.deptno,dname
-- 2.查询每个部门，每个岗位的部门编号，部门名称，岗位名称，部门人数，最高工资，最低工资，工资总和，平均工资
select e.deptno,dname,job,count(1),max(sal),min(sal),sum(sal),avg(sal) from emp e,dept d
where e.deptno=d.deptno
group by e.deptno,dname,job
-- 3.查询 每个经理所管理的人数，经理编号，经理姓名，要求包括没有经理的人员信息
select * from emp

having

针对于 group by分组之后，再次进行筛选的（条件处理的）

-- 每个部门的人数 大于2个人的部门显示出来
-- having 是针对于 group by分组之后，再次进行筛选的（条件处理的）
-- having 不能单独 使用，需要和group by一起配合使用
select deptno,count(1) from emp
group by deptno
having count(1)>2
-- 求每个部门的平均工资，大于1200的部门显示出来
select deptno,avg(sal) from emp
group by deptno
having avg(sal)>1200
-- select的执行顺序
select deptno 列名,count(1) --5.需要哪些列的数据                必填
from emp                    --1.先找到表                       必填
where sal>1000              --2.对这个表进行初步的筛选（条件）  可选
group by deptno             --3.对初步筛选之后的数据，进行分组  可选
having count(1)>2           --4.对分组之后的结果，再次进行筛选  可选，是和group by配合使用的
order by deptno             --6.对这些列来进行排序              可选

练习3

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

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

---

函数嵌套

-- 函数嵌套
select max(avg(sal)) from emp
group by deptno

练习4

1.查询部门平均工资在2500元以上的部门名称及平均工资。
2.查询员工岗位中不是以“SA”开头并且平均工资在2500元以上的岗位及平均工资，并按平均工资降序排序
3.查询部门人数在2人以上的部门名称、最低工资、最高工资，并对求得的工资进行四舍五入到整数位

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

---

子查询

子查询需要用 小括号括起来

-- 子查询
-- 子查询需要用 小括号括起来
-- 在执行语句时，先执行 子查询中的信息（小括号中的信息），之后把子查询的结果当做是一个条件，于父查询一起联合查出结果
-- 如果使用的是 比较运算符，那么子查询必须返回一行一列的信息，这样才能进行比较
select * from emp where deptno=(select deptno from dept where loc='CHICAGO')
-- 比某个人的工资高的所有人的信息，用子查询写
select * from emp
where sal>(select sal from emp where ename='SCOTT')

多行子查询

-- 多行子查询
-- in(范围) 表示可以在in括号中的范围里进行查询，可以是多行形式出现
select * from emp where deptno in(select deptno from dept)   --（10,20,30,40,60）

-- 子查询不单纯的可以放在条件中，还能用子查询的结果，当做一个表来使用
select deptno,count(1) from emp
group by deptno
select * from dept d,(select deptno,count(1) from emp group by deptno having count(1)>3) a
where d.deptno=a.deptno

select * from emp where sal=(select min(sal) from emp)

---

作业

1.查询工资高于编号为7782的员工工资，并且和7369号员工从事相同工作的员工的编号、姓名及工资。
2.查询工资最高的员工姓名和工资。
3.查询部门最低工资高于10号部门最低工资的部门的编号、名称及部门最低工资。
4.查询员工工资为其部门最低工资的员工的编号和姓名及工资。
5.显示经理是KING的员工姓名，工资。
6.显示比员工SMITH参加工作时间晚的员工姓名，工资，参加工作时间。
7.使用子查询的方式查询哪些职员在NEW YORK工作。
8.写一个查询显示和员工SMITH工作在同一个部门的员工姓名，雇用日期，查询结果中排除SMITH。
9.写一个查询显示其工资比全体职员平均工资高的员工编号、姓名。
10.写一个查询显示其上级领导是King的员工姓名、工资。
11.显示所有工作在RESEARCH部门的员工姓名，职位。
12.查询每个部门的部门编号、平均工资，要求部门的平均工资高于部门20的平均工资。
13.查询大于自己部门平均工资的员工姓名，工资，所在部门平均工资，高于部门平均工资的额度。
14. 列出至少有一个雇员的所有部门
15. 列出薪金比"SMITH"多的所有雇员
16. 列出入职日期早于其直接上级的所有雇员
17. 找员工姓名和直接上级的名字
18. 显示部门名称和人数
19. 显示每个部门的最高工资的员工
20. 显示出和员工号7369部门相同的员工姓名，工资
21. 显示出和姓名中包含"W"的员工相同部门的员工姓名
22. 显示出工资大于平均工资的员工姓名，工资
23. 显示出工资大于本部门平均工资的员工姓名，工资
24. 显示每位经理管理员工的最低工资，及最低工资者的姓名
25. 显示比工资最高的员工参加工作时间晚的员工姓名，参加工作时间
26. 显示出平均工资最高的的部门平均工资及部门名称

------------------------------作业------------------------------------
-- 1.查询工资高于编号为7782的员工工资，并且和7788号员工从事相同工作的员工的编号、姓名及工资。
select empno,ename,sal,job from emp
where sal>(select sal from emp where empno=7782)
and job=(select job from emp where empno=7788)
-- 2.查询工资最高的员工姓名和工资。
select sal,ename from emp where sal=(select max(sal) from emp)
-- **3.查询部门最低工资高于10号部门最低工资的部门的编号、名称及部门最低工资。
select e.deptno,dname,min(sal) from emp e,dept d
where e.deptno=d.deptno
group by e.deptno,dname
having min(sal)>=(select min(sal) from emp where deptno=10)
-- ***04.查询员工工资为其部门最低工资的员工的编号和姓名及工资。
-- ××select empno,ename,min(sal) from (select min(sal) from emp group by deptno)
select * from emp e,(select deptno,min(sal) sal from emp group by deptno) tmp
where e.deptno=tmp.deptno and e.sal=tmp.sal
-- 5.显示经理是KING的员工姓名，工资。
select ename,sal from emp
where mgr=(select empno from emp where ename='KING')
-- 6.显示比员工SMITH参加工作时间晚的员工姓名，工资，参加工作时间。
select ename,sal,hiredate from emp 
where hiredate>(select hiredate from emp where ename='SMITH')
-- 7.使用子查询的方式查询哪些职员在NEW YORK工作。
select * from emp e,dept d where e.deptno=d.deptno and loc='NEW YORK'
select * from emp where deptno=(select deptno from dept where loc='NEW YORK')
-- 8.写一个查询显示和员工SMITH工作在同一个部门的员工姓名，雇用日期，查询结果中排除SMITH。
select ename,hiredate from emp 
where deptno=(select deptno from emp where ename='SMITH')
and ename<>'SMITH'
-- 9.写一个查询显示其工资比全体职员平均工资高的员工编号、姓名。
select empno,ename from emp 
where sal>(select avg(sal) from emp)
-- 10.写一个查询显示其上级领导是King的员工姓名、工资。
select e.ename,e.sal,m.ename from emp e,emp m 
where e.mgr=m.empno 
and e.mgr=(select empno from emp where ename='KING')
-- 11.显示所有工作在RESEARCH部门的员工姓名，职位。
select e.ename,e.job,d.dname from emp e,dept d 
where e.deptno=d.deptno 
and d.dname='RESEARCH'

select * from emp where deptno=(select deptno from dept where dname='RESEARCH')

-- 12.查询每个部门的部门编号、平均工资，要求部门的平均工资高于部门20的平均工资。
select deptno,avg(sal) from emp e 
group by deptno
having avg(sal)>(select avg(sal) from emp where deptno=20)
-- **13.查询大于自己部门平均工资的员工姓名，工资，所在部门平均工资，高于部门平均工资的额度。
select * from emp e,(select deptno,avg(sal) sal from emp group by deptno) a
where e.deptno=a.deptno and e.sal>a.sal
-- 14. 列出至少有一个雇员的所有部门
select deptno,count(1) from emp 
group by deptno
having count(1)>1
-- 15. 列出薪金比"SMITH"多的所有雇员
select * from emp where sal>(select sal from emp where ename='SMITH')
-- **16. 列出入职日期早于其直接上级的所有雇员
select * from emp e,emp m
where e.mgr=m.empno
and e.hiredate<m.hiredate
-- 17. 找员工姓名和直接上级的名字
select e.ename,m.ename from emp e,emp m where e.mgr=m.empno
-- *18. 显示部门名称和人数
select e.deptno,d.dname,count(1) from emp e,dept d
where e.deptno=d.deptno
group by e.deptno,d.dname
-- **19. 显示每个部门的最高工资的员工
select * from emp e,(select deptno,max(sal) sal from emp group by deptno) tmp
where e.deptno=tmp.deptno and e.sal=tmp.sal
-- 20. 显示出和员工号7369部门相同的员工姓名，工资
select ename,sal from emp 
where job=(select job from emp where empno=7369)
-- 21. 显示出和姓名中包含"W"的员工相同部门的员工姓名
select * from emp
where job in(select distinct job from emp where ename like '%W%')
-- 22. 显示出工资大于平均工资的员工姓名，工资
select ename,sal from emp where sal>(select avg(sal) from emp)
-- **23. 显示出工资大于本部门平均工资的员工姓名，工资
select e.ename,e.sal from emp e,(select deptno,avg(sal) sal from emp group by deptno) m
where e.deptno=m.deptno
and e.sal>m.sal
-- **24. 显示每位经理管理员工的最低工资，及最低工资者的姓名
select * from emp e,
(select mgr, min(sal) sal from emp group by mgr having mgr is not null) tmp
where e.mgr=tmp.mgr
and e.sal=tmp.sal
-- ***25. 显示比工资最高的员工参加工作时间晚的员工姓名，参加工作时间
select * from emp 
where hiredate>(select hiredate from emp where sal=(select max(sal) from emp))
-- ****26. 显示出平均工资最高的的部门平均工资及部门名称
select dname,avg(sal) from emp e,dept d where e.deptno=d.deptno
group by dname
having avg(sal)=(select max(avg(sal)) from emp group by deptno)