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 you 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.
