几道sql查询练习题

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

select e.ename,a.*

from  emp e

join 

(select deptno,max(sal) as maxsal  from emp group by deptno) a 

on  e.deptno=a.deptno and e.sal=a.maxsal;

2.找出薪水在平均薪水之上员工

select e.ename ,t.*

from emp e

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

on e.sal>t.avgsal and t.deptno=e.deptno;

3.取得部门中平均的薪水等级

select avg(t.grade),t.deptno 

from  

(select ename,deptno,grade from emp e join salgrade s  on e.sal between s.losal and hisal) t

group by t.deptno;

4.不用max找最高工资

逆序+limit

自连接

select sal  form emp

where sal  not in

(select distinct e1.sal from  emp e1 join emp e2 on e1.sal<e2.sal);

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

嵌套2+max

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

太绕了我服了

select max(sal) from emp 

where empno not in (select distinct mgr from where mgr is not null);

这就找到了员工最高薪水，然后嵌套

7.取得薪水最高的前五名员工

select * from emp  order by sal desc limit 0,5;

8.找出每个员工薪水等级有多少个员工

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

9.列出薪水高于在部门30工作的所有员工的薪水的员工姓名和薪水，部门编号

Select ename,sal,deptno from emp where sal>
(Select max(sal) from emp e join dept d on e.deptno = d.deptno
Where t.deptno =30);
 

10.列出所有部门信息和人数

Select d.deptno,dname,loc,count(*)

from emp e join dept d On e.deptno = d.deptno

Group by deptno;

11.列出各个部门的manager的最低薪水

Select deptno,min(sal) from emp where job='manager' group by deptno;