老师布置的几道SQL语句练习题

--0、求大于等于自己所在工种的平均工资的 员工姓名 (面试题)

select ename, e.job, e.sal, j.a --思路,将平均工资和工种当作子表
  from emp e, (select job, avg(sal) a from emp group by job) j
 where e.job = j.job
   and e.sal > j.a;
   
--法二: 99语法 
select ename
  from emp e
  join (select job, avg(sal) a from emp group by job) j
    on e.job = j.job
 where e.sal > j.a;
--1、求平均薪水最高的部门的部门编号
select deptno
  from (select deptno, avg(sal) a from emp group by deptno)
 where a =
       (select max(a)
          from (select deptno, avg(sal) a from emp group by deptno));
--2、求部门平均薪水的等级
select deptno, grade
  from (select deptno, avg(sal) a from emp group by deptno) e, salgrade s
 where e.a > s.losal
   and e.a < s.hisal;
--3、求部门平均的薪水等级
select avg(grade)
  from (select deptno, grade
          from (select deptno, avg(sal) a from emp group by deptno) e,
               salgrade s
         where e.a > s.losal
           and e.a < s.hisal);
--4、求平均薪水的等级最低的部门名称及等级
select dname
  from dept d,
       (select deptno, grade
          from (select deptno, avg(sal) a from emp group by deptno) e,
               salgrade s
         where e.a > s.losal
           and e.a < s.hisal) a
 where a.grade =
       (select min(grade)
          from (select deptno, grade
                  from (select deptno, avg(sal) a from emp group by deptno) e,
                       salgrade s
                 where e.a > s.losal
                   and e.a < s.hisal))

   and d.deptno = a.deptno;

各种嵌套,