orcale查询平均工资最高的的部门平均工资及部门名称

1 找出平均工资最高的部门号deptno和对应的平均工资avg(sal)
select * from (select avg(sal) s,deptno from emp group by deptno order by avg(sal) desc) where rownum=1;
2 将第一不得到的结果集作为表b,并且和dept表中的deptno进行关联，取出dept表中的dname
select a.dname, b.s
from dept a,
(select *
from (select avg(sal) s, deptno
from emp
group by deptno
order by avg(sal) desc)
where rownum = 1) b
where a.deptno = b.deptno;

select d.dname from dept d where d.deptno=(select deptno from (select avg(sal) a, deptno from emp where sal>0 group by deptno order by a desc )where rownum=1 )