Oracle（六）子查询

Oracle（六）子查询

select e.ename,e.sal from emp e where e.sal >
 (select f.sal from emp f where f.ename = 'JONES');

select max(avg(e.sal))from emp e group by e.deptno;

select e.deptno, avg(e.sal)
from emp e
group by e.deptno
having avg(e.sal) > 1300;

--查询姓名、工作、薪资
--要求工作和员工编号是7788的人工作一样
--并且薪资大于员工编号7521的工资

select e.ename, e.job, e.sal from emp e where e.job = (
select t.job from emp t where t.empno = 7788) and e.sal > (
select f.sal from emp f where f.empno = 7521);

--查询姓名、工作、薪资
--要求薪资大于最低的薪资

select e.ename, e.job, e.sal from emp e where e.sal > (
select min(t.sal) from emp t);

--查询部门编号，每个部门最低工资
--要求最低工资大于30号部门的最低工资

select e.deptno, min(e.sal) from emp e
group by e.deptno
having min(e.sal) > (
select min(t.sal) from emp t where e.deptno = 30);