Oracle笔记_基于样例表的子查询

1、查询工资最高的员工的名字

select ename from emp where sal = (select max(sal) from emp);

2、查询每个部门中工资最高的员工的名字，部门编号和工资

SQL1992标准：select ename, emp.deptno, sal from emp, (select max(sal) max_sal, deptno from emp group by deptno) t where sal = max_sal and emp.deptno = t.deptno;

SQL1999标准：select ename, emp.deptno, emp.sal from emp join (select max(sal) max_sal, deptno from emp group by deptno) t on (emp.deptno = t.deptno and sal = max_sal);

注：99标准将表连接条件和过滤条件进行分离，过滤条件写在where子句中，连接条件写在on后面

3、每个部门平均薪水的等级

select t.deptno, grade from salgrade, (select deptno, avg(sal) avg_sal from emp group by deptno) t where avg_sal between losal and hisal;

select grade, t.deptno from salgrade
join (select deptno, avg(sal) avg_sal from emp group by deptno) t
on avg_sal between losal and hisal;

4、查询部门的平均工资大于2000的所有部门及其平均工资（having应用，非子查询）

select deptno, avg(sal) from emp group by deptno having avg(sal) > 2000;