oracle学习篇六:子查询
-- 1.查询比7654工资要高的员工
select * from emp where sal>(select sal from emp where empno=7654);
---2.查询最低工资的员工信息
select * from emp where sal=(select min(sal) from emp);
------------查询出,部门名称,部门员工数,部门平均工资,部门最低收入的人员姓名,和最高收入的人员
select d.dname,t1.c,t1.avgSal,t1.maxsal,t1.minsal from dept d,(select deptno,count(empno) c,avg(sal) avgSal,max(sal) maxsal,min(sal) minsal from emp group by deptno) t1
where d.deptno=t1.deptno
----ANY函数使用,=any 与in 相同
select * from emp where sal in(select min(sal) from emp group by deptno);
-- > any 比最小的值要大
select * from emp where sal>any(select min(sal) from emp group by deptno);
-- < any 比最大的值要小。
select * from emp where sal < any(select min(sal) from emp group by deptno);
select * from emp where sal < any(select min(sal) from emp group by deptno);
---< all 比最小的还小
select * from emp where sal > ALL(select max(sal) from emp group by deptno);
---> all 比最大的还大
select * from emp where sal < ALL(select max(sal) from emp group by deptno);