结果的交集

C. SELECT DISTINCTp.prod_id

FROM products p JOINsales s

ON p.prod_id=s.prod_id;

同样是得到交集，并且去除重复的结果

154. View the Exhibitand eva luate structures of the SALES, PRODUCTS, and COSTS tables.

eva luate 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. eva luate 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. eva luate 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.

eva luate 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