Oracle之---子查询语法

SELECT select_list

FROM  table

WHERE  exPR Operator

     (SELECT select_list

         FROM  table);

   =         

   >= 

   <

   <= 

   <>

题目：返回job_id与141号员工相同，salary比143号员工多的员工

            姓名，job_id和工资

SELECTlast_name,job_id,salary

FROM   employees

WHERE job_id= 

                (SELECTjob_id

                 FROM   employees

                 WHERE employee_id= 141)

AND    salary >

                (SELECT salary

                 FROM   employees

                 WHERE employee_id= 143);

在查询中使用组函数

SELECTlast_name,job_id, salary

FROM   employees

WHERE  salary =

                (SELECT  MIN(salary)

                 FROM   employees);

子查询中的HAVING 语句

[

SELECT   department_id,MIN(salary)

FROM     employees

GROUPBY department_id

HAVING   MIN(salary) >

                       (SELECT MIN(salary)

                        FROM   employees

                        WHERE  department_id= 50);

多行子查询

*返回多行

*使用多行比较符

IN   等于列表的任意一个

ANY  和子查询返回的某一个值

ALL  和子查询返回的所有值进行比较

题目：返回其它部门中比job_id为‘IT_PROG’部门任一工资低的员工的员

              工号、姓名、job_id 以及salary

SELECT employee_id,last_name,job_id, salary

FROM   employees

WHERE  salary < ANY

                    (SELECTsalary

                     FROM   employees

                     WHERE  job_id = 'IT_PROG')

AND    job_id <> 'IT_PROG';