Oracle_071_lesson_p16

bestu

Retrieving Data by Using Subqueries 子查询
　　you should be able to:
　　1、Write a multiple-column subquery  多列子查询
　　2、Use scalar subqueries in SQL   标量子查询
　　3、Solve problems with correlated subqueries  关联子查询 ，子查询子句与主查询相互利用
　　4、Use the EXISTS and NOT EXISTS operators
　　EXISTS 子句有1条及以上的值输出，则主查询执行。NOT EXISTS 无值则匹配输出。
　　方法：先执行主查询的结果，再找一行值带入子查询来理解子查询的意思。
　　5、Use the WITH clause  类似视图
　　SELECT department_name, city
　　FROM   departments
　　NATURAL JOIN (SELECT l.location_id, l.city, l.country_id
　　FROM   locations l
　　JOIN   countries c
　　ON(l.country_id = c.country_id)
　　JOIN regions
　　USING(region_id)
　　WHERE region_name = 'Europe');
　　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 (174, 199))
　　AND employee_id NOT IN (174,199);
　　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);
　　A scalar subquery expression is a subquery thatreturns exactly one column value from one row.
　　Scalar subqueries can be used in:
　　The condition and expression part of DECODE and CASE
　　All clauses of SELECT except GROUP BY
　　The SET clause and WHERE clause of an UPDATE statement
　　SELECT employee_id, last_name,
　　(CASE
　　WHEN department_id =
　　(SELECT department_id
　　FROM departments
　　WHERE location_id = 1800)
　　THEN 'Canada' ELSE 'USA' END) location
　　FROM   employees;
　　select department_id, department_name,
　　(select count(*)
　　from   employees e
　　where  e.department_id = d.department_id) as emp_count
　　from   departments d;
　　SELECT column1, column2, ...
　　FROM   table1  outertable
　　WHERE  column1 operator
　　(SELECT  column1, column2
　　FROM    table2
　　WHERE   expr1 =   outertable.expr2);
　　SELECT last_name, salary, department_id
　　FROM   employees outer_table
　　WHERE  salary > (SELECT AVG(salary)
　　FROM   employees inner_table
　　WHERE inner_table.department_id =
　　outer_table.department_id);
　　SELECT department_id, employee_id, salary
　　FROM EMPLOYEES e
　　WHERE 1 =
　　(SELECT  COUNT(DISTINCT salary)
　　FROM EMPLOYEES
　　WHERE e.department_id = department_id
　　AND e.salary