开发工具:
文件大小: 61kb
下载次数: 0
上传时间: 2017-05-06
详细说明： ORACLE 知识总结 思考题： 1、 ORACLE中存在哪几类子查询？描述各种子查询的区别？ 2、 ORACLE是如何处理子查询中返回的空值？ 实战练习题： 1、You need to create a report to display the names of customers with a credit limit greater than the average credit limit of all customers. Which SELECT statement should yo u use? （A）SELECT last_name, first_name FROM customer WHERE credit_limit > AVG(credit_limit); （B）SELECT last_name, first_name, AVG(credit_limit) FROM customer GROUP BY AVG(credit_limit); （C）SELECT last_name, first_name, AVG(credit_limit) FROM customer GROUP BY AVG(credit_limit) HAVING credit_limit > AVG(credit_limit); （D）SELECT last_name, first_name FROM customer WHERE credit_limit > (SELECT AVG(credit_limit) FROM customer); Answer: (D) SELECT last_name, first_name FROM customer WHERE credit_limit > (SELECT AVG(credit_limit) FROM customer); Reference: To return the names of all customers with a credit limit greater than the average credit limit of all customers, you must use a subquery. In this instance, the inner query returns the average credit limit of all customers. The outer query takes this average credit limit value and uses this value to display all the customers who have a credit limit greater than this amount. 2、Evaluate the columns in the CUSTOMER and ORDER tables. CUSTOMER -------------------- CUSTOMER_ID NUMBER(5) NAME VARCHAR2(25) CREDIT_LIMIT NUMBER(8,2) ACCT_OPEN_DATE DATE ORDER ------------- ORDER_ID NUMBER(5) CUSTOMER_ID NUMBER(5) ORDER_DATE DATE TOTAL NUMBER(8,2) Which scenario would require a subquery to return the desired results? （A） You need to display the names of all the customers who placed an order today. （B） You need to determine the number of orders placed this year by the customer with ID value 30450. （C） You need to determine the average credit limit of all the customers who opened an account this year. （D） You need to determine which customers have a credit limit greater than the customer with ID value 30450. Answer: (D) You need to determine which customers have a credit limit greater than the customer with ID value 30450. Reference: To determine which customers have a credit limit greater than the customer with ID value 30450, use this SELECT statement with a subquery: SELECT customer_id, name FROM customer WHERE credit_limit > (SELECT credit_limit FROM customer WHERE customer_id = 30450); You could display the names of all the customers who placed an order today by using a join operator or a subquery, but a subquery is not required. 3、Click the EXHIBIT button and examine the INVENTORY table. Evaluate this SQL statement: SELECT id_number FROM inventory WHERE manufacturer_id = (SELECT manufacturer_id FROM inventory WHERE price > 6.00); What will happen if you attempt to execute this statement? （A） The statement will execute and display four values. （B） The statement will execute and display five values. （C） The statement will execute and display three values. （D） The subquery will not execute because the main query will cause an error. （E） The main query statement will not execute because of the values returned by the subquery. Answer: (E) The main query statement will not execute because of the values returned by the subquery. Reference: The main query statement will not execute because of the values returned by the subquery. When executing this statement, an error is returned: ORA-01427: Single-row subquery returns more than one row. Because the greater than (>) operator is used in the subquery, it is possible that more than one row will be returned. These multiple rows are then compared to the MANUFACTURER_ID column with an equality (=) operator, which is used to compare one value to another value. In this instance, we are comparing one value to multiple values that were returned by the subquery causing the statement to fail. 4、examine the table instance chart for the EMPLOYEE table. (3-1) The user needs to retrieve information on employees that have the same department id and salary as an employee id that they will enter. You want the query results to include employees that do not have a salary. Which statement will provide you with the desired results? （A）SELECT * FROM employee WHERE (department, salary) NOT IN (SELECT department, salary) FROM employee WHERE employee_id = &1); （B）SELECT * FROM employee WHERE (department_id, salary) IN (SELECT department_id, NVL(salary, 0) FROM employee WHERE employee_id = &1); （C）SELECT * FROM employee WHERE (department_id, NVL(salary, 0)) IN (SELECT department_id, NVL(salary, 0) FROM employee WHERE employee_id = &1); （D）SELECT * FROM employee WHERE (department_id, salary) IN (SELECT department_id, salary) FROM employee WHERE employee_id = &1 AND salary IS NULL); Answer: (C) SELECT * FROM employee WHERE (department_id, NVL(salary, 0)) IN (SELECT department_id, NVL(salary, 0) FROM employee WHERE employee_id = &1); Reference: The correct answer is: SELECT * FROM employee WHERE (department_id, NVL(salary, 0)) IN (SELECT department_id, NVL(salary, 0) FROM employee WHERE employee_id = &1); If a null value is returned in a subquery, the entire query will return no rows. To ensure that the subquery does not return a null value for the SALARY column, the NVL function is used. Because "0" is returned from the subquery if the SALARY value is null, the query result will include employees that do not have a salary. 5、evaluate the SERVICE table. (3-2) Evaluate this SELECT statement: SELECT machine_id FROM service WHERE technician_id = (SELECT technician_id FROM service WHERE service_date = '11-JAN-1996'); Which result does the query provide? （A） No values will be displayed. （B） The value 980076 will be displayed. （C） The value 678523 will be displayed. （D） A syntax error will be returned. Answer: (A) No values will be displayed. Reference: The WHERE clause of the subquery is searching for the wrong date. If the date were '11-JAN-1997', the SELECT statement would return the value 678523. Because the subquery does not return any rows, the SELECT statement does not return any rows. ...展开收缩
(系统自动生成,下载前可以参看下载内容)
下载文件列表
相关说明
- 本站资源为会员上传分享交流与学习,如有侵犯您的权益,请联系我们删除.
- 本站是交换下载平台，提供交流渠道，下载内容来自于网络，除下载问题外，其它问题请自行百度。
- 本站已设置防盗链，请勿用迅雷、QQ旋风等多线程下载软件下载资源，下载后用WinRAR最新版进行解压.
- 如果您发现内容无法下载，请稍后再次尝试；或者到消费记录里找到下载记录反馈给我们.
- 下载后发现下载的内容跟说明不相乎，请到消费记录里找到下载记录反馈给我们，经确认后退回积分.
- 如下载前有疑问，可以通过点击"提供者"的名字，查看对方的联系方式，联系对方咨询.
