1.取得每个部门最高薪水的人员名称
第一步：按照部门编号分组，找出每一组最大值
select deptno,max(sal) as maxsal from emp group by deptno;
第二部：将以上的查询结果当做一张临时表t，t和emp表连接，条件是：t.deptno == emp.deptno and t.maxsal == emp.sal
select e.name,t.*
from emp e
join (select deptno,max(sal) as maxsal from emp group by deptno) t
on t.deptno == e.deptno and t.maxsal == e.sal;
2.哪些人的薪水在平均薪水之上
第一步：找出每个部门的平均薪水
select deptno,avg(sal) as avgsal from emp group by deptno;
第二步：将以上查询结果当做t表，t表和emp表连接，条件：部门标号相同，并且emp的sal大于t的avgsal
select e.name,e.sal,t.* from emp e
join (select deptno,avg(sal) as avgsal from emp group by deptno) as t
on t.deptno == e.deptno ans e.sal > t.avgsal;
3.取得部门中（所有人的）平均薪水等级，如下：
第一步：找出每个人的薪水等级
select
e.ename,e.sal,e.deptno,e.grade
from emp e
join salgrade s
on e.sal between s.losal and s.hisal;
第二步：基于以上的结果
select e.deptno,avg(t.grade)
from emp e
join salgrade s on e.sal between s.losal and s.hisal
group by e.deptno;4.不准用组函数（Max），取得最高薪水
第一种：sal降序，limit 1
select ename,sal from emp order by sal desc limit 1;
第二种：表的自连接
select sal from emp where sal not in (
select distinct sal from a emp
join b emp
on a.sal < b.sal);
第三种：select max(sal) from emp;
5.取得平均薪水最高的部门编号
第一种方案：
第一步:找出每个部门的平均薪水
select avg(sal) as avgsal from emp group by deptno;
第二步：降序取第一个
select e.deptno avg(sal) as avgsal from emp group by emp.deptno oeder by avgsal desc limit 1;
第二种方法：max
select deptno,avg(sal) as avgsal from emp;
select deptno,avg(sal) as avgsal
from emo
group by deptno
having avgsal = (select max(t.avgsal) from (select avg(sal) as avgsal fron emp group by deptno) t;
6.取得平均薪水最高部门的部门名称
select
e.deptno,avg(e.sal) as avgsal
from emp e
join dept d
on e.deptno = d.deptno
group by d.dname
order by
avgsal desc
limit 1;
7.求平均薪水的等级最低的部门的部门名称
第一步：按照部门的名称分组，找出每个部门的平均薪水
select deptno,avg(sal) as avgsal as from emp group by
deptno;
第二步：找出每个部门的平均薪水的等级
以上t表和salgrade表连接，条件：t.avgsal between s.losal and s.hisal;
select t.*,s.grade
from (select deptno,avg(sal) as avgsal as from emp group by deptno) t
join salgrade s
on t.avgsal between s.losal and s.hisal;
第三步：
平均薪水最低的对应的等级一定是最低的
select avg(sal) as avgsal as from emp group by
deptno order by avgsal asc limit 1;
找上述最低平均薪水的等级
select grade from salgrade where (select avg(sal) as avgsal as from emp group by deptno order by avgsal asc limit 1) between losal and hisal;
第四步:
select t.*,s.grade
from (select d.dname,avg(sal) as avgsal as from emp e
join dept d on e.deptno == d.deptno group by dname) t
join salgrade s
on t.avgsal between s.losal and s.hisal
where s.grade = (select grade from salgrade where (select avg(sal) as avgsal as from emp group by deptno order by avgsal asc limit 1) between losal and hisal);
8.取得比普通员工（员工代码没有在mgr字段上出现的)的最高薪水要高的领导人的姓名
select distinct mgr from emp;
员工编号没有在上述结果内的都是普通员工
第一步：找出普通员工的最高薪水!
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.取得最高薪水的前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名员工
日期可以降序排，升序
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，SCGREADE）代表（学号，课名，成绩）
问题:
1.找出没选过“黎明老师”的所有学生姓名
2.列出2门以上（含2门）不及格学生姓名及平均成绩
3.学过1号课程又学过2号课所有学生的姓名
14.列出所有员工及领导的姓名
select a.ename '员工',b.ename ' 领导'
from emp a
left join emp b
on a.mgr = b.empno;
15.列出受雇日期早于其直接上级的所有员工的编号，姓名，部门名称
emp a 员工表
emp b 领导表
a.hiredate < b.hiredate and a.mgr == b.empno
select a.ename '员工'，a.hiredate, b.ename '领导',b.hiredate,d.dname
from emp a
join emp b
on a.mgr = b.empno
join d
on a.deptno = d.deptno
where a.hiredate < b.hiredate;
16.列出部门名称和这些部门的员工信息，同时列出哪些没有员工的部门
select e.*,d.*
from emp e
right join dept d
on e.deptno = d.deptno;
17.列出至少有5个员工的所有部门
select deptno from emp group by deptno having count(*) >= 5;
18.列出薪金比‘SMITH’多的所有员工信息
select sal from emp where ename = 'SMITH';
select ename,sal from emp where sal > (select sal from emp where ename = 'SMITH');
19.列出所有“CLERK”（办事员）的姓名及部门名称，部门的人数
select e.ename,e.job,d.dname
from emp e
join dept d
on e.deptno = d.deptno
where e.job = 'CLERK';
每个部门的人数？
select deptno,count(*) as deptcount from emp group by deptno;
select t1.*,t2.deptcount
from (select e.ename,e.job,d.dname,d.deptno
from emp e
join dept d
on e.deptno = d.deptno
where e.job = 'CLERK') t1
join (select deptno,count(*) as deptcount from emp group by deptno) t2
on t1.deptno = t2.deptno;
20.列出最低薪金大于1500的各种工作及从事此工作的全部雇员人数
按照工作岗位分组求最小值
select job,count(*) from emp group by job having min(sal) > 1500;
先分组，再按照组别查询min(sal)>1500
× select job,count(*) from emp where sal > 1500 group by job;
21.列出部门‘SALES’《销售部》工作的员工的姓名，假定不知道销售部门编号
select ename from emp where deptno = (select deptno from dept where dname = 'SALES');
22.列出薪金高于公司平均薪金的所有员工，所在部门，上级领导，雇员的工资等级
select e.ename '员工',d.dname,l.ename '领导',s.grade
from emp e
join dept d
on s.deptno = d.deptno
left join
emp l
on e.mgr == l.empno
join salgrade s
on e.sal between s.losal and s.hisal
where e.sal > (select avg(sal) from emp);
23.列出与‘SCOTT’从事相同工作的所有员工及部门名称
select job from emp where ename = 'SCOTT';
select e.enmae,e.job,d.dname
from emp e
join dept d
on e.deptno = d.deptno
where e.job = (select job from emp where ename = 'SCOTT')
and e.ename <> 'SCOTT';
24.列出薪金等于部门30中员工的薪金的其他员工的姓名和薪金
select distinct sal from emp where deptno = 30;
select ename,sal from emp where sal in (select distinct sal from emp where deptno = 30) and deptno <> 30;
25.列出薪金高于在部门30工作的所有员工的薪金的员工姓名和薪金 部门名称
select max(sal) from emp where deptno = 30;
select e.ename,e.sal,d.dname
from emp e
join dept d
on e.deptno = d.deptno
where e.sal > (select max(sal) from emp where deptno = 30);
26.列出在每个部门工作的员工数量，平均工资和平均服务期限
select d.*,count(e.ename),ifnull(avg(e.sal),0),ifnull(avg(timestampdiff(YEAR,hiredate,now())),0) as avgservercetime
from emp e
right join dept d
on e.deptno = d.deptno
group by d.deptno,d.dnam,d.loc;
在mysql中怎么计算两个日期的“年差”
select timestampdiff(YEAR,hiredate,now()) from emp;
27.列出所有员工的姓名、部门名称和工资
select e.ename,d.dname,e.sal
from emp e
join dept d
on e.deptno = d.deptno;
28.列出所有部门的详细信息和人数
select d.deptno,d.dname,d.loc,count(e.ename)
from emp e
right dept d
on e.deptno = d.deptno
group by d.deptno,d.dname,d.loc;
29.累出各种工作的最低工资及从事此工作的雇员姓名
select job,min(sal) as minsal
from emp
group by job;
select e.ename,t.*
from emp e
join (select job,min(sal) as minsal
from emp group by job;)t
on e.job = t.job and e.sal = t.minsal;
30.列出各个部门的MANAGER（领导）的最低薪金
select deptno,min(sal)
from emp
where job = 'MANAGER'
group by deptno;
31.累出所有员工的的年工资，按照年薪从低到高排序
select ename,(sal+ifnull(comm,0))*12 as yearsal
from emp
order by yearsal asc;
32.求出员工领导的薪水超过3000的员工名称与领导名称
select a.ename '员工',b.ename '领导'
from emp a
join emp b
on a.mgr = b.empno
where b.sal > 3000;
33.求出部门名称中带‘S’字符的部门员工的工资合计、部门人数
select d.deptno,d.dname,d.loc,count(e.ename),ifnull(sum(e.sal),0) sumsal
from emp e
right join dept d
where d.dname like '%S%'
group by
d.deptno,d.dname,d.loc;
34.给任职日期超过30年的员工加薪10%
update emp set sal = sla*1.1 where timestampdiff(YEAR,hiredate,now())>30;
https://www.bilibili.com/video/BV1Vy4y1z7EX?p=132
