Mysql练习

MySql练习题

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

• 第一步：取得每个部门最高薪水(按照部门编号分组，找出每一组的最大值)

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

deptno	maxsal
10	5000.00
20	3000
30	2850

• 第二步:将以上的查询结果当做一-张临时表t, t和emp表连接，条件: t.deptno = e .deptno and t.maxsal = e.sal

select
    e.ename,t.*
from
    emp e
join
     (select deptno,max(sal) as maxal from emp group by deptno) t
 on
     t.deptno = e .deptno and t.maxal = e.sal

ename	deptno	maxal
BLAKE	30	2850.00
SCOTT	20	3000.00
KING	10	5000.00
FORD	20	3000.00

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

• 找出每个部门的平均薪水

select deptno,avg(sal) as avgsal from emp group by deptno

deptno	avgsal
10	2916.666667
20	2175.000000
30	1566.666667

• 第二步:将以上查询结果当做t表，t和emp表连接 条件:部门编号相同，并且emp的sal大于t表的avgsal

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

depton	avgsal	ename	sal
30	1 566.666667	ALLEN	1 600.00
20	2175.000000	JONES	2975.00
30	1566.6666671	BLAKE	2850. 00
20	21 75.000000	SCOTT	3000.00
10	2916.6666671	KING	5000.0
20	21 75.000000	FORD	3000.0

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

• 第一步:找出每个人的薪水等级 emp e 和 salgrade s 表连接 连接条件：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 s.hisal order by deptno;
group by
    e.deptno;

deptno	avg(s.grade)
10	3.6667
20	2.8000
30	2.5000

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

• 第一种：sal降序，limit1

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

• 第二种方案：

select max(sal) from emp;

• 第三种方案：表的自连接

select sal from emp where sal not in (select distinct a. sal from emp a join emp b on a.sal < b. sal)

sal	sal
5000.00	5000.00

5.取得平均薪水最高的部门的部门编号(至少给出两种解决方案)

• 第一种方案：降序取第一个
• 第一步:找出每个部门的平均薪水

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

deptno	avgsal
10	2916.666667
20	2175 .000000
30	1566.666667

• 降序选

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

deptno	avgsal
10	2916.666667

• 第二种方案：max

select max (t. avgsal) from (select avg(sal) as avgsal from emp group by deptno) t;

select deptno,avg(sal) as avgsal from emp 
group by deptno 
having avgsal = (select max (t. avgsal) from (select avg(sal) as avgsal from emp group by deptno) t);

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

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
l imit
    1;

8.取得比普通员工的最高薪水还要高的领导人姓名

select distinct mgr from emp;

员工编号没有在以上范围内的都是普通员工。

• 第一步：找出普通员工的最高薪水

not in 使用的时候，后面小括号中记得排除NULL

select max (sal) from emp where empno not in (select di stinct mgr from emp where mgr is not null);

• 第二步：找出高于1600的

select ename,sal from emp where sal > (select max (sal) from emp where empno not in (select di stinct 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 limit5,5