141. View the Exhibitand examine the structure of CUSTOMERS and GRADES tables.
You need to displaynames and grades of customers who have the highest credit limit.
Which two SQL statementswould accomplish the task? (Choose two.)
A. SELECT custname,grade
FROM customers, grades
WHERE (SELECTMAX(cust_credit_limit)
FROM customers) BETWEENstartval and endval;
B. SELECT custname,grade
FROM customers, grades
WHERE (SELECT MAX(cust_credit_limit)
FROM customers)
A. SELECT custname,grade
FROM customers, grades
WHERE (SELECTMAX(cust_credit_limit)
FROM customers) BETWEENstartval and endval;
B. SELECT custname,grade
FROM customers, grades
WHERE (SELECTMAX(cust_credit_limit)
FROM customers) BETWEENstartval and endval
AND cust_credit_limitBETWEEN startval AND endval;
C. SELECT custname,grade
FROM customers, grades
WHERE cust_credit_limit= (SELECT MAX(cust_credit_limit)
FROM customers)
AND cust_credit_limitBETWEEN startval AND endval;
D. SELECT custname,grade
FROM customers , grades
WHERE cust_credit_limitIN (SELECT MAX(cust_credit_limit)
FROM customers)
ANDMAX(cust_credit_limit) BETWEEN startval AND endval;
Answer: BC
解析：
题意：You need to display names and grades of customers whohave the highest credit limit 意思要求找出最高credit limit 但是须在startval endval 之间，执行where后的条件从右到左
142. View the Exhibitand examine the structure of the PRODUCTS table.
Evaluate the followingquery:
SQL> SELECT prod_name
FROM products
WHERE prod_id IN (SELECTprod_id FROM products
WHERE prod_list_price =
(SELECT MAX(prod_list_price)FROM products
WHERE prod_list_price <
(SELECT MAX(prod_list_price)FROM products)));
What would be theoutcome of executing the above SQL statement?
A. It produces an error.
B. It shows the names ofall products in the table.
C. It shows the names ofproducts whose list price is the second highest in the table.
D. It shows the names ofall products whose list price is less than the maximum list price.
Answer: C
解析：
(SELECT MAX(prod_list_price)FROMproducts
WHERE prod_list_price<
(SELECTMAX(prod_list_price)FROM products))
从小于最大价格的结果集找最大的结果，那就是第二大的
143. View the Exhibitand examine the structure of the PROMOTIONS table.
You have to generate areport that displays the promo name and start date for all promos that startedafter
the last promo in the'INTERNET' category.
Which query would giveyou the required output?
A. SELECT promo_name,promo_begin_date FROM promotions
WHERE promo_begin_date> ALL (SELECT MAX(promo_begin_date)
FROM promotions )AND
promo_category ='INTERNET';
B. SELECT promo_name,promo_begin_date FROM promotions
WHERE promo_begin_dateIN (SELECT promo_begin_date
FROM promotions
WHEREpromo_category='INTERNET');
C. SELECT promo_name,promo_begin_date FROM promotions
WHERE promo_begin_date> ALL (SELECT promo_begin_date
FROM promotions
WHERE promo_category ='INTERNET');
D. SELECT promo_name,promo_begin_date FROM promotions
WHERE promo_begin_date> ANY (SELECT promo_begin_date
FROM promotions
WHERE promo_category ='INTERNET');
Answer: C
解析：
(SELECT promo_begin_date
FROM promotions
WHERE promo_category ='INTERNET');
得到类别为internet的所有promo_begin_date
SELECT promo_name,promo_begin_date FROM promotions
WHERE promo_begin_date> ALL (SELECT promo_begin_date
FROM promotions
WHERE promo_category ='INTERNET');
大于所有的类别为internet的所有promo_begin_date，即大于最大的，也就是最近的
时间的比较，越大说明离当前时间越近
144. View the Exhibitand examine the structure of the PRODUCTS table.
You want to display thecategory with the maximum number of items.
You issue the followingquery:
SQL>SELECTCOUNT(*),prod_category_id
FROM products
GROUP BYprod_category_id
HAVING COUNT(*) =(SELECT MAX(COUNT(*)) FROM products);
What is the outcome?
A. It executessuccessfully and gives the correct output.
B. It executessuccessfully but does not give the correct output.
C. It generates an errorbecause the subquery does not have a GROUP BY clause.
D. It generates an errorbecause = is not valid and should be replaced by the IN operator.
Answer: C
解析：
子查询用在group by 后面是错误的
145. View the Exhibitand examine the structure of the CUSTOMERS table.
You issue the followingSQL statement on the CUSTOMERS table to display the customers who are in the
same country ascustomers with the last name 'KING' and whose credit limit is less than themaximum
credit limit incountries that have customers with the last name 'KING':
SQL> SELECTcust_id,cust_last_name
FROM customers
WHERE country_idIN(SELECT country_id
FROM customers
WHERE cust_last_name='King')
AND cust_credit_limit< (SELECT MAX(cust_credit_limit)
FROM customers
WHERE country_idIN(SELECT country_id
FROM customers
WHEREcust_last_name='King'));
Which statement is trueregarding the outcome of the above query?
A. It executes and showsthe required result.
B. It produces an errorand the < operator should be replaced by < ALL to get the requiredoutput.
C. It produces an errorand the < operator should be replaced by < ANY to get the requiredoutput.
D. It produces an errorand the IN operator should be replaced by = in the WHERE clause of the main
query to get therequired output.
Answer: A
解析：
题意：
display the customerswho are in the
same country as customerswith the last name 'KING' andwhose
credit limit isless than the maximum
credit limit incountries that have customers with the last name 'KING':
customers withthe last name 'KING'
WHERE country_idIN(SELECT country_id
FROM customers
WHERE cust_last_name='King')
找到所有cust_last_name为king的
whose creditlimit is less than the maximum
credit limitin countries that have customers with the last name 'KING':
cust_credit_limit小于last name 为king中最大的cust
_credit_limit
cust_credit_limit <(SELECT MAX(cust_credit_limit)
FROM customers
WHERE country_idIN(SELECT country_id
FROM customers
WHERE cust_last_name='King'));
146. Evaluate thefollowing SQL statement:
SQL> SELECT cust_id,cust_last_name
FROM customers
WHERE cust_credit_limitIN
(selectcust_credit_limit
FROM customers
WHERE cust_city='Singapore');
Which statement is trueregarding the above query if one of the values generated by the subquery is
NULL?
A. It produces an error.
B. It executes butreturns no rows.
C. It generates outputfor NULL as well as the other values produced by the subquery.
D. It ignores the NULLvalue and generates output for the other values produced by the subquery.
Answer: C
解析：
In 如果子查询中得到部分行为空，则只会返回不为空的行，测试：
scott@ORCL>select *from emp where sal<2000;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- ------------------- -------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-12-80 968 20
7499 ALLEN SALESMAN 7698 20-02-81 1600 160 30
7521 WARD SALESMAN 7698 22-02-81 1250 500 30
7654 MARTIN SALESMAN 7698 28-09-81 1250 1400 30
7788 SCOTT ANALYST 7566 19-04-87 1850 20
7844 TURNER SALESMAN 7698 08-09-81 1500 0 30
7876 ADAMS CLERK 7788 23-05-87 1100 20
7900 JAMES CLERK 7698 03-12-81 950 30
7934 MILLER CLERK 7782 23-01-82 1430 10
已选择9行。
scott@ORCL>select *from emp where comm in (select comm from emp where sal<2000);
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ------------------- ---------- -------- ---------- ---------- ----------
7499 ALLEN SALESMAN 7698 20-02-81 1600 160 30
7521 WARD SALESMAN 7698 22-02-81 1250 500 30
7654 MARTIN SALESMAN 7698 28-09-81 1250 1400 30
7844 TURNER SALESMAN 7698 08-09-81 1500 0 30
如果是not in 需要对子查询结果中的空值做处理，否则会全部返回空
147. View the Exhibitand examine the structure of the PROMOTIONS table.
Evaluate the followingSQL statement:
SQL>SELECTpromo_name,CASE
WHEN promo_cost>=(SELECT AVG(promo_cost)
FROM promotions
WHEREpromo_category='TV')
then 'HIGH'
else 'LOW'
END COST_REMARK
FROM promotions;
Which statement is trueregarding the outcome of the above query?
A. It shows COST_REMARKfor all the promos in the table.
B. It produces an errorbecause the subquery gives an error.
C. It shows COST_REMARKfor all the promos in the promo category 'TV'.
D. It produces an errorbecause subqueries cannot be used with the CASE expression.
Answer: A
解析：
CASE
WHEN promo_cost>=(SELECT AVG(promo_cost)
FROM promotions
WHEREpromo_category='TV')
then 'HIGH'
else 'LOW'
END COST_REMARK
如果大于显示为 HIGH 否则显示为LOW 所以所有的promos将会显示出来
148. View the Exhibitand examine the structure of the PRODUCTS tables.
You want to generate areport that displays the average list price of product categories where theaverage
list price is less thanhalf the maximum in each category.
Which query would give thecorrect output?
A. SELECTprod_category,avg(prod_list_price)
FROM products
GROUP BY prod_category
HAVINGavg(prod_list_price) < ALL
(SELECTmax(prod_list_price)/2
FROM products
GROUP BY prod_category);
B. SELECTprod_category,avg(prod_list_price)
FROM products
GROUP BY prod_category
HAVINGavg(prod_list_price) > ANY
(SELECTmax(prod_list_price)/2
FROM products
GROUP BY prod_category);
C. SELECTprod_category,avg(prod_list_price)
FROM products
HAVINGavg(prod_list_price) < ALL
(SELECTmax(prod_list_price)/2
FROM products
GROUP BY prod_category);
D. SELECTprod_category,avg(prod_list_price)
FROM products
GROUP BY prod_category
HAVINGavg(prod_list_price) > ANY
(SELECTmax(prod_list_price)/2
FROM products);
Answer: A
解析：
题意：generate a reportthat displays the average list price of product categorieswhere the average
list price is less than half the maximum in each category.
average listprice is less than half the maximum in each category.
avg(prod_list_price)< ALL
(SELECTmax(prod_list_price)/2
FROM products
GROUP BY prod_category);
e average listprice of product categories
需要以类别分组
所以：
SELECTprod_category,avg(prod_list_price)
FROM products
GROUP BY prod_category
HAVINGavg(prod_list_price) < ALL
(SELECTmax(prod_list_price)/2
FROM products
GROUP BY prod_category);
149. View the Exhibitsand examine the structures of the COSTS and PROMOTIONS tables.
Evaluate the followingSQL statement:
SQL> SELECT prod_idFROM costs
WHERE promo_id IN(SELECT promo_id FROM promotions
WHERE promo_cost <ALL
(SELECT MAX(promo_cost)FROM promotions
GROUP BY(promo_end_datepromo_
begin_date)));
What would be theoutcome of the above SQL statement?
A. It displays prod IDsin the promo with the lowest cost.
B. It displays prod IDsin the promos with the lowest cost in the same time interval.
C. It displays prod IDsin the promos with the highest cost in the same time interval.
D. It displays prod IDsin the promos with cost less than the highest cost in the same time interval.
Answer: D
解析：
GROUP BY (promo_end_datepromo_
begin_date)
表示以这个时间段分组
150. View the Exhibitand examine the data in the PROMOTIONS table.
You need to display allpromo categories that do not have 'discount' in their subcategory.
Which two SQL statementsgive the required result? (Choose two.)
A. SELECTpromo_category
FROMpromotions
MINUS
SELECTpromo_category
FROMpromotions
WHEREpromo_subcategory = 'discount';
B. SELECTpromo_category
FROMpromotions
INTERSECT
SELECTpromo_category
FROMpromotions
WHEREpromo_subcategory = 'discount';
C. SELECTpromo_category
FROMpromotions
MINUS
SELECTpromo_category
FROMpromotions
WHEREpromo_subcategory <> 'discount';
D. SELECTpromo_category
FROMpromotions
INTERSECT
SELECTpromo_category
FROMpromotions
WHEREpromo_subcategory <> 'discount';
Answer: AD
解析：
MINUS，引用官方文档:
The following statementcombines results with the MINUS operator,
which returns onlyunique rows returned by the first query but not by the second:
INTERSECT
这个值要存在于第一句和第二句才会被选出,相当于两个查询的结果的交集
151. View the Exhibitand examine the structure of the CUSTOMERS and CUST_HISTORY tables.
The CUSTOMERS tablecontains the current location of all currently active customers. The
CUST_HISTORY tablestores historical details relating to any changes in the location of allcurrent as well
as previous customerswho are no longer active with the company.
You need to find thosecustomers who have never changed their address.
Which SET operator wouldyou use to get the required output?
A. MINUS
B. UNION
C. INTERSECT
D. UNION ALL
Answer: A
解析：
题意：find thosecustomers who have never changed their address
所有的customers减去已经改变地址customers就等于没有改变地址的顾客
所以用运算符 minus,引用官方文档：
The followingstatement combines results with the MINUS operator,
which returnsonly unique rows returned by the first query but not by the second:
152. Which statement istrue regarding the UNION operator?
A. By default, theoutput is not sorted.
B. NULL values are notignored during duplicate checking.
C. Names of all columnsmust be identical across all SELECT statements.
D. The number of columnsselected in all SELECT statements need not be the same.
Answer: B
解析：
引用官方文档：
UNION Example The following statement combines the results of two queries with
the UNION operator,which eliminates duplicate selected rows. This statement shows
that you must match datatype (using the TO_CHAR function) when columns do not
exist in one or theother table:
SELECT location_id,department_name "Department",
TO_CHAR(NULL)"Warehouse" FROM departments
UNION
SELECT location_id,TO_CHAR(NULL) "Department", warehouse_name
FROM warehouses;
LOCATION_ID DepartmentWarehouse
----------------------------------------- ---------------------------
1400 IT
1400 Southlake, Texas
1500 Shipping
1500 San Francisco
1600 New Jersey
153. View the Exhibitsand examine the structures of the PRODUCTS and SALES tables.
Which two SQL statementswould give the same output? (Choose two.)
A. SELECT prod_id FROMproducts
INTERSECT
SELECT prod_id FROMsales;
B. SELECT prod_id FROMproducts
MINUS
SELECT prod_id FROMsales;
FROM products pJOIN sales s
ONp.prod_id=s.prod_id;
D. SELECT DISTINCTp.prod_id
FROM products p JOINsales s
ON p.prod_id <>s.prod_id;
Answer: AC
解析：
INTERSECT
这个值要存在于第一句和第二句才会被选出,相当于两个查询的结果的交集
所以A选项的结果是
SELECT prod_id FROMproducts
SELECT prod_id FROMsales;
结果的交集
C. SELECT DISTINCTp.prod_id
FROM products p JOINsales s
ON p.prod_id=s.prod_id;
同样是得到交集，并且去除重复的结果
154. View the Exhibitand evaluate structures of the SALES, PRODUCTS, and COSTS tables.
Evaluate the followingSQL statement:
SQL>SELECT prod_idFROM products
INTERSECT
SELECT prod_id FROMsales
MINUS
SELECT prod_id FROMcosts;
Which statement is trueregarding the above compound query?
A. It produces an error.
B. It shows productsthat were sold and have a cost recorded.
C. It shows productsthat were sold but have no cost recorded.
D. It shows productsthat have a cost recorded irrespective of sales.
Answer: C
解析：
INTERSECT
这个值要存在于第一句和第二句才会被选出,相当于两个查询的结果的交集
Minus，引用官方文档：
The following statementcombines results with the MINUS operator,
which returns onlyunique rows returned by the first query but not by the second:
取得product和sales表的交集，说明已经被销售出去
再减去costs表中对prod_id的记录，所以最终得到被销售出了的产品，但是没有价格记录
155. Evaluate thefollowing SQL statement:
SQL> SELECT promo_id,promo_category
FROM promotions
WHERE promo_category ='Internet' ORDER BY 2 DESC
UNION
SELECT promo_id,promo_category
FROM promotions
WHERE promo_category ='TV'
UNION
SELECT promo_id,promo_category
FROM promotions
WHERE promo_category='Radio';
Which statement is trueregarding the outcome of the above query?
A. It executessuccessfully and displays rows in the descending order of PROMO_CATEGORY.
B. It produces an errorbecause positional notation cannot be used in the ORDER BY clause with SET
operators.
C. It executessuccessfully but ignores the ORDER BY clause because it is not located at theend of the
compound statement.
D. It produces an errorbecause the ORDER BY clause should appear only at the end of a compound
query-that is, with thelast SELECT statement.
Answer: D
解析：
Order by 不能用在此位置，测试：
scott@ORCL>selectempno,ename from emp where job='CLERK' order by 2 union select empno,ename fromemp where job='SALESMAN';
select empno,ename fromemp where job='CLERK' order by 2 union select empno,ename from emp wherejob='SALESMAN'
*
第 1 行出现错误:
ORA-00933: SQL 命令未正确结束
Answer: D
156. Evaluate thefollowing SQL statement:
SQL> SELECT cust_id,cust_last_name "Last Name"
FROM customers
WHERE country_id = 10
UNION
SELECT cust_id CUST_NO,cust_last_name
FROM customers
WHERE country_id = 30;
Which ORDER BY clausesare valid for the above query? (Choose all that apply.)
A. ORDER BY 2,1
B. ORDER BY CUST_NO
C. ORDER BY 2,cust_id
D. ORDER BY"CUST_NO"
E. ORDER BY "LastName"
Answer: ACE
解析：
Order by 后面不能使用别名
Order by 1 表示以第一列进行排序
157. View the Exhibitand examine the structure of the ORDERS and CUSTOMERS tables.
Evaluate the followingSQL command:
SQL> SELECTo.order_id, c.cust_name, o.order_total, c.credit_limit
FROM orders o JOINcustomers c
USING (customer_id)
WHERE o.order_total >c.credit_limit
FOR UPDATE
ORDER BY o.order_id;
Which two statements aretrue regarding the outcome of the above query? (Choose two.)
A. It locks all the rowsthat satisfy the condition in the statement.
B. It locks only thecolumns that satisfy the condition in both the tables.
C. The locks arereleased only when a COMMIT or ROLLBACK is issued.
D. The locks arereleased after a DML statement is executed on the locked rows.
Answer: AC
解析：
引用官方文档：
The FOR UPDATE clauselets you lock the selected rows so that other users cannot lock
or update the rows untilyou end your transaction. You can specify this clause only in
a top-level SELECTstatement, not in subqueries.
当然提交或者回滚将释放该锁
158. Which statementsare true regarding the FOR UPDATE clause in a SELECT statement? (Choose all
that apply.)
A. It locks only thecolumns specified in the SELECT list.
B. It locks the rowsthat satisfy the condition in the SELECT statement.
C. It can be used only inSELECT statements that are based on a single table.
D. It can be used inSELECT statements that are based on a single or multiple tables.
E. After it is enforcedby a SELECT statement, no other query can access the same rows until a
COMMIT or ROLLBACK isissued.
Answer: BD
解析：
同上题
159. View the Exhibitand examine the structure of the CUSTOMERS table.
NEW_CUSTOMERS is a newtable with the columns CUST_ID, CUST_NAME and CUST_CITY that
have the same data typesand size as the corresponding columns in the CUSTOMERS table.
Evaluate the followingINSERT statement:
INSERT INTOnew_customers (cust_id, cust_name, cust_city)
VALUES(SELECTcust_id,cust_first_name' 'cust_last_name,cust_city
FROM customers
WHERE cust_id >23004);
The INSERT statementfails when executed. What could be the reason?
A. The VALUES clausecannot be used in an INSERT with a subquery.
B. Column names in theNEW_CUSTOMERS and CUSTOMERS tables do not match.
C. The WHERE clausecannot be used in a subquery embedded in an INSERT statement.
D. The total number ofcolumns in the NEW_CUSTOMERS table does not match the total number of
columns in the CUSTOMERStable.
Answer: A
解析：
这里不能用子查询的结果插入的到表中
160. View the Exhibitand examine the structure of ORDERS and CUSTOMERS tables.
There is only one customerwith the cust_last_name column having value Roberts. Which INSERT
statement should be usedto add a row into the ORDERS table for the customer whose
CUST_LAST_NAME isRoberts and CREDIT_LIMIT is 600?
A. INSERT INTO orders
VALUES (1,'10-mar-2007','direct',
(SELECT customer_id
FROM customers
WHEREcust_last_name='Roberts' AND
credit_limit=600),1000);
B. INSERT INTO orders(order_id,order_date,order_mode,
(SELECT customer_id
FROM customers
WHEREcust_last_name='Roberts' AND
credit_limit=600),order_total)
VALUES(1,'10-mar-2007','direct', &&customer_id, 1000);
C. INSERT INTO(SELECTo.order_id, o.order_date,o.order_mode,c.customer_id, o.order_total
FROM orders o, customersc
WHERE o.customer_id =c.customer_id
ANDc.cust_last_name='Roberts' ANDc.credit_limit=600 )
VALUES (1,'10-mar-2007','direct',(SELECT customer_id
FROM customers
WHEREcust_last_name='Roberts' AND
credit_limit=600),1000);
D. INSERT INTO orders(order_id,order_date,order_mode,
(SELECT customer_id
FROM customers
WHERE cust_last_name='Roberts'AND
credit_limit=600),order_total)
VALUES(1,'10-mar-2007','direct', &customer_id, 1000);
Answer: A
解析：
A选项中
INSERT INTO orders
VALUES (1,'10-mar-2007','direct',
FROM customers
WHEREcust_last_name='Roberts' AND
credit_limit=600), 1000);
这里将(SELECTcustomer_id
FROM customers
WHEREcust_last_name='Roberts' AND
credit_limit=600)得到的结果作为相应列插入到orders表中
161. View the exhibitand examine the description for the SALES and CHANNELS tables.
You issued the followingSQL statement to insert a row in the SALES table:
INSERT INTO sales VALUES
(23, 2300, SYSDATE,(SELECT channel_id
FROM channels
WHEREchannel_desc='Direct Sales'), 12, 1, 500);
Which statement is trueregarding the execution of the above statement?
A. The statement willexecute and the new row will be inserted in the SALES table.
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.
Evaluate 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
that of the customerwith the CUST_ID 2560 and the CUST_CREDIT_LIMIT to have the same value as
that of the customerwith CUST_ID 2566.
Which UPDATE statementwill accomplish the task?
A. UPDATE customers
SET cust_income_level =(SELECT cust_income_level
FROM customers
WHERE cust_id = 2560),
cust_credit_limit =(SELECT cust_credit_limit
FROM customers
WHERE cust_id = 2566)
WHERE cust_id=2360;
B. UPDATE customers
SET(cust_income_level,cust_credit_limit) = (SELECT
cust_income_level,cust_credit_limit
FROM customers
WHERE cust_id=2560 ORcust_id=2566)
WHERE cust_id=2360;
C. UPDATE customers
SET(cust_income_level,cust_credit_limit) = (SELECT
cust_income_level,cust_credit_limit
FROM customers
WHERE cust_id IN(2560,2566)
WHERE cust_id=2360;
D. UPDATE customers
SET(cust_income_level,cust_credit_limit) = (SELECT
cust_income_level,cust_credit_limit
FROM customers
WHERE cust_id=2560 ANDcust_id=2566)
WHERE cust_id=2360;
Answer: A
解析：
题意：You want thevalue for the CUST_INCOME_LEVEL to have the same value as
that of the customerwith the CUST_ID 2560 and the CUST_CREDIT_LIMIT to have the same value as
that of the customerwith CUST_ID 2566.
意思是要更新的CUST_INCOME_LEVEL要和CUST_ID 2560的CUST_INCOME_LEVEL一样
更新的CUST_CREDIT_LIMIT要和CUST_ID 2566的CUST_CREDIT_LIMIT一样
所以需要两个子查询将CUST_ID 2560的CUST_INCOME_LEVEL和CUST_ID 2566的CUST_CREDIT_LIMIT查询出来再设置为对应值
166. View the Exhibitand examine the structures of the EMPLOYEES and DEPARTMENTS tables.
You want to update theEMPLOYEES table as follows:4 ? 4;
-Update only thoseemployees who work in Boston or Seattle (locations 2900 and 2700).
-Set department_id forthese employees to the department_id corresponding to London (location_id
2100).
-Set the employees'salary in location_id 2100 to 1.1 times the average salary of their department.
-Set the employees'commission in location_id 2100 to 1.5 times the average commission of their
department.
You issue the followingcommand:
SQL>UPDATE employees
SET department_id =
(SELECT department_id
FROM departments
WHERE location_id =2100),
(salary, commission) =
(SELECT 1.1*AVG(salary),1.5*AVG(commission)
FROM employees,departments
WHEREdepartments.location_id IN(2900,2700,2100))
WHERE department_id IN
(SELECT department_id
FROM departments
WHERE location_id = 2900
OR location_id = 2700)
What is the outcome?
A. It executessuccessfully and gives the correct result.
B. It executessuccessfully but does not give the correct result.
C. It generates an errorbecause a subquery cannot have a join condition in an UPDATE statement.
D. It generates an errorbecause multiple columns (SALARY, COMMISION) cannot be specified together
in an UPDATE statement.
Answer: B
解析：
无语法错误
WHEREdepartments.location_id IN(2900,2700,2100)
条件给错，WHEREdepartments.location_id=2100
167. Evaluate thefollowing DELETE statement:
DELETE FROM sales;
There are no otheruncommitted transactions on the SALES table.
Which statement is trueabout the DELETE statement?
A. It would not removethe rows if the table has a primary key.
B. It removes all therows as well as the structure of the table.
C. It removes all therows in the table and deleted rows can be rolled back.
D. It removes all therows in the table and deleted rows cannot be rolled back.
Answer: C
解析：
含有主键的列一样可以删除
Delete 操作不会删除表结构
Delete操作可以回滚
168. View the Exhibitand examine the description of SALES and PROMOTIONS tables.
You want to delete rowsfrom the SALES table, where the PROMO_NAME column in the PROMOTIONS
table has either blowoutsale or everyday low price as values.
Which DELETE statementsare valid? (Choose all that apply.)
A. DELETE
FROM sales
WHERE promo_id = (SELECTpromo_id
FROM promotions
WHERE promo_name ='blowout sale')
AND promo_id = (SELECTpromo_id
FROM promotions
WHERE promo_name ='everyday low price');
B. DELETE
FROM sales
WHERE promo_id = (SELECTpromo_id
FROM promotions
WHERE promo_name ='blowout sale')
OR promo_id = (SELECTpromo_id
FROM promotions
WHERE promo_name ='everyday low price');
C. DELETE
FROM sales
WHERE promo_id IN(SELECT promo_id
FROM promotions
WHERE promo_name ='blowout sale'
OR promo_name ='everyday low price');
D. DELETE
FROM sales
WHERE promo_id IN(SELECT promo_id
FROM promotions
WHERE promo_name IN('blowout sale','everyday low price'));
Answer: BCD
解析：
题意：You want to delete rows from the SALES table, where thePROMO_NAME column in the PROMOTIONS tablehas either blowout sale oreveryday
low price as values.
意思要求找出promo_name 为blowout sale或everyday lowprice的
所以
B，C，D选项正确
169. View the Exhibitand examine the description for the PRODUCTS and SALES table.
PROD_ID is a primary keyin the PRODUCTS table and foreign key in the SALES table. You want to
remove all the rows fromthe PRODUCTS table for which no sale was done for the last three years.
Which is the validDELETE statement?
A. DELETE
FROM products
WHERE prod_id = (SELECTprod_id
FROM sales
WHERE time_id - 3*365 =SYSDATE );
B. DELETE
FROM products
WHERE prod_id = (SELECTprod_id
FROM sales
WHERE SYSDATE >=time_id - 3*365 );
C. DELETE
FROM products
WHERE prod_id IN (SELECTprod_id
FROM sales
WHERE SYSDATE - 3*365>= time_id);
D. DELETE
FROM products
WHERE prod_id IN (SELECTprod_id
FROM sales
WHERE time_id >=SYSDATE - 3*365 );
Answer: C
解析：
这里主要考察了时间的顺序，时间越大，离当前时间越近，所以需要sysdate-3*365>=time_id
170. Which twostatements are true regarding the DELETE and TRUNCATE commands? (Choose two.)
A. DELETE can be used toremove only rows from only one table at a time.
B. DELETE can be used toremove only rows from multiple tables at a time.
C. DELETE can be usedonly on a table that is a parent of a referential integrity constraint.
D. DELETE can be used toremove data from specific columns as well as complete rows.
E. DELETE and TRUNCATEcan be used on a table that is a parent of a referential integrityconstraint
having ON DELETE rule .
Answer: AE
解析：
B选项，Delete操作在同一时间只能对一个表进行删除
C选项，delete还可以删除其他表
D选项，delete只能删除行级数据，不能删除列
172. The SQL statementsexecuted in a user session are as follows:
SQL> CREATE TABLEproduct
(pcode NUMBER(2),
pname VARCHAR2(10));
SQL> INSERT INTOproduct VALUES (1, 'pen');
SQL> INSERT INTOproduct VALUES (2,'pencil');
SQL> SAVEPOINT a;
SQL> UPDATE product SETpcode = 10 WHERE pcode = 1;
SQL> SAVEPOINT b;
SQL> DELETE FROMproduct WHERE pcode = 2;
SQL> COMMIT;
SQL> DELETE FROMproduct WHERE pcode=10;
Which two statementsdescribe the consequences of issuing the ROLLBACK TO SAVE POINT a
command in the session?(Choose two.)
A. The rollbackgenerates an error.
B. No SQL statements arerolled back.
C. Only the DELETEstatements are rolled back.
D. Only the secondDELETE statement is rolled back.
E. Both the DELETEstatements and the UPDATE statement are rolled back.
Answer: AB
解析：
因为已经提交，所以无法做rollback操作
173. When does atransaction complete? (Choose all that apply.)
A. when a DELETEstatement is executed
B. when a ROLLBACKcommand is executed
C. when a PL/SQLanonymous block is executed
D. when a datadefinition language ( DDL) statement is executed
E. when a TRUNCATEstatement is executed after the pending transaction
Answer: BDE
解析：
A选项，delete操作后可以回滚，所以事务未完成
C选项，pl/sql匿名块执行后，也可以回滚
174. Which statement istrue regarding transactions? (Choose all that apply.)
A. A transaction canconsist only of a set of DML and DDL statements.
B. A p art or an entiretransaction can be undone by using ROLLBACK command .
C. A transactionconsists of a set of DML or DCL statements.
D. A part or an entiretransaction can be made permanent with a COMMIT.
E. A transaction canconsist of only a set of queries or DML or DDL statements.
Answer: BC
解析：
A选项，事务只能是dml或DCL语句组成
D选项，违反事务原子性
E选项和A选项原因一样
175. Which twostatements are true regarding savepoints? (Choose two.)
A. Savepoints are effectiveonly for COMMIT.
B. Savepoints may beused to ROLLBACK.
C. Savepoints can beused for only DML statements.
D. Savepoints areeffective for both COMMIT and ROLLBACK.
E. Savepoints can beused for both DML and DDL statements.
Answer: BC
解析：
A选项，应该是rollback
D选项和C选项原因一样
E选项，不能用于ddl