--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;
------解决方案--------------------
------解决方案--------------------
------解决方案--------------------
那结贴散分吧,呵~