1.2 练习题 ( 1-19 )
1、取得每个部门最高薪水的人员名称
1 各部门最高薪水
select deptno,max(sal) as maxsal from emp group by deptno;
+--------+--------+
| deptno | maxsal |
+--------+--------+
| 10 | 5000 |
| 20 | 3000 |
| 30 | 2850 |
+--------+--------+
select
e.ename,t.deptno,t.maxsal
from emp e
join (select deptno,max(sal) as maxsal from emp group by deptno) as t
on e.deptno = t.deptno and e.sal = t.maxsal;
+-------+--------+--------+
| ename | deptno | maxsal |
+-------+--------+--------+
| BLAKE | 30 | 2850 |
| SCOTT | 20 | 3000 |
| KING | 10 | 5000 |
| FORD | 20 | 3000 |
+-------+--------+--------+
2、哪些人的薪水在部门的平均薪水之上
select avg(sal),deptno from emp group by deptno;
+-----------+--------+
| avg(sal) | deptno |
+-----------+--------+
| 2916.6667 | 10 |
| 2175.0000 | 20 |
| 1566.6667 | 30 |
+-----------+--------+
select
e.ename,t.deptno,t.avgsal
from emp e
join (select avg(sal)as avgsal,deptno from emp group by deptno) as t
on e.deptno = t.deptno and e.sal>avgsal;
+-------+--------+-----------+
| ename | deptno | avgsal |
+-------+--------+-----------+
| KING | 10 | 2916.6667 |
| JONES | 20 | 2175.0000 |
| SCOTT | 20 | 2175.0000 |
| FORD | 20 | 2175.0000 |
| ALLEN | 30 | 1566.6667 |
| BLAKE | 30 | 1566.6667 |
+-------+--------+-----------+
3、取得部门中（所有人的）平均的薪水等级，如下：
1 先找出每个人的薪水等级
emp 和 salgrade 做表连接，条件e.sal between s.losal and s.hisal;
select
e.ename,e.deptno,e.sal,s.grade
from emp e
join salgrade s
on e.sal between s.losal and s.hisal
order by deptno;
+--------+--------+------+-------+
| ename | deptno | sal | grade |
+--------+--------+------+-------+
| KING | 10 | 5000 | 5 |
| CLARK | 10 | 2450 | 4 |
| MILLER | 10 | 1300 | 2 |
求10部门的平均薪水等级 = (5+4+2)/3
| JONES | 20 | 2975 | 4 |
| ADAMS | 20 | 1100 | 1 |
| SMITH | 20 | 800 | 1 |
| FORD | 20 | 3000 | 4 |
| SCOTT | 20 | 3000 | 4 |
求20部门的平均薪水等级 = (4+1+1+1+4+4)/5
| TURNER | 30 | 1500 | 3 |
| MARTIN | 30 | 1250 | 2 |
| BLAKE | 30 | 2850 | 4 |
| JAMES | 30 | 950 | 1 |
| ALLEN | 30 | 1600 | 3 |
| WARD | 30 | 1250 | 2 |
求30部门的平均薪水等级 = (3+2+4+1+3+2)/6
+--------+--------+------+-------+
2 基于以上表继续做分组统计，求grade的平均值
（而不是当做临时表再和其他表做连接）
select
e.deptno,avg(s.grade)
from emp e
join salgrade s
on e.sal between s.losal and s.hisal
group by e.deptno;
+--------+--------------+
| deptno | avg(s.grade) |
+--------+--------------+
| 10 | 3.6667 |
| 20 | 2.8000 |
| 30 | 2.5000 |
+--------+--------------+
4、不准用组函数（Max），取得最高薪水
1 用limit方法
select ename,sal from emp order by sal desc limit 1;
+-------+------+
| ename | sal |
+-------+------+
| KING | 5000 |
+-------+------+
2 用自连接方法
select sal from emp;
a表
+------+
| sal |
+------+
| 800 |
| 1600 |
| 1250 |
| 2975 |
| 1250 |
| 2850 |
| 2450 |
| 3000 |
| 5000 |
| 1500 |
| 1100 |
| 950 |
| 3000 |
| 1300 |
+------+
b表
+------+
| sal |
+------+
| 800 |
| 1600 |
| 1250 |
| 2975 |
| 1250 |
| 2850 |
| 2450 |
| 3000 |
| 5000 |
| 1500 |
| 1100 |
| 950 |
| 3000 |
| 1300 |
+------+
select
distinct a.sal
from emp a
join emp b
on a.sal < b.sal;
查询结果了排除掉了a表的最大值，因为a表里的5000不小于b表的任何值，其他值都小于b表的5000，所以都会查询出来
+------+
| sal |
+------+
| 800 |
| 1250 |
| 1500 |
| 1100 |
| 950 |
| 1300 |
| 1600 |
| 2850 |
| 2450 |
| 2975 |
| 3000 |
+------+
第二部 用 not in
select ename,sal from emp where sal not in(select distinct a.sal from emp a join emp b on a.sal < b.sal);
+-------+------+
| ename | sal |
+-------+------+
| KING | 5000 |
+-------+------+
5、取得平均薪水最高的部门的部门编号
1 取得部门的平均薪水
select deptno,avg(sal) as avgsal from emp group by deptno;
+--------+-----------+
| deptno | avgsal |
+--------+-----------+
| 10 | 2916.6667 |
| 20 | 2175.0000 |
| 30 | 1566.6667 |
+--------+-----------+
方案1：降序取第一条
select deptno,avg(sal) as avgsal from emp group by deptno order by avgsal desc limit 1;
方案2 max函数
1 先找出各部门的平均薪水
select deptno,avg(sal) as avgsal from emp group by deptno;
+--------+-----------+
| deptno | avgsal |
+--------+-----------+
| 10 | 2916.6667 |
| 20 | 2175.0000 |
| 30 | 1566.6667 |
+--------+-----------+
2 筛选出avgsal最大的值
select max(e.avgsal) from (select deptno,avg(sal) as avgsal from emp group by deptno)e;
3 和 dept 表连接 找出部门编号
select
avg(sal) as avgsal
from emp
group by deptno
having avgsal=(select max(e.avgsal) from (select deptno,avg(sal) as avgsal from emp group by deptno)e); //查询没有结果 老师讲的SQL也没结果
6、取得平均薪水最高的部门的部门名称
limit方案 1
select avg(sal),deptno from emp group by deptno;
+-----------+--------+
| avg(sal) | deptno |
+-----------+--------+
| 2916.6667 | 10 |
| 2175.0000 | 20 |
| 1566.6667 | 30 |
+-----------+--------+
-------------------------------------------------------------------------
select
d.dname,e.avgsal
from (select avg(sal) avgsal,deptno from emp group by deptno) as e
join dept d
on e.deptno = d.deptno
order by e.avgsal desc
limit 1;
-------------------------------------------------------------------------
limit方案2
select
avg(e.sal)as avgsal,d.dname
from emp e
join dept d
on e.deptno = e.deptno
group by d.dname
order by avgsal limit 1;
7、求平均薪水的等级最低 的部门的部门名称
用limit不严谨 有可能两个部门都是一个等级
比如：
a部门平均工资800 工资等级1
b部门平均工资900 工资等级也是1
用limit就不太准确了
------------------------------------------------------------------------------------
推荐做法：
1 先找出部门平均薪水，按照部门名称分组
select
d.dname,avg(e.sal)as avgsasl
from emp e
join dept d
on e.deptno = d.deptno
group by d.dname;
+------------+------------+
| dname | avg(e.sal) |
+------------+------------+
| ACCOUNTING | 2916.6667 |
| RESEARCH | 2175.0000 |
| SALES | 1566.6667 |
+------------+------------+
2 找出部门平均薪水对应的等级
select
e.dname,e.avgsal,s.grade
from (select d.dname,avg(e.sal)as avgsal from emp e join dept d on e.deptno = d.deptno group by d.dname) e
join salgrade s
on e.avgsal between s.losal and hisal
+------------+-----------+-------+
| dname | avgsal | grade |
+------------+-----------+-------+
| ACCOUNTING | 2916.6667 | 4 |
| RESEARCH | 2175.0000 | 4 |
| SALES | 1566.6667 | 3 |
+------------+-----------+-------+
3 添加where条件 grade = 3；(select grade from salgrade where (select avg(sal) as avgsal from emp group by deptno order by avgsal limit 1) between losal and hisal)
select
e.dname,e.avgsal,s.grade
from (select d.dname,avg(e.sal)as avgsal from emp e join dept d on e.deptno = d.deptno group by d.dname) e
join salgrade s
on e.avgsal between s.losal and hisal
where
s.grade = (select grade from salgrade where (select avg(sal) as avgsal from emp group by deptno order by avgsal limit 1) between losal and hisal);
这一段SQL 执行结果 就是 3
+-------+-----------+-------+
| dname | avgsal | grade |
+-------+-----------+-------+
| SALES | 1566.6667 | 3 |
+-------+-----------+-------+
-------------------------------------------------------------------------------------------------------------------------
抛开上面的，最低等级如何找：
1 找出最低的平均薪水
select avg(sal) as avgsal from emp group by deptno order by avgsal limit 1;
+-----------+
| avgsal |
+-----------+
| 1566.6667 |
+-----------+
2 找到最低薪水对应的等级
select grade from salgrade where (select avg(sal) as avgsal from emp group by deptno order by avgsal limit 1) between losal and hisal;
这段sql 就是 1566.6667 这个值
+-------+
| grade |
+-------+
| 3 |
+-------+
平均薪水最低的一定是等级最低
平均薪水不是最低，但也可能等级最低
平均薪水是800 平均薪水是900 都是最低级别1级（700-1200），就是说一个等级下可能有多个值
8、取得 比普通员工的最高薪水 还要高的领导姓名
普通员工就是(员工代码没有在mgr字段上出现的)
1 先找出所有领导 as t表
select distinct mgr from emp;
+------+
| mgr |
+------+
| 7902 |
| 7698 |
| 7839 |
| 7566 |
| NULL |
| 7788 |
| 7782 |
+------+
员工编号没有在t表出现的都是 普通员工
2 找出普通员工的最高薪水
重点：使用not in 的时候，后面小括号中记得排除null。如何排除：is not null
select max(sal) as maxsal from emp where empno not in (select distinct mgr from emp where mgr is not null);
排除null
+--------+
| maxsal |
+--------+
| 1600 |
+--------+ 普通员工最高薪水是1600元
3 找出比工资大于 1600的 select ename form emp where sal>1600;
select
ename
from emp
where
sal>(select max(sal) as maxsal from emp where empno not in (select distinct mgr from emp where mgr is not null));
+-------+
| ename |
+-------+
| JONES |
| BLAKE |
| CLARK |
| SCOTT |
| KING |
| FORD |
+-------+
比普通员工最高工资还要高的一定是领导？对吗 (#^.^#)
9、取得薪水最高的前五名员工
select ename,sal from emp order by sal desc limit 5;
10、取得薪水最高的第六到第十名员工
select ename,sal from emp order by sal desc limit 5,5;
11、取得最后入职的5名员工
select ename,hiredate from emp order by hiredate desc limit 5;
12、取得每个薪水等级有多少员工
分组统计
1 找出每个员工的薪水等级
select
e.ename,s.grade,e.sal
from emp e
join salgrade s
on e.sal between s.losal and hisal;
+--------+-------+------+
| ename | grade | sal |
+--------+-------+------+
| SMITH | 1 | 800 |
| ALLEN | 3 | 1600 |
| WARD | 2 | 1250 |
| JONES | 4 | 2975 |
| MARTIN | 2 | 1250 |
| BLAKE | 4 | 2850 |
| CLARK | 4 | 2450 |
| SCOTT | 4 | 3000 |
| KING | 5 | 5000 |
| TURNER | 3 | 1500 |
| ADAMS | 1 | 1100 |
| JAMES | 1 | 950 |
| FORD | 4 | 3000 |
| MILLER | 2 | 1300 |
+--------+-------+------+
2 按grade分组统计
select
s.grade,count(*)
from emp e
join salgrade s
on e.sal between s.losal and s.hisal
group by s.grade;
13、面试题：
学生表：student （学号 sno，姓名 sname）
课程表：class （课号cno，课号cname，老师cteacher）
选课表：score（学号sno，课号cno，成绩grade）
学生表 s
select * from s;
+-----+--------+
| sno | sname |
+-----+--------+
| 1 | 张三 |
| 2 | 李四 |
| 3 | 王五 |
| 4 | 赵六 |
+-----+--------+
课程表 c
select * from c;
+-----+--------+-----------+
| cno | cname | teacher |
+-----+--------+-----------+
| 1 | java | 王老师 |
| 2 | C++ | 张老师 |
| 3 | C# | 李老师 |
| 4 | mysql | 周老师 |
| 5 | oracle | 黎明 |
+-----+--------+-----------+
学生选课表 score
select * from sc;
+-----+-----+---------+
| sno | cno | grade |
+-----+-----+---------+
| 1 | 1 | 30.0 |
| 1 | 2 | 50.0 |
| 1 | 3 | 80.0 |
| 1 | 4 | 80.0 |
| 1 | 5 | 70.0 |
| 2 | 2 | 80.0 |
| 2 | 3 | 50.0 |
| 2 | 4 | 70.0 |
| 2 | 5 | 80.0 |
| 3 | 1 | 60.0 |
| 3 | 2 | 70.0 |
| 3 | 3 | 60.0 |
| 4 | 3 | 50.0 |
| 4 | 5 | 40.0 |
+-----+-----+---------+
问题：
1，找出没选过“黎明”老师的所有学生姓名。
1、先找出 黎明 教授课程的编号 编号等于5
select cno from class where teacher = '黎明';
+-----+
| cno |
+-----+
| 5 |
+-----+
2，查找成绩表 通过课程号5 找出学号
select sno from score where cno = (select cno from class where teacher = '黎明');
+-----+
| sno |
+-----+
| 1 |
| 2 |
| 4 |
+-----+
3 通过学号找出学生姓名
select sname from student where sno not in (select sno from score where cno = (select cno from class where teacher = '黎明'));
+--------+
| sname |
+--------+
| 王五 |
+--------+
2，列出 2 门以上（含2 门）不及格学生姓名及平均成绩。
3，即学过 1 号课程又学过 2 号课所有学生的姓名。
1 学过1号课程的同学
select sno from score where cno=1;
+-----+
| sno |
+-----+
| 1 |
| 3 |
+-----+
2 学过2号课程的同学
select sno from score where cno=2;
+-----+
| sno |
+-----+
| 1 |
| 2 |
| 3 |
+-----+
3 where条件：学过课程 1 并且学过课程 2
select sno,sname from score where cno=1 and cno in (select sno from score where cno=2);
学过课程1的 学过课程2个
+-----+
| sno |
+-----+
| 1 |
| 3 |
+-----+
4 表连接 条件sno相等
select
s.sno,c.sname
from score s
join student c
on s.sno = c.sno
where cno=1 and cno in (select sno from score where cno=2);
+-----+--------+
| sno | sname |
+-----+--------+
| 1 | 张三 |
| 3 | 王五 |
+-----+--------+
14、列出所有员工及领导的姓名
select
e.ename as '员工',c.ename as '领导'
from emp e
left join emp c
on e.mgr = c.empno;
+--------+--------+
| 员工 | 领导 |
+--------+--------+
| SMITH | FORD |
| ALLEN | BLAKE |
| WARD | BLAKE |
| JONES | KING |
| MARTIN | BLAKE |
| BLAKE | KING |
| CLARK | KING |
| SCOTT | JONES |
| KING | NULL |
| TURNER | BLAKE |
| ADAMS | SCOTT |
| JAMES | BLAKE |
| FORD | JONES |
| MILLER | CLARK |
+--------+--------+
用左外连接，用内连接 King 就匹配不上
15、列出受雇日期早于其直接上级的所有员工的编号,姓名,部门名称
员工表e 领导表c 部门表d
select
e.ename as '员工',c.ename as '领导'
from emp e
join emp c
on e.mgr = c.empno
join dept d
on e.deptno = d.deptno
where e.hiredate<c.hiredate;
+--------+--------+
| 员工 | 领导 |
+--------+--------+
| CLARK | KING |
| SMITH | FORD |
| JONES | KING |
| ALLEN | BLAKE |
| WARD | BLAKE |
| BLAKE | KING |
+--------+--------+
16、列出部门名称和这些部门的员工信息,同时列出那些没有员工的部门.
select
e.*,d.dname
from emp e
right join dept d
on e.deptno = d.deptno;//考察的是right join 的掌握程度
17、列出至少有5个员工的所有部门
按照部门编号分组 计数 过滤 >=5;
select deptno from emp group by deptno having count(*)>5;//这里自己没有想到
18、列出薪金比"SMITH"多的所有员工信息.
1 找出 SMITH 的薪金
select sal from emp where ename = 'SMITH';
+------+
| sal |
+------+
| 800 |
+------+
2 找出比800高的员工
select * from emp where sal>(select sal from emp where ename = 'SMITH'); 有点简单
19、列出所有 " CLERK " ( 办事员 ) 的姓名及其 部门名称,部门的人数
1 找出所有 办事员 和 部门名称
select
e.ename,e.job,d.dname,e.deptno
from emp e
join dept d
on e.deptno = d.deptno
where e.job = '办事员';
+--------+-------+------------+--------+
| ename | job | dname | deptno |
+--------+-----------+-------------+
| SMITH | 办事员 | RESEARCH | 20 |
| ADAMS | 办事员 | RESEARCH | 20 |
| JAMES | 办事员 | SALES | 30 |
| MILLER | 办事员 | ACCOUNTING | 10 |
+--------+-----------+-------------+
2 找出每个部门的人数 as t表
select deptno,count(*) as deptnocount from emp group by deptno;
+--------+-------------+
| deptno | deptnocount |
+--------+-------------+
| 10 | 3 |
| 20 | 5 |
| 30 | 6 |
+--------+-------------+
3 把t表和上面的表连接
select
e.ename,e.job,d.dname,e.deptno,t.deptnocount as '部门人数'
from emp e
join dept d
on e.deptno = d.deptno
join (select deptno,count(*) as deptnocount from emp group by deptno) as t
on e.deptno = t.deptno
where e.job = '办事员';
+--------+-----------+--------+--------+----------+
| ename | job | dname | deptno | 部门人数 |
+--------+-------+------------+--------+----------+
| SMITH | 办事员 | RESEARCH | 20 | 5 |
| ADAMS | 办事员 | RESEARCH | 20 | 5 |
| JAMES | 办事员 | SALES | 30 | 6 |
| MILLER | 办事员 | ACCOUNTING | 10 | 3 |
+--------+-----------+------------+--------+-----+
这个题有难度
浙公网安备 33010602011771号