B. The statement willfail because subquery cannot be used in the VALUES clause.

C. The statement willfail because the VALUES clause is not required with subquery.

D. The statement willfail because subquery in the VALUES clause is not enclosed with in singlequotation

marks .

Answer: A

解析：

同上题，将查询的结果最为相应列，所以能正确执行

162. View the Exhibitand examine the structure of the PRODUCTS, SALES, and SALE_SUMMARY

tables.

SALE_VW is a view createdusing the following command :

SQL>CREATE VIEWsale_vw AS

SELECT prod_id,SUM(quantity_sold) QTY_SOLD

FROM sales GROUP BYprod_id;

You issue the followingcommand to add a row to the SALE_SUMMARY table :

SQL>INSERT INTOsale_summary

SELECT prod_id, prod_name,qty_sold FROM sale_vw JOIN products

USING (prod_id) WHEREprod_id = 16;

What is the outcome

A. It executessuccessfully.

B. It gives an errorbecause a complex view cannot be used to add data into the SALE_SUMMARY table.

C. It gives an error becausethe column names in the subquery and the SALE_SUMMARY table do not

match.

D. It gives an errorbecause the number of columns to be inserted does not match with the number of

columns in theSALE_SUMMARY table.

Answer: D

解析：

这里插入行数和sale_summary表中的行数不一样会导致错误，测试：

scott@ORCL>insertinto zbcxy select empno from emp;

insert into zbcxy selectempno from emp

*

第 1 行出现错误:

ORA-00947: 没有足够的值

163. View the Exhibitand examine the description for the CUSTOMERS table.

You want to update the CUST_CREDIT_LIMITcolumn to NULL for all the customers, where

CUST_INCOME_LEVEL hasNULL in the CUSTOMERS table. Which SQL statement will accomplish the

task

A. UPDATE customers

SET cust_credit_limit =NULL

WHERE CUST_INCOME_LEVEL= NULL;

B. UPDATE customers

SET cust_credit_limit =NULL

WHERE cust_income_levelIS NULL;

C. UPDATE customers

SET cust_credit_limit =TO_NUMBER(NULL)

WHERE cust_income_level= TO_NUMBER(NULL);

D. UPDATE customers

SET cust_credit_limit =TO_NUMBER(' ',9999)

WHERE cust_income_levelIS NULL;

Answer: B

解析：

题意：update theCUST_CREDIT_LIMIT column to NULL for all the customers, where

CUST_INCOME_LEVEL hasNULL in the CUSTOMERS table

意思是将CUST_INCOME_LEVEL为空的列对应的CUST_CREDIT_LIMIT全部更新为null

获得CUST_INCOME_LEVEL为空的列

cust_income_level ISNULL

164. View the Exhibitand examine the structure of CUSTOMERS and SALES tables.

eva luate the followingSQL statement:

UPDATE (SELECT prod_id,cust_id, quantity_sold, time_id

FROM sales)

SET time_id ='22-MAR-2007'

WHERE cust_id = (SELECTcust_id

FROM customers

WHERE cust_last_name ='Roberts' AND

credit_limit = 600);

Which statement is trueregarding the execution of the above UPDATE statement

A. It would not executebecause two tables cannot be used in a single UPDATE statement.

B. It would not executebecause the SELECT statement cannot be used in place of the table name.

C. It would execute andrestrict modifications to only the columns specified in the SELECT statement.

D. It would not executebecause a subquery cannot be used in the WHERE clause of an UPDATE

statement.

Answer: C

解析：

测试：

scott@ORCL>update(select empno,ename from zbcxy) set ename='zbcxy' where empno>7000;

已更新14行。

说明这种方式可以更新表

165. View the Exhibitand examine the description for the CUSTOMERS table.

You want to update theCUST_INCOME_LEVEL and CUST_CREDIT_LIMIT columns for the customer

with the CUST_ID 2360.You want the value for the CUST_INCOME_LEVEL to have the same value as