68.求部门中哪些人的薪水最高
select ename, sal
from emp
join (select max(sal) max_sal, deptno from emp group by deptno) t
on (emp.sal = t.max_sal and emp.deptno = t.deptno);
69.求部门平均薪水的 等级 (先取平均薪水再等级 整数)
select deptno, avg_sal, grade
from (select deptno, avg(sal) avg_sal from emp group by deptno) t
join salgrade s
on (t.avg_sal between s.losal and s.hisal);
70.求部门平均的 薪水等级 (先等级后平均值 小数)
select deptno, avg(grade)
from (select deptno, ename, grade
from emp
join salgrade s
on emp.sal between s.losal and s.hisal) t
group by deptno;
71.雇员中有哪些人是经理人(不是领导empno没有在mgr出现)
select ename from emp where empno in (select distinct mgr from emp);
72.不准用组(聚合)函数,求薪水的最高值(面试题)
select distinct sal
from emp
where sal not in
(select distinct e1.sal from emp e1 join emp e2 on (e1.sal < e2.sal));
73.求平均薪水最高的部门的部门编号
select deptno, avg_sal
from (select deptno, avg(sal) avg_sal from emp group by deptno)
where avg_sal =
(select max(avg_sal)
from (select deptno, avg(sal) avg_sal from emp group by deptno));
74.求平均薪水的等级最低的部门的部门名
select dname, t1.deptno, grade, avg_sal
from (select deptno, grade, avg_sal
from (select deptno, avg(sal) avg_sal from emp group by deptno) t
join salgrade s
on (t.avg_sal between s.losal and s.hisal)) t1
join dept
on (t1.deptno = dept.deptno)
where t1.grade =
(select min(grade)
from (select deptno, grade, avg_sal
from (select deptno, avg(sal) avg_sal
from emp
group by deptno) t
join salgrade s
on (t.avg_sal between s.losal and s.hisal)));
75.求比普通员工的最高薪水还要高的经理人(领导,不是boss)的名称(思考题)
select ename from emp
where empno in (select distinct mgr from emp where mgr is not null)
and sal >
(select max(sal) from emp
where empno not in (select distinct mgr from emp where mgr is not null));
76.求薪水最高的前5名雇员(重点掌握)
select ename, sal
from (select ename, sal from emp order by sal desc)
where rownum <= 5;
77.求薪水最高的第6到第10名雇员(重点掌握)
select ename, sal
from (select ename, sal, rownum r
from (select ename, sal from emp order by sal desc))
where r >= 6
and r <= 10;
68.求部门中哪些人的薪水最高select ename, salfrom empjoin (select max(sal) max_sal, deptno from emp group by deptno) ton (emp.sal = t.max_sal and emp.deptno = t.deptno);69.求部门平均薪水的 等级 (先取平均薪水再等级 整数)select