数据库06笔记（分组group by）

– 查出比本部门平均工资高的员工信息
–查询10部门的平均薪资
select avg(sal) from emp where deptno=10;
select *
from emp e1
where sal > (select avg(sal) from emp e2 where e2.deptno = e1.deptno);

–分组 group by 分组字段
–select *|字段… from 表名 where 行过滤条件 group by 分组字段 having 组过滤信息 order by 排序字段;
–执行流程:from–where–group by–having–select–order by
–select ename en from emp where en=‘SMITH’;
select deptno from emp group by deptno;

– 找出20部门和30部门的最高工资
select max(sal) from emp group by deptno having deptno in(20,30);

– 求出每个部门的平均工资
select avg(sal) from emp group by deptno;

– 求出每个部门员工工资高于1000的的平均工资
select avg(sal) from emp where sal>1000 group by deptno;

– 求出10和20部门部门的哪些工资高于1000的员工的平均工资
select avg(sal) from emp where sal>1000 group by deptno having deptno in(20,30);
select avg(sal) from emp where sal>1000 and deptno in(20,30) group by deptno;

– 求出平均工资高于2000的部门编号和平均工资
select deptno,avg(sal) from emp group by deptno having avg(sal)>2000;

–查询 最低平均工资的部门编号
–先查出最低的部门平均薪资
select min(avg(sal)) from emp group by deptno;
select deptno
from emp
group by deptno
having avg(sal) = (select min(avg(sal)) from emp group by deptno);

----查看 高于本部门平均薪水员工姓名
select *
from emp e
where exists
(select deptno
from (select deptno, avg(sal) avgsal from emp group by deptno) e2
where e.deptno = e2.deptno
and e.sal > avgsal);

– 统计每个部门的员工数，和部门编号
select deptno,count(1) from emp group by deptno;
– 查询每个工种的最高工资以及工种
select job,max(sal) from emp group by job;
---- 查询平均工资在1500到2000之间的部门平均工资和部门编号
select avg(sal),deptno from emp group by deptno having avg(sal) between 1500 and 2000 ;

–decode(字段,字段值1,值2,字段值2,值3…,默认值)根据某个字段进行判断
–打印所用的部门名称,如果10,添加一个伪列,显示对应部门的大写名称
select deptno,decode(deptno,10,‘十’,20,‘二十’,30,‘三十’,‘其他’) from dept;

–给20部门的员工涨薪10%,打印员工信息
select ename,deptno,sal,decode(deptno,20,sal*1.1,sal) from emp;
–case when then else end
select ename,
sal,
deptno,
(case deptno
when 10 then
sal * 1.1
when 20 then
sal * 1.08
when 30 then
sal * 1.15
else
sal * 1.2
end) raisesal
from emp;

–测试数据
create table tb_student(
id number(4) ,
name varchar2(20),
course varchar2(20),
score number(5,2)
);
insert into tb_student values(1,‘张三’,‘语文’,81);
insert into tb_student values(2,‘张三’,‘数学’,75);
insert into tb_student values(3,‘李四’,‘语文’,81);
insert into tb_student values(4,‘李四’,‘数学’,90);
insert into tb_student values(5,‘王五’,‘语文’,81);
insert into tb_student values(6,‘王五’,‘数学’,100);
insert into tb_student values(7,‘王五’,‘英语’,90);
commit;
drop table tb_student cascade constraints;

select * from tb_student;

–使用一条sql语句，查询每门课都大于80分的学生姓名
select name
from tb_student
group by name
having min(score) > 80 and count(course) = (select count(distinct course)
from tb_student);
–数据 : name
–来源 : tb_student
–条件 : 每门课程(这个人所有课程最小分数)>80分 课程个数=3(select count(distinct course) from tb_student)
–查询课程数
select count(distinct course) from tb_student;
–求每个人课程最小分数
select min(score) from tb_student group by name;