34道查询语句的练习题：

初始表格

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 |
+-------+--------+-----------+------+------------+---------+---------+--------+


dept
+--------+------------+----------+
| DEPTNO | DNAME      | LOC      |
+--------+------------+----------+
|     10 | ACCOUNTING | NEW YORK |
|     20 | RESEARCH   | DALLAS   |
|     30 | SALES      | CHICAGO  |
|     40 | OPERATIONS | BOSTON   |
+--------+------------+----------+


salgrade
+-------+-------+-------+
| GRADE | LOSAL | HISAL |
+-------+-------+-------+
|     1 |   700 |  1200 |
|     2 |  1201 |  1400 |
|     3 |  1401 |  2000 |
|     4 |  2001 |  3000 |
|     5 |  3001 |  9999 |
+-------+-------+-------+

mysql中 有专门计算日期差的函数 timestampdiff(间隔类型，起始日期，当前日期)

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

第一步取得每个部门最高薪资：
select max(sal), deptno from emp group by deptno;
+----------+--------+
| max(sal) | deptno |
+----------+--------+
|  5000.00 |     10 |
|  3000.00 |     20 |
|  2850.00 |     30 |
+----------+--------+
第二部将上面的表当成临时表 t 和emp表 进行对比  

select    
	e.ename, t.*    
from   
	( select max(sal) as 'max' , deptno from emp group by deptno) t  
join  
	emp e  
on   
		t.deptno = e.deptno and t.max = e.sal;
+-------+---------+--------+
| ename | max     | deptno |
+-------+---------+--------+
| BLAKE | 2850.00 |     30 |
| SCOTT | 3000.00 |     20 |
| KING  | 5000.00 |     10 |
| FORD  | 3000.00 |     20 |
+-------+---------+--------+

2.那些人的薪水在部门平均水平之上

第一步 找出每个部门的平均薪水：
select deptno, avg(sal) from emp group by deptno;
+--------+-------------+
| deptno | avg(sal)    |
+--------+-------------+
|     10 | 2916.666667 |
|     20 | 2175.000000 |
|     30 | 1566.666667 |
+--------+-------------+

第二步 找出高于这个水平的人：
select e.ename, e.sal, t.* from  emp e join (select deptno, avg(sal) as avg  from emp e group by deptno) t on  e.sal>t.avg and e.deptno=t.deptno;
+-------+---------+--------+-------------+
| ename | sal     | deptno | avg         |
+-------+---------+--------+-------------+
| ALLEN | 1600.00 |     30 | 1566.666667 |
| JONES | 2975.00 |     20 | 2175.000000 |
| BLAKE | 2850.00 |     30 | 1566.666667 |
| SCOTT | 3000.00 |     20 | 2175.000000 |
| KING  | 5000.00 |     10 | 2916.666667 |
| FORD  | 3000.00 |     20 | 2175.000000 |
+-------+---------+--------+-------------+

3.取得部门中所有人的平均薪水等级：

select 
		e.deptno, avg(s.grade)
from 
		emp  e
join 
		salgrade s
on 
		e.sal between s.losal and s.hisal
group by
		deptno;
+--------+--------------+
| deptno | avg(s.grade) |
+--------+--------------+
|     10 |       3.6667 |
|     20 |       2.8000 |
|     30 |       2.5000 |
+--------+--------------+

4.不用组函数（max）取得最高薪水

第一种降序取最高值  limit：
select 
	ename, sal 
from 
	emp 
order by
	sal 
desc 

limit 1;
+-------+---------+
| ename | sal     |
+-------+---------+
| KING  | 5000.00 |
+-------+---------+

第二种 max（）函数 这里不让用


第三种：自连接
//这个查询条件能找到除了最大值之外的所有值   a b两张表相同  两表里面不会有比最大值更大的数字出现  

select 
	distinct a.sal
from
	emp a
join 
	emp b
on 
a.sal<b.sal;

distinct去重之后把他当作条件来查询

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.00 |
+-------+---------+

5.取得平均薪水最高的部门编号

第一种：  求平均值 然后用排序 limit 函数取值
select
deptno, avg(sal) as avg  
from emp
group by deptno 
order by avg  desc
limit 1;
+--------+-------------+
| deptno | avg         |
+--------+-------------+
|     10 | 2916.666667 |
+--------+-------------+


方法二：max函数
把找到的平均值当作临时表 找最大值
select max(t.avg) 
from
(
select
deptno, avg(sal) as avg  
from emp
group by deptno) t;

+-------------+
| max(t.avg)  |
+-------------+
| 2916.666667 |
+-------------+

6.取得平均薪水最高的部门名称：

emp 表的部门名称等于 dept表的部门名称
--------------------------

select
e.deptno, avg(e.sal) as avg , d.dname
from emp e
join
dept d
on
e.deptno=d.deptno
group by deptno 
order by avg  desc
limit 1;
+--------+-------------+------------+
| deptno | avg         | dname      |
+--------+-------------+------------+
|     10 | 2916.666667 | ACCOUNTING |
+--------+-------------+------------+

7.取得平均薪水等级最低的部门名称

找出部门的平均薪水 和部门名称 
select avg(sal), deptno from emp group by deptno;
+-------------+--------+
| avg(sal)    | deptno |
+-------------+--------+
| 2916.666667 |     10 |
| 2175.000000 |     20 |
| 1566.666667 |     30 |
+-------------+--------+
第二部找出平均水平的等级： 上一把找出来的表当作临时表
select
t.*, s.grade 
from
(select avg(sal) as avg, deptno from emp group by deptno) t 
join salgrade s
on
t.avg between s.losal and s.hisal;

最后：通过临时表找到工资等级 然后通过 工资排序找到最低的工资
select t.*, s.grade 
from 
(select avg(sal) as avg, e.deptno, dname 
 from
	 emp e 
 join 
 	dept d 
 on 
 	e.deptno = d.deptno
 group by 
 	deptno) t 
 join
 	salgrade s 
 on	
    t.avg between s.losal and s.hisal 
 order by 
 	avg 
 limit 1;

8.取得比普通员工（员工代码没有在mgr字段上出现的）最高薪水还要高的领导人的姓名

//mgr是领导的编号   员工编号不在这个范围
找出所有领导的编号
select distinct mgr from emp;
+------+
| mgr  |
+------+
| 7902 |
| 7698 |
| 7839 |
| 7566 |
| NULL |
| 7788 |
| 7782 |
+------+
找出员工的最高薪水 not in 在使用的时候后面的小括号里如果有 null 要手动排除掉
select
ename, max(sal) 
from emp 
where empno 
not in 
(select
 distinct mgr 
 from emp 
 where mgr 
 is not 
 null);
 +-------+----------+
| ename | max(sal) |
+-------+----------+
| SMITH |  1600.00 |
+-------+----------+
 
 
 
比普通员工最高薪水高的一定是领导

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));


+-------+---------+
| ename | sal     |
+-------+---------+
| JONES | 2975.00 |
| BLAKE | 2850.00 |
| CLARK | 2450.00 |
| SCOTT | 3000.00 |
| KING  | 5000.00 |
| FORD  | 3000.00 |
+-------+---------+