Oracle--子查询

select sal from emp
where ename = ‘SCOTT‘;

select * 
from emp
where sal > (select sal from emp
where ename = ‘SCOTT‘);

select *
from emp
where deptno = (
        select deptno 
        from dept
        where dname = ‘SALES‘
);

select e.*
from emp e, dept d
where e.deptno = d.deptno and d.dname = ‘SALES‘;

        select a, b, c (select *...)    //ok    //在select 后面的子查询，必须是单行子查询
        from tab1 ,tab2, (select **)    //ok
        where ... (select *...)         //ok
        order by ...    //err
        group by ...    //err
        having ...(select ****)         //

        select last_name, job_id, salary
        from employees
        where job_id = (
                select job_id
                from employees
                where employee_id = 141
        )
        and salary > (
                select salary
                from employees
                where employee_id = 143
        );

        select last_name, job_id, salary
        from employees
        where salary = (
                select MIN(salary)
                from employees
        );

        select department_id, MIN(salary)
        from employees
        group by department_id
        having MIN(salary) > (
                select MIN(salary)
                from employees
                where department_id = 50
        );

        select *
        from emp
        where deptno in (select deptno from dept where dname=‘SALES‘ or dname = ‘ACCOUNTING‘);

        select *
        from emp
        where deptno not in (select deptno from dept where dname=‘SALES‘ or dname = ‘ACCOUNTING‘);

        select * 
        from emp
        where sal > (select max(sal) from emp where deptno = 30);

        select * 
        from emp
        where sal > all(select max(sal) from emp where deptno = 30);

        select * 
        from emp
        where sal > (select min(sal) from emp where deptno = 30);


        select * 
        from emp
        where sal > all(select min(sal) from emp where deptno = 30);

        select *
        from emp
        where empno in (select mgr from emp);

        select *
        from emp
        where empno not in (select mgr from emp where mgr is not null);

        select empno, ename, sal
        from emp
        order by sal desc;

        select rownum, empno, ename, sal
        from emp
        where rownum <= 3
        order by sal desc

        select rownum, empno, ename, sal from
        (
                select  empno, ename, sal
                from emp
                order by sal desc
        )
        where rownum <=3 ;

select r, empno, ename, sal
from
        (
                select rownum r, empno, ename, sal from
                (
                        select  empno, ename, sal
                        from emp
                        order by sal desc
                )
        where rownum <= 8
        )
where r >= 5;

        select deptno , avg(sal) avgsal
        from emp
        group by deptno

select e.empno, e.ename, e.sal, d.avgsal
from emp e ,
        (
                select deptno , avg(sal) avgsal
                from emp
                group by deptno     
        ) d
where e.deptno = d.deptno and e.sal > d.avgsal;

        select e.empno, e.ename, e.sal, ( select avg(sal) avgsal from emp where deptno = e.deptno) avgsal
        from emp e
        where sal > ( select avg(sal) avgsal from emp where deptno = e.deptno);