MySQL学习第10天（34道练习题：1-12）

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

思路：

        第一步：按照部门分组，找出最高薪资（作为新表t）

select
	deptno,max(sal)
from 
	emp
group by
	deptno;

新表t：

 

        第二步：表e和表t连接。连接条件：e.sal=t.max_sal and e.deptno=t.eptno

select
	t.*,e.ename
from
	emp e
join
	t
on
	e.sal=t.max_sal and e.deptno=t.eptno

         第三步：合并

select
	e.ename,t.*
from
	emp e
join
	(select deptno,max(sal) max_sal from emp group by deptno) t
on
	e.deptno=t.deptno and e.sal=t.max_sal
order by
	deptno asc;

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

思路：

        第一步：按照部门分组，算出平均薪资（作为新表t）

select
	deptno,avg(sal) avg_sal
from
	emp
group by
	deptno;

        第二步：找出薪资高于平均薪资的人员

        表e和表t连接。连接条件：e.sal > t.avg_sal and e.deptno=t.deptno

select
	e.ename,e.sal,t.*
from
	emp e
join
	t
on
	e.sal > t.avg_sal and e.deptno=t.deptno.

        第三步：合并。

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

 

3、取得部门中（所有人的）平均的薪水等级

思路：

        第一步：按照部门分组，算出每个人的薪水等级。将emp e表与salgrade s 表连接

select
	e.ename,e.sal,e.deptno,s.grade
from 
	emp e
join
	salgrade s
on 
	e.sal between s.losal and hisal
order by
	deptno asc;

 

        第二步：算出每个部门的平均薪资等级。基于以上结果，按照deptno分组，求sal（grade)

select
	e.deptno,avg(grade)

from 
	emp e
join
	salgrade s
on 
	e.sal between s.losal and hisal
group by 
	deptno;

4、不准用组函数（Max ），取得最高薪水

思路：将薪资降序排列，取第一个。limit 1

select 
	ename,deptno,sal
from 
	emp
order by 
	sal desc
limit 1;

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

思路：

        第一步：按照部门分组，算出部门的平均薪资。（作为新表t）

select deptno,avg(sal) avg_sal from emp group by deptno order by avg_sal desc

        第二步：基于上述结果，找出平均薪资最高的部门。limit 1

select deptno,avg(sal) avg_sal from emp group by deptno order by avg_sal desc limit 1;

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

思路1：

        第一步：按照部门分组，算出部门平均薪资。作为新表t

select deptno,avg(sal) avg_sal from emp group by deptno order by avg_sal desc limit 1;

 

        第二步：t表与dept d表连接。连接条件：t.deptno=d.deptno

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

 

思路2： 

      将表emp e与表dept d连接。连接条件：e.deptno=d.deptno。 

      按照d.dname分组，求avg(sal)。

      显示最高薪资。limit 1

select 
	d.dname,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、求平均薪水的等级最低的部门的部门名称

思路：

        第一步：按照部门分组，求平均薪资，表e和表d连接；

select d.dname,avg(sal) avg_sal from emp e join dept d on e.deptno=d.deptno group by dname;

         新表t:

 

        第二步：找出每个部门平均薪资的等级。t表和salgrade s表连接，连接条件：t.avg_sal between s.losal and s.hisal。

select
	t.*,s.grade
from
	(select d.dname,avg(sal) avg_sal from emp e join dept d on e.deptno=d.deptno group by dname) t
join
	salgrade s
on 
	t.avg_sal between s.losal and s.hisal
order by
	grade asc
limit 1;

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

思路：

        第一步：找出普通员工找出最高薪水。没有出现在mgr字段的就是普通员工

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、取得薪水最高的前五名员工

思路：将薪水降序排列，取前五。limit 5。

select ename,empno,sal from emp order by sal desc limit 5;

 

10、取得薪水最高的第六到第十名员工

思路：将薪水降序排列。limit 5，5。

select ename,empno,sal from emp order by sal desc limit 5,5;

 

11、取得最后入职的 5 名员工（日期也可以降序、升序）

select ename,empno,hiredate from emp order by hiredate desc limit 5;

 

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

思路：

        第一步：找出每个员工的薪资等级。表e和表s连接，连接条件：e.sal between s.losal and s.hisal

select
	e.ename,e.sal,s.grade
from
	emp e
join
	salgrade s
on 
	e.sal between s.losal and s.hisal
order by 
	grade asc;

        第二步：继续找出每个等级的员工人数。

select 
	s.grade ,count(*)
from 
	emp e 
join 
	salgrade s 
on 
	e.sal between s.losal and s.hisal
group by
	s.grade;