动力节点MySQL34题精炼（带答案解析）（上）

1、取得每个部门最高薪水的人员名称

1.第一步，找到每个部门的最高薪水
select deptno,max(sal) as maxsal from emp group by deptno;
2.将这张表看作t表，表连接  
t.deptno=emp.deptno and t.maxsal=emp.sal
3.查询人员姓名
SELECT ename,t.*
FROM emp
join (select deptno,max(sal) as maxsal from emp group by deptno) t
on t.deptno=emp.deptno and t.maxsal=emp.sal;

2、哪些人的薪水在部门的平均薪水之上

1.找到平均薪资
SELECT deptno,avg(sal) as avgsal from emp GROUP BY deptno;
2.将上表看作t表，表关联
t.deptno=emp.deptno and emp.sal>t.avgsal
3.整合
SELECT t.*,e.ename,e.sal
FROM emp e
JOIN (SELECT deptno,avg(sal) as avgsal from emp GROUP BY deptno) t
on t.deptno=e.deptno and e.sal>t.avgsal;

3、取得部门中（所有人的）平均的薪水等级
第一步：找出每个人的薪水等级
emp e和salgrade s表连接。
连接条件：e.sal between s.losal and s.hisal
select 
        e.ename,e.sal,e.deptno,s.grade
from
        emp e
join
        salgrade s
on
        e.sal between s.losal and s.hisal;
第二步：基于以上的结果继续按照deptno分组，求grade的平均值。
SELECT e.deptno,avg(s.grade)
from emp e
join salgrade s
on
e.sal BETWEEN s.losal and hisal
GROUP BY e.deptno;

4、不准用组函数（Max ），取得最高薪水
SELECT ename,sal from emp order by sal DESC limit 1;

5、取得平均薪水最高的部门的部门编号
GROUP BY deptno
SELECT deptno,AVG(sal) as avgsal FROM emp GROUP BY deptno ORDER BY avgsal desc LIMIT 1;

6、取得平均薪水最高的部门的部门名称
表关联
t.deptno=d.deptno
SELECT t.*,d.dname
FROM dept d
join (SELECT deptno,AVG(sal) as avgsal FROM emp GROUP BY deptno ORDER BY avgsal desc LIMIT 1) t
on t.deptno=d.deptno;

7、求平均薪水的等级最低的部门的部门名称
第一步：找出每个部门的平均薪水
SELECT d.dname,avg(sal) as avgsal from emp e join dept d on e.deptno=d.deptno GROUP BY e.deptno
第二步：找出每个部门的平均薪水的等级
以上t表和salgrade表连接，条件：t.avgsal between s.losal and s.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 e.deptno) t
on t.avgsal between s.losal and s.hisal
ORDER BY s.grade ASC
LIMIT 1;

8、取得比普通员工(员工代码没有在 mgr 字段上出现的) 的最高薪水还要高的领导人姓名
SELECT DISTINCT mgr from emp WHERE mgr is not 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、取得薪水最高的前五名员工
ORDER BY sal desc LIMIT 5
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 名员工
ORDER BY hiredate desc limit 5  
SELECT ename,hiredate FROM emp ORDER BY hiredate desc limit 5;

12、取得每个薪水等级有多少员工

group by 在on后面
GROUP BY grade
sal BETWEEN losal and hisal
count(ename)
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）代表（学号，课号，成绩）
首先建表：

CREATE table s
(SNO VARCHAR(20) PRIMARY KEY,
sname varchar(20)
)
CREATE table C
(CNO VARCHAR(20) PRIMARY KEY,
cname varchar(20),
cteacher varchar(20) not null
)
CREATE table SC
(SNO VARCHAR(20) PRIMARY KEY,
CNO varchar(20),
scgrade varchar(20)
)

问题：
1，找出没选过“黎明”老师的所有学生姓名。

没选过not in

SELECT S.SNAME FROM S WHERE S.SNO NOT IN (
 SELECT SC.SNO FROM SC  INNER JOIN C ON SC.CNO = C.CNO WHERE C.CTEACHER = '黎明'
);
2，列出 2 门以上（含2 门）不及格学生姓名及平均成绩。

group by 后面跟的是多个字段，那么表示按照这些字段的不同组合分组查询。
3，即学过 1 号课程又学过 2 号课所有学生的姓名。在MySql中，NOT IN是用来查询不在某个值集的数据。

SELECT  S.SNAME from S join SC on S.SNO = SC.SNO WHERE SC.CNO in(1,2)
GROUP BY S.SNO,S.SNAME HAVING COUNT(DISTINCT SC.CNO) =2;
这条SQL语句不对是因为WHERE子句中的条件SC.CNO=1和SC.CNO=2是互相矛盾的，不可能同时成立。如果想要查询SC.CNO既等于1