oracle中的高级查询

oracle中的高级查询

一、集合操作 set operators

    a)联合union 相当于 or条件

      sal>2500 or job=manager

    

      select ename,sal,job  from emp where sal>2500

      union

      select ename,sal,job  from emp where job='MANAGER';

      select ename,sal,job  from emp where job='MANAGER' or sal>2500;

    b)union all

      select ename,sal,job  from emp where sal>2500

      union all

      select ename,sal,job  from emp where job='MANAGER'; 

    c)差集intersect

      sal>2500 and job=manager 

      select ename,sal,job  from emp where job='MANAGER' and sal>2500;

      select ename,sal,job  from emp where sal>2500

      intersect

      select ename,sal,job  from emp where job='MANAGER';

    d)补集minus

      sal>2500 and job<>manager

      select ename,sal,job  from emp where sal>2500 and job<>'MANAGER';

      select ename,sal,job  from emp where sal>2500

      minus

      select ename,sal,job  from emp where job='MANAGER';

二、高级查询advanced subqueries

     select ename from emp

     where sal >(select sal from emp where empno=7844);

     SCOTT>create table manager as select * from emp;

     SCOTT>update manager set sal=1300 where empno=7521;

     SCOTT>update manager set sal=1600 where empno=7782;

     SCOTT> select empno,ename,sal,job 

            from manager

            where (sal,job) in (select max(sal),job from manager group by job);

     SCOTT>select empno,ename,sal,job from manager

           where sal in (select max(sal) from manager group by job)

           and job in (select distinct job from manager);

     HR>SELECT employee_id, manager_id, department_id

        FROM  employees

        WHERE  (manager_id, department_id) IN

                       (SELECT manager_id, department_id

                        FROM   employees

                        WHERE  employee_id IN (178,174))

        AND employee_id NOT IN (178,174);    

     HR>SELECT  employee_id, manager_id, department_id

        FROM employees

        WHERE manager_id IN (SELECT  manager_id

                            FROM    employees

                            WHERE   employee_id IN (174,141))

        AND department_id IN (SELECT  department_id

                             FROM    employees

                             WHERE   employee_id IN (174,141))

        AND employee_id NOT IN (174,141)