oracle 子查询6,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);