|
1、子查询语法 - 主查询执行前，子查询先执行，而且只执行一次
- 主查询使用子查询的结果
例子：查询薪水比Abel高的人员的名字和薪水 SQL> select last_name,salary 2 from employees 3 where salary>(select salary from employees where last_name='Abel'); LAST_NAME SALARY ------------------------- ---------- Hartstein 13000 使用子查询的注意事项： - 子查询需要使用括号括起来
- 子查询一般放在比较条件的右边
- 只有在进行Top-N分析的时候，子查询才可以使用order by进行排序，其他情况都不行
- 对单行子查询使用单行操作符，对多行子查询使用多行操作符
例子：子查询如果不加括号会报错 SQL> select last_name,salary 2 from employees 3 where salary>select salary from employees where last_name='Abel'; where salary>select salary from employees where last_name='Abel' * ERROR at line 3: ORA-00936: missing expression
例子：子查询可以放在比较条件的左边，不过一般都放在右边，便于阅读和理解。 SQL> select last_name,salary 2 from employees 3 where (select salary from employees where last_name='Abel')<salary; <="" salary; LAST_NAME SALARY ------------------------- ---------- Hartstein 13000
例子：一般情况下子查询加上order by进行排序会报错 SQL> select last_name,salary 2 from employees 3 where salary>(select salary from employees where last_name='Abel' order by 1); where salary>(select salary from employees where last_name='Abel' order by 1) * ERROR at line 3: ORA-00907: missing right parenthesis
例子：查找薪水最大的10名员工，在进行Top-N分析的时候，子查询才可以使用order by进行排序 SQL> select * 2 from (select last_name,salary from employees order by 2 desc) 3 where rownum<11; LAST_NAME SALARY ------------------------- ---------- King 24000
2、子查询类型 - 单行子查询，只返回一行结果
- 多行子查询，返回多行结果
（1）单行子查询 单行子查询比较操作符
例子：查找job_id与employee_id为141号员工相同，薪水比143号员工大的人员 SQL> select last_name,job_id,salary 2 from employees 3 where job_id=(select job_id from employees where employee_id=141) 4 and salary>(select salary from employees where employee_id=143); LAST_NAME JOB_ID SALARY ------------------------- ---------- ---------- Nayer ST_CLERK 3200
在子查询里面使用组函数 例子：查找最低薪水员工的信息 SQL> select last_name,job_id,salary 2 from employees 3 where salary=(select min(salary) from employees); LAST_NAME JOB_ID SALARY ------------------------- ---------- ---------- Olson ST_CLERK 2100
在having子句使用子查询 例子：查找部门最小薪水比50部门最小薪水大的部门 SQL> select department_id,min(salary) 2 from employees 3 group by department_id 4 having min(salary)>(select min(salary) from employees where department_id=50); DEPARTMENT_ID MIN(SALARY) ------------- ----------- 100 6900 如果将上面语句子查询中的where替换成group by就会报错，因为使用group by后，子查询结果为多行，而“>”为单行操作符 SQL> select department_id,min(salary) 2 from employees 3 group by department_id 4 having min(salary)>(select min(salary) from employees group by department_id=50); having min(salary)>(select min(salary) from employees group by department_id=50) * ERROR at line 4: ORA-00907: missing right parenthesis
（2）多行子查询 如果子查询结果返回多行，则需要使用多行操作符，再使用单行操作符会报错 例子：子查询返回多行，使用单行操作符报错 SQL> select employee_id,last_name from employees 2 where salary=(select min(salary) from employees group by department_id); where salary=(select min(salary) from employees group by department_id) * ERROR at line 2: ORA-01427: single-row subquery returns more than one row 如果子查询返回0行，则主查询也返回0行，但是在单行子查询中，通过使用“=”进行判断的时候，即使主查询返回0行，也不能说明子查询返回0行，因为子查询有可能返回的是null，而null是不能使用“=”进行判断的。 例子：查询与人员编号100有相同的管理人员的员工信息 SQL> select last_name,manager_id from employees 2 where manager_id=(select manager_id from employees where employee_id=100); no rows selected 子查询的结果为null SQL> select manager_id from employees where employee_id=100; MANAGER_ID ----------
多行子查询比较操作符 前面都可以使用not
在多行子查询中使用any操作符 例子：查询比职位为“IT_PROG”的任何一位员工的薪水低的员工信息（实际上就是比职位为“IT_PROG”的最大薪水的员工薪水小，小于最大的，如果是大于any，就是大于最小的） SQL> select employee_id,last_name,job_id,salary from employees 2 where salary<any(select salary="" from="" employees="" where="" job_id="IT_PROG" )="" 3 and job_id<>'IT_PROG'; EMPLOYEE_ID LAST_NAME JOB_ID SALARY ----------- ------------------------- ---------- ---------- 132 Olson ST_CLERK 2100
在多行子查询中使用all操作符 例子：查询比职位为“IT_PROG”的所有员工的薪水低的员工信息（实际上就是比职位为“IT_PROG”的最小薪水的员工薪水小，小于最小的，如果是大于all，就是大于最大的） SQL> select employee_id,last_name,job_id,salary from employees 2 where salary<all(select salary="" from="" employees="" where="" job_id="IT_PROG" )="" 3 and job_id<>'IT_PROG'; EMPLOYEE_ID LAST_NAME JOB_ID SALARY ----------- ------------------------- ---------- ---------- 185 Bull SH_CLERK 4100
在多行子查询中使用in或者not in操作符 例子：查找是管理人员的员工姓名和不是管理人员的员工名字 SQL> select emp.last_name from employees emp 2 where emp.employee_id in (select mgr.manager_id from employees mgr); LAST_NAME ------------------------- Zlotkey 18 rows selected. SQL> select emp.last_name from employees emp 2 where emp.employee_id not in (select mgr.manager_id from employees mgr); no rows selected 这里因为如果你使用in只需要和子查询返回结果的任何一个匹配都可以，而使用not in则需要和子查询返回结果的所有进行比较，包括null，而与null进行计算，都为null，故结果为0行，可以先在子查询中排除null SQL> select emp.last_name from employees emp 2 where emp.employee_id not in (select mgr.manager_id from employees mgr where mgr.manager_id is not null); LAST_NAME ------------------------- Whalen 89 rows selected.
3、相关习题 （1）View the Exhibit and examine the description of the EMPLOYEES table. You want to display the EMPLOYEE_ID, FIRST_NAME, and DEPARTMENT_ID for all the employees who work in the same department and have the same manager as that of the employee having EMPLOYEE_ID 104. To accomplish the task, you execute the following SQL statement: SELECT employee_id, first_name, department_id FROM employees WHERE (manager_id, department_id) =(SELECT department_id, manager_id FROM employees WHERE employee_id = 104) AND employee_id <> 104 When you execute the statement it does not produce the desired output. What is the reason for this ? A.The WHERE clause condition in the main query is using the = comparison operator, instead of EXISTS.
B.The WHERE clause condition in the main query is using the = comparison operator, instead of the IN operator.
C.The WHERE clause condition in the main query is using the=comparison operator, instead of the =ANY operator.
D.The columns in the WHERE clause condition of the main query and the columns selected in the subquery should be in the same order. 答案：D （2）View the Exhibit and examine PRODUCTS and ORDER_ITEMS tables. You executed the following query to display PRODUCT_NAME and the number of times the product has been ordered: SELECT p.product_name, i.item_cnt FROM (SELECT product_id, COUNT (*) item_cnt FROM order_items GROUP BY product_id) i RIGHT OUTER JOIN products p ON i.product_id = p.product_id? What would happen when the above statement is executed? A.The statement would execute successfully to produce the required output. B.The statement would not execute because inline views and outer joins cannot be used together. C.The statement would not execute because the ITEM_CNT alias cannot be displayed in the outer query. D.The statement would not execute because the GROUP BY clause cannot be used in the inline view. 答案：Ａ （3）A non-?correlated subquery can be defined as ____. A.a set of sequential queries, all of which must always return a single value B.a set of sequential queries, all of which must return values from the same table C.a SELECT statement that can be embedded in a clause of another SELECT statement only D.a set of one or more sequential queries in which generally the result of the inner query is used as the search value in the outer query 答案：D （4）Which two statements are true regarding operators used with subqueries? (Choose two.) A. The NOT IN operator is equivalent to IS NULL. B. The<any operator="" means="" less="" than="" the="" maximum.="" C. =ANY and =ALL operators have the same functionality. D. The IN operator cannot be used in single-row subqueries. E. The NOT operator can be used with IN, ANY and ALL operators. 答案：BE （5）Which two statements are true regarding multiple?row subqueries? (Choose two.) A.They can contain group functions. B.They always contain a subquery within a subquery. C.They use the < ALL operator to imply less than the maximum. D.They can be used to retrieve multiple rows from a single table only. E.They should not be used with the NOT IN operator in the main query if NULL is likely to be a part of the result of the subquery. 答案：AE （6）View the Exhibit and examine the structure of the EMPLOYEES table. You want to know the FIRST_NAME and SALARY for all employees who have the same manager as that of the employee with the first name 'Neena' and have salary equal to or greater than that of 'Neena'. Which SQL statement would give you the desired result? A.SELECT first_name, salary FROM employees WHERE (manager_id, salary) >= ALL (SELECT manager_id, salary FROM employees WHERE first_name = 'Neena' ) AND first_name <> 'Neena'; B.SELECTfirst_name, salary FROM employees WHERE (manager_id, salary) >= (SELECT manager_id, salary FROM employees WHERE first_name = 'Neena' ) AND first_name <> 'Neena'; C.SELECT first_name, salary FROM employees WHERE (manager_id, salary) >= ANY (SELECT manager_id, salary FROM employees WHERE first_name = 'Neena' ) AND first_name <> 'Neena'; D.SELECT first_name, salary FROM employees WHERE ( manager_id = (SELECT manager_id FROM employees WHERE first_name = 'Neena' ) AND salary >= ( SELECT salary FROM employees WHERE first_name = 'Neena' ) ) AND first_name <> 'Neena'; 答案：D （7）View the Exhibit and examine the structure of the ORDERS table. You have to display ORDER_ID, ORDER_DATE, and CUSTOMER_ID for all those orders that were placed after the last order placed by the customer whose CUSTOMER_ID is 101. Which query would give you the desired output? A.SELECT order_id, order_date FROM orders WHERE order_date > ALL (SELECT MAX(order_date) FROM orders ) AND customer_id = 101;
B.SELECT order_id, order_date FROM orders WHERE order_date > ANY (SELECT order_date FROMorders WHERE customer_id = 101);
C.SELECT order_id, order_date FROM orders WHERE order_date > ALL (SELECT order_date FROM orders WHERE customer_id = 101);
D.SELECT order_id, order_date FROM orders WHERE order_date IN (SELECT order_date FROM orders WHERE customer_id = 101); 答案：C （8）View the Exhibit and examine the data in the PRODUCT_INFORMATION table. Which two tasks would require subqueries？(Choose two.) A.displaying the minimum list price for each product status.
B.displaying all supplier IDs whose average list price is more than 500.
C.displaying the number of products whose list prices are more than the average list price.
D.displaying all the products whose minimum list prices are more than the average list price of products having the product status orderable.
E.displaying the total number of products supplied by supplier 102071 and having product status OBSOLETE. 答案：CD （9）Which two statements are true regarding subqueries？(Choose two.)
A.Only two subqueries can be placed at one level.
B.A subquery can be used to access data from one or more tables or views.
C.If the subquery returns 0 rows, then the value returned by the subquery expression is NULL.
D.The columns in a subquery must always be qualified with the name or alias of the table used.
E.A subquery in the WHERE clause of a SELECT statement can be nested up to three levels only. 答案：BC （10）View the Exhibit and examine the structure of the ORDERS table. Which task would require subqueries? A. displaying the total order value for sales representatives 161 and 163 B. displaying the order total for sales representative 161 in the year 1999 C. displaying the number of orders that have order mode online and order date in 1999 D. displaying the number of orders whose order total is more than the average order total for all online orders 答案：D （11）Which two statements are true regarding subqueries？(Choose two.) A.The ORDER BY clause can be used in the subquery.
B.A subquery can be used in the FROM clause of a SELECT statement.
C.If the subquery returns NULL, the main query may still return result rows.
D.A subquery can be placed in a WHERE clause, GROUP BY clause, or a HAVING clause.
E.Logical operators, such as AND, OR and NOT, cannot be used in the WHERE clause of a subquery. 答案：AB
| 
|手机版|小黑屋|重庆思庄Oracle、Redhat认证学习论坛
( 渝ICP备12004239号-4 )
发表于 2015-11-24 14:28:21
QQ好友和群
QQ空间
腾讯微博
腾讯朋友
收藏
支持
反对
发表于 2015-11-24 17:46:48
楼主