SQL基础7作业题

1、取得每个部门最高薪水的人员名称
select e.ename,t.maxsal,e.deptno
from emp e
join (select ename,max(sal) as maxsal,deptno from emp group by deptno) t
on e.deptno=t.deptno and t.maxsal=e.sal;
+-------+---------+--------+
| ename | maxsal  | deptno |
+-------+---------+--------+
| BLAKE | 2850.00 |     30 |
| SCOTT | 3000.00 |     20 |
| KING  | 5000.00 |     10 |
| FORD  | 3000.00 |     20 |
+-------+---------+--------+

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

select e.ename,e.deptno,e.sal,t.avgsal
from emp e
join (select avg(sal) as avgsal,ename,deptno from emp group by deptno) t
on e.sal > t.avgsal and e.deptno=t.deptno
order by e.deptno,e.sal;
+-------+--------+---------+-------------+
| ename | deptno | sal     | avgsal      |
+-------+--------+---------+-------------+
| KING  |     10 | 5000.00 | 2916.666667 |
| JONES |     20 | 2975.00 | 2175.000000 |
| SCOTT |     20 | 3000.00 | 2175.000000 |
| FORD  |     20 | 3000.00 | 2175.000000 |
| ALLEN |     30 | 1600.00 | 1566.666667 |
| BLAKE |     30 | 2850.00 | 1566.666667 |
+-------+--------+---------+-------------+

3、取得部门中（所有人的）平均的薪水等级
1)每个部分平均薪水的等级
select t.*,s.grade
from (select avg(sal) as avgsal,deptno from emp group by deptno) t
join salgrade s
on t.avgsal between s.losal and s.hisal;
+-------------+--------+-------+
| avgsal      | deptno | grade |
+-------------+--------+-------+
| 2916.666667 |     10 |     4 |
| 2175.000000 |     20 |     4 |
| 1566.666667 |     30 |     3 |
+-------------+--------+-------+
3 rows in set (0.02 sec)

2)每个部门薪水等级的平均值

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

4、不准用组函数（Max ），取得最高薪水
select ename,sal
from emp 
order by sal desc
limit 0,1;
+-------+---------+
| ename | sal     |
+-------+---------+
| KING  | 5000.00 |
+-------+---------+
1 row in set (0.00 sec)

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

平均薪水
select avg(sal) as avgs,deptno from emp group by deptno order by avgs desc limit 0,1; 
+-------------+--------+
| avgs        | deptno |
+-------------+--------+
| 2916.666667 |     10 |
+-------------+--------+
1 row in set (0.00 sec)

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

select t.deptno,d.dname,t.avgs
from (select avg(sal) as avgs,deptno from emp group by deptno order by avgs desc limit 0,1) t
join dept d
on d.deptno = t.deptno;
+--------+------------+-------------+
| deptno | dname      | avgs        |
+--------+------------+-------------+
|     10 | ACCOUNTING | 2916.666667 |
+--------+------------+-------------+
1 row in set (0.00 sec)

7、求平均薪水的等级最低的部门的部门名称
select t.deptno,d.dname,t.avgs
from (select avg(sal) as avgs,deptno from emp group by deptno order by avgs limit 0,1) t
join dept d
on d.deptno = t.deptno;
+--------+-------+-------------+
| deptno | dname | avgs        |
+--------+-------+-------------+
|     30 | SALES | 1566.666667 |
+--------+-------+-------------+
1 row in set (0.00 sec)

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

    比“普通员工的最高薪水”还要高的一定是领导！
        没毛病！！！！

答：不知所云，不做了。

9、取得薪水最高的前五名员工
select ename,sal from emp order by sal desc limit 0,5;
+-------+---------+
| ename | sal     |
+-------+---------+
| KING  | 5000.00 |
| FORD  | 3000.00 |
| SCOTT | 3000.00 |
| JONES | 2975.00 |
| BLAKE | 2850.00 |
+-------+---------+
5 rows in set (0.00 sec)

10、取得薪水最高的第六到第十名员工
select ename,sal from emp order by sal desc limit 5,5;
+--------+---------+
| ename  | sal     |
+--------+---------+