OCP试题疑问集-051

131. View the Exhibitand examine the structure of the PRODUCT, COMPONENT, and PDT_COMP

tables.

In PRODUCT table, PDTNOis the primary key.

In COMPONENT table,COMPNO is the primary key.

In PDT_COMP table,(PDTNO,COMPNO) is the primary key, PDTNO is the foreign key referencing

PDTNO in PRODUCT tableand COMPNO is the foreign key referencing the COMPNO in COMPONENT

table.

You want to generate areport listing the product names and their corresponding component names, ifthe

component names andproduct names exist.

Evaluate the followingquery:

SQL>SELECTpdtno,pdtname, compno,compname

FROM product _____________pdt_comp

USING (pdtno)____________ component USING(compno)

WHERE compname IS NOTNULL;

Which combination ofjoins used in the blanks in the above query gives the correct output?

A. JOIN; JOIN

B. FULL OUTER JOIN; FULLOUTER JOIN

C. RIGHT OUTER JOIN; LEFTOUTER JOIN

D. LEFT OUTER JOIN;RIGHT OUTER JOIN

Answer: C

这道题乍一看，确实很绕。

if the component names and product names exist.要找到组件名和产品名都不为空的数据，其实包含了四个条件。

1. product表中的product name不为空

2.componet表中的component name不为空

3.product表中的数据必须在pdt_comp表中出现，否则不管用哪种外连接，都会导致某一个name为空

4.componet表中的数据必须在pdt_comp表中出现，否则不管用哪种外连接，都会导致某一个name为空

由于有WHERE compname IS NOTNULL;所以不论连接方式如何.条件2必定满足。

也就是说，只需考虑条件1,3,4满足情况。

A选项用两个内连接，是最容易分析。因为pdt_comp表的主键是以外键形式参照另两张表的，所以两次内连接后

可以看成以pdt_comp为驱动表连接其他两张表。这样3.4条件就符合了。

唯独条件1无法满足。

B选项用两个全外连接。因为pdt_comp表的主键是以外键形式参照另两张表的，意味着先以product表作驱动表左连接pdt_comp。

这将导致出现product表中存在，而pdt_comp中不存在数据会被显示出来。当然由于最后WHERE compname IS NOTNULL的条件，

这些数据又被过滤掉，因此条件3被满足。之后又以componet表作为驱动表左连接pdt_comp，这将导致出现componet表中存在，

而pdt_comp中不存在数据会被显示出来。这将导致条件4不被满足。同时条件1也无法满足。

D选项和B选项是一样。就不多说了

C选项先右在左连接。意味着以pdt_comp为驱动表左连接其他两张表。和A选项是一样的。

同样无法满足条件1，所以我认为也不是正确答案。

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) BETWEEN startval and endval

AND cust_credit_limit BETWEEN 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

疑问在B选项，第一个条件 (SELECTMAX(cust_credit_limit)  FROM customers) BETWEEN startval and endval

确定最大值所在的级别，但第二个条件cust_credit_limit BETWEEN startval AND endval;会把比最大值小，但

同属一个级别的顾客检索出来。

D选项的问题出在，后一个条件里的分组函数不应出现（此处应该用子查询）。导致ORA错误。

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

经实测，子查询为NULL的行会被无视掉。这题的答案应该为D

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

可能是我的英文不好，我觉得D选项应该写成  It displays prod IDsin the promos with cost less than all the highest cost in the each time interval.

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

D选项有问题，如果某个分类的子分类即包含 'discount'也包含非 'discount'数据，会被选出来。

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

remove all the rows from the PRODUCTS table for which no sale was done for the last three years.要求是删除过去3年里没有发生交易的商品ID。

首先PROD_ID is a primary keyin the PRODUCTS table and foreign key in the SALES table，即使过去3年里没有发生交易，3年之前仍然可能存在交易，因此由于存在外键关联，products表的记录是不能被删除的。

而且C选项的子查询检索的是3年之前的交易记录。和3年之内的交易没有任何关系。所以为什么选C完全看不懂。