34题

最新推荐文章于 2022-06-12 11:55:20 发布

叶慕林

最新推荐文章于 2022-06-12 11:55:20 发布

阅读量1.2k

点赞数 1

本文链接：https://blog.csdn.net/Ye_mulin/article/details/108657049

版权

emp;
±------±-------±----------±-----±-----------±--------±--------±-------+
| EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO |
±------±-------±----------±-----±-----------±--------±--------±-------+
| 7369 | SMITH | CLERK | 7902 | 1980-12-17 | 800.00 | NULL | 20 |
| 7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 | 1600.00 | 300.00 | 30 |
| 7521 | WARD | SALESMAN | 7698 | 1981-02-22 | 1250.00 | 500.00 | 30 |
| 7566 | JONES | MANAGER | 7839 | 1981-04-02 | 2975.00 | NULL | 20 |
| 7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 | 1250.00 | 1400.00 | 30 |
| 7698 | BLAKE | MANAGER | 7839 | 1981-05-01 | 2850.00 | NULL | 30 |
| 7782 | CLARK | MANAGER | 7839 | 1981-06-09 | 2450.00 | NULL | 10 |
| 7788 | SCOTT | ANALYST | 7566 | 1987-04-19 | 3000.00 | NULL | 20 |
| 7839 | KING | PRESIDENT | NULL | 1981-11-17 | 5000.00 | NULL | 10 |
| 7844 | TURNER | SALESMAN | 7698 | 1981-09-08 | 1500.00 | 0.00 | 30 |
| 7876 | ADAMS | CLERK | 7788 | 1987-05-23 | 1100.00 | NULL | 20 |
| 7900 | JAMES | CLERK | 7698 | 1981-12-03 | 950.00 | NULL | 30 |
| 7902 | FORD | ANALYST | 7566 | 1981-12-03 | 3000.00 | NULL | 20 |
| 7934 | MILLER | CLERK | 7782 | 1982-01-23 | 1300.00 | NULL | 10 |
±------±-------±----------±-----±-----------±--------±--------±-------+
14 rows in set (0.00 sec)

salgrade;
±------±------±------+
| GRADE | LOSAL | HISAL |
±------±------±------+
| 1 | 700 | 1200 |
| 2 | 1201 | 1400 |
| 3 | 1401 | 2000 |
| 4 | 2001 | 3000 |
| 5 | 3001 | 9999 |
±------±------±------+
5 rows in set (0.00 sec)

1、取得每个部门最高薪水的人员名称
select e.ename,t.*
from emp e
join
(select deptno,max(sal) as maxsal
from emp
group by deptno) t
on e.sal = t.maxsal and e.deptno = t.deptno;

2、哪些人的薪水在部门的平均薪水之上
求部门平均薪水：
select deptno,avg(sal) avgsal
from emp
group by deptno;

合体：
select e.ename,e.sal,t.*
from emp e
join (select deptno,avg(sal) avgsal
from emp
group by deptno) t
on e.sal > t.avgsal and e.deptno = t.deptno;

3、取得部门中（所有人的）平均的薪水等级
求每个部门的平均薪水等级
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;

与所有人合体:
select e.ename,e.sal,e.deptno,avggra
from emp e
left join (select e.deptno,avg(s.grade) avggra
from emp e
join salgrade s
on e.sal between s.losal and s.hisal
group by e.deptno) t
on e.deptno = t.deptno;

4、不准用组函数（Max ），取得最高薪水【两种解决方案】
{第一种}：sal降序，limit 1【limit 0,1的意思就是从零取，取一个。可省略0直接写limit 1】
select ename,sal
from emp
order by sal
desc limit 1;

{第二种}：表的自联
首先查出来所有有比它本身大的工资的数据来
select distinct e1.sal
from emp e1
join emp e2
on e1.sal < e2.sal;

然后用一个not in查询，得出来的就是最大的
select ename,sal
from emp
where sal not in (select distinct e1.sal
from emp e1
join emp e2
on e1.sal < e2.sal);

5、取得平均薪水最高的部门的部门编号【两种解决方案】
{第一种}：desc+limit
求每个部门的平均薪水
select deptno,avg(sal) avgsal
from emp
group by deptno;

降序：
select deptno,avg(sal) avgsal
from emp
group by deptno
order by avg(sal)
desc limit 1;

{第二种}：
求每个部门的平均薪水
select deptno,avg(sal) avgsal
from emp
group by deptno;

！！！！！！！！！！！！下面为典型错误！
因为没有按照部门分组，所以没办法输出保证t.deptno和max(t.avgsal)是一一对应的
求最高薪水的部门的平均薪水的部门编号（即结果）【错误示范】
/*

select t.deptno,max(t.avgsal) maxsal
from
(select deptno,avg(sal) avgsal
from emp
group by deptno) t
where t.deptno = max…; 【这里也没办法在where后面加入分组函数max】
*/

第一步：求每个部门的平均薪水
select deptno,avg(sal) avgsal
from emp
group by deptno;

第二步：找出以上结果中avgsal最大的值。
select max(t.avgsal) maxsal
from (select deptno,avg(sal) avgsal
from emp
group by deptno) t;

第三步：再次依据第一步求出的平均薪水，与第二步求得的最大值进行“=”比较，
从而找出对应t.maxsal的部门编号
select deptno,avg(sal) avgsal
from emp
group by deptno
having avgsal = (select max(t.avgsalx) maxsal
from (select deptno,avg(sal) avgsalx
from emp
group by deptno) t);

值得深思的是，第三步的having后面跟着的是avgsal，可是如果我把这个avgsal后面的所有的avgsal都换一个名字（例如avgsal改为avgsalx，上方代码为修改后的测试代码）
证明这个里的avgsal不是从后面取的（也就是先执行的语句中取的），然而此时“having avgsal”仍能正常运行。但是这说明了第一行select语句中的重命名语句“select deptno,avg(sal) avgsal”中的“avg(sal) avgsal”是比having先运行的。
那么sql语句中的执行顺序究竟是什么呢？？？资料和课堂中说的是having在select之前。
那么难道说重命名语句自动先执行么？？

6、取得平均薪水最高的部门的部门名称
{第一种}：desc+limit
取部门平均薪水
select deptno,avg(sal)
from emp
group by deptno;

降序排列
select deptno,avg(sal) avgsal
from emp
group by deptno
order by avg(sal)
desc limit 1;

合体：
select d.dname,t.avgsal
from dept d
join (select deptno,avg(sal) avgsal
from emp
group by deptno
order by avg(sal)
desc limit 1) t
on t.deptno = d.deptno;

第一种的另外写法：
select d.dname,avg(e.sal) avgsal
from emp e
join dept d
on e.deptno = d.deptno
group by e.deptno
order by avg(e.sal)
desc limit 1;

下面为错误写法！！！：
/*
*select d.dname,avg(e.sal) avgsal
*from emp e
*join dept d
*group by e.deptno
*order by avg(e.sal)
*asc limit 1;
*/

select d.dname,max(t.avgsal) maxsal
from
(select deptno,avg(sal) avgsal
from emp
group by deptno) t
join dept d
on t.deptno = d.deptno;

{第二种}：max
取部门平均薪水
select avg(sal)
from emp
group by deptno;

求平均薪水的最大值（不能加上deptno了，因为无法对应上）
select max(t.avgsal)
from (select avg(sal) avgsal
from emp
group by deptno) t;

找到对应这个部门的薪水的【不写了，三层嵌套就ok】】
select d.dname,
from emp e
join dept d
on e.deptno = deptno
group by deptno
having avg(e.sal) =。。。。。。。。。。。。

7、求平均薪水的等级最低的部门的部门名称（注意事平均薪水的等级）
第一步：按照部门分类，得到每个部门的平均薪水
select deptno,avg(sal) as avgsal
from emp
group by deptno;

第二步：求这些薪水的对应的等级
select t.deptno,t.avgsal,s.grade
from salgrade s
join (select deptno,avg(sal) as avgsal
from emp
group by deptno) t
on t.avgsal between s.losal and s.hisal;

抛开一切，最低等级如何找？答案：平均薪水最低的对应的等级，等级也是一定是最低的
第三步：求最低的平均薪水
select avg(sal) as avgsal
from emp
group by deptno
order by avgsal
asc limit 1;

第四步：求这个最低的平均薪水所对应的等级
select grade
from salgrade
where (select avg(sal) as avgsal
from emp
group by deptno
order by avgsal
asc limit 1) between losal and hisal;

第五步：把第二步优化，加入部门名称
select t.*,s.grade
from salgrade s
join (select d.dname,avg(sal) as avgsal
from emp e
join dept d
on e.deptno = d.deptno
group by d.dname) t
on t.avgsal between s.losal and s.hisal;

第六步：第五步与第四步结合，条件是s.grade = 最低的等级
select t.*,s.grade
from salgrade s
join (select d.dname,avg(sal) as avgsal
from emp e
join dept d
on e.deptno = d.deptno
group by d.dname) t
on t.avgsal between s.losal and s.hisal
where s.grade = (select grade
from salgrade
where (select avg(sal) as avgsal
from emp
group by deptno
order by avgsal
asc limit 1) between losal and hisal);

8、取得比普通员工(员工代码没有在 mgr 字段上出现的) 的最高薪水还要高的领导人姓名
第一步：找出最高薪水
select max(sal) from emp where mgr is not null;

第二步：普通员工是谁？（不是mgr的人就是普通员工）
mgr有这些人：
select distinct mgr
from emp;

第三步:1,2合体，找出普通员工的最高薪水
注意：not in 在使用的时候，后面小括号中记得排除NULL
select max(sal) from emp where empno not in (select distinct mgr
from emp where mgr is not null);

第四步：找出高于第三步的最高薪水的人
select ename,sal from emp where sal > (select max(sal) from emp where empno not in (select distinct mgr
from emp where mgr is not null));

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、取得每个薪水等级有多少员工
分组count

第一步：找出每个员工的薪水等级
select e.ename,e.sal,s.grade
from emp e
join salgrade s
on e.sal between s.losal and s.hisal;

第二步：继续按照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、面试题：
有 3 个表 S(学生表)，C（课程表），SC（学生选课表）
S（SNO，SNAME）代表（学号，姓名）
C（CNO，CNAME，CTEACHER）代表（课号，课名，教师）
SC（SNO，CNO，SCGRADE）代表（学号，课号，成绩）
问题：
1，找出没选过“黎明”老师的所有学生姓名。
2，列出 2 门以上（含2 门）不及格学生姓名及平均成绩。
3，即学过 1 号课程又学过 2 号课所有学生的姓名。

14、列出所有员工及领导的姓名

15、列出受雇日期早于其直接上级的所有员工的编号,姓名,部门名称

16、列出部门名称和这些部门的员工信息, 同时列出那些没有员工的部门

17、列出至少有 5 个员工的所有部门

18、列出薪金比"SMITH" 多的所有员工信息

19、列出所有"CLERK"( 办事员) 的姓名及其部门名称, 部门的人数

20、列出最低薪金大于 1500 的各种工作及从事此工作的全部雇员人数

21、列出在部门"SALES"< 销售部> 工作的员工的姓名, 假定不知道销售部的部门编号.

22、列出薪金高于公司平均薪金的所有员工, 所在部门, 上级领导, 雇员的工资等级.

23、列出与"SCOTT" 从事相同工作的所有员工及部门名称

24、列出薪金等于部门 30 中员工的薪金的其他员工的姓名和薪金.

25、列出薪金高于在部门 30 工作的所有员工的薪金的员工姓名和薪金. 部门名称