Oracle笔记_基于样例表的更多查询练习

1、查询工资最高的的员工的工资，不允许使用分组函数

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

2、查询平均工资最高的部门的部门编号

我们可能会试图这样去做

SQL> select max(avg(sal)), deptno from emp group by deptno;
select max(avg(sal)), deptno from emp group by deptno
                      *
ERROR 位于第 1 行:
ORA-00937: 非单组分组函数

但是结果却并非我们所想，猜想可能因为内层平均工资根据部门编号分组会有一组部门编号的返回值，那么再加入一个最大的分组函数，或许ORACLE内部就无法识别这里该显示的是什么了

select avg_sal, deptno from (select avg(sal) avg_sal, deptno from emp group by deptno) where avg_sal = (select max(avg_sal) from (select avg(sal) avg_sal from emp group by deptno) );

或者使用组函数嵌套，组函数嵌套最多为两层，因为最内一层组函数的返回值已经可能存在单行了

select deptno,avg_sal 部门最大平均工资 from  (select avg(sal) avg_sal, deptno from emp group by deptno) where avg_sal = (selectmax(avg(sal)) from emp group by deptno);

3、查询平均工资最高的部门的部门名称，根据上一个查询的结果集再连接DEPT表，连接条件为DEPTNO相等

select dname, avg_sal from dept
join
(
select deptno, avg_sal
    from    (
        select avg(sal) avg_sal, deptno
        from emp group by deptno
        )
    where avg_sal = (
            select max(avg(sal))
            from emp group by deptno
            )
) t
on dept.deptno = t.deptno;