33道数据库题目

准备三张表

1. emp–员工表
   
2. dept–部门表
   
3. salgrade–工资等级表
   

题目

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

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

(方法2）

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

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

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

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

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

4. 不准用组函数（Max），取得最高薪水（给出两种解决方案）
   (方法1- - -order by & limit)

select sal from emp order by sal desc limit 1;

(方法2- - -自连接)

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

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

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

(方法2- - -order by & limit)

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

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