Oracle Database 12c SQL OCA/OCP 1Z0-071题库（36-40题）

欢迎大家来到IT世界,在知识的湖畔探索吧!

QUESTION 36

Examine the structure of the BOOKS_TRANSACTIONS table:

You want to display the member IDs, due date, and late fee as $2 for all transactions.

Which SQL statement must you execute?

A. SELECT member_id AS MEMBER_ID, due_date AS DUE_DATE, $2 AS LATE_FEE FROM
BOOKS_TRANSACTIONS;

欢迎大家来到IT世界,在知识的湖畔探索吧!

欢迎大家来到IT世界,在知识的湖畔探索吧!B. SELECT member_id 'MEMBER ID', due_date 'DUE DATE', '$2 AS LATE FEE' FROM
BOOKS_TRANSACTIONS;

C. SELECT member_id AS "MEMBER ID", due_date AS "DUE DATE", '$2' AS "LATE FEE"
FROM BOOKS_TRANSACTIONS;

欢迎大家来到IT世界,在知识的湖畔探索吧!D. SELECT member_id AS "MEMBER ID", due_date AS "DUE DATE", $2 AS "LATE FEE" FROM
BOOKS_TRANSACTIONS;

答案：C

解析：列别名，注意点：

1. 如果列别名包含在双引号中，则可包含空格和其他特殊字符；
2. 如果列别名未包含在双引号中，则根据数据库对象的标准规则命名。

QUESTION 37

In which three situations does a transaction complete?

A. when a PL/SQL anonymous block is executed

B. when a DELETE statement is executed

C. when a ROLLBACK command is executed

D. when a data definition language (DDL) statement is executed

E. when a TRUNCATE statement is executed after the pending transaction

答案：CDE

解析：控制事务语句：

1. 显式提交：COMMIT，隐式提交：DDL语句执行之前和之后、从大都数Oracle程序和工具中正常退出；
2. 当前用户未提交的事务只有当前对话中能看到；
3. 显式/隐式提交都将之前的SAVEPOINT 从内存中删除；
4. 所有的DDL都无法通过ROLLBACK撤销；
5. 当Oracle软件或工具非正常退出，则会回滚。

QUESTION 38

View the exhibit and examine the data in ORDERS_MASTER and MONTHLY_ORDERS tables.

Evaluate the following MERGE statement:

MERGE_INTO orders_master o
USING monthly_orders m
ON (o.order_id = m.order_id)
WHEN MATCHED THEN
UPDATE SET o.order_total = m.order_total
DELETE WHERE (m.order_total IS NULL)
WHEN NOT MATCHED THEN
INSERT VALUES (m.order_id, m.order_total)

What would be the outcome of the above statement?

A. The ORDERS_MASTER table would contain the ORDER_IDs 1, 2, 3 and 4.

B. The ORDERS_MASTER table would contain the ORDER_IDs 1, 2 and 4.

C. The ORDERS_MASTER table would contain the ORDER_IDs 1, 2 and 3.

D. The ORDERS_MASTER table would contain the ORDER_IDs 1 and 2.

答案：B

QUESTION 39

Evaluate the following SQL statement:

SELECT product_name || 'it's not available for order'
FROM product_information
WHERE product_status = 'obsolete';

You received the following error while executing the above query:

ERROR
ORA-01756: quoted string not properly terminated

What would you do to execute the query successfully?

A. Remove the single quotation marks enclosing the character literal string in the SELECT clause

B. Use the escape character to negate the single quotation mark within the literal character string in the SELECT clause

C. Enclose the character literal string in the SELECT clause within double quotation marks

D. Use the Oracle (q) operator and delimite to allow the use of a single quotation mark within the literal character string in the SELECT clause

答案：D

解析：错误原因：在SELECT product_name || ‘it’s not available for order’ 中的单引号。有如下方法修改：

1.在字符串单引号处再加一个单引号

product_name || ‘it’s not available for order’ –改为product_name || ‘it”s not available for order’

单引号的作用：加了单引号的字段是一个字，类似字符串，不区分大小写，，单引号是用来特制的，比如日期字符串的引用，两个单引号就是纯正的单引号，表示转义。

2、使用“q-quote写法”(Quote (q) operator and delimiter)

product_name || ‘it’s not available for order’ –改为product_name || q'<it’s not=”” available=”” for=”” order=””>’

QUESTION 40

View the exhibit and examine the ORDERS table.

The ORDERS table contains data and all orders have been assigned a customer ID. Which statement would add a NOT NULL constraint to the CUSTOMER_ID column?

A. ALTER TABLE orders
MODIFY CONSTRAINT orders_cust_id_nn NOT NULL (customer_id);

B. ALTER TABLE orders
ADD CONSTRAINT orders_cust_id_nn NOT NULL (customer_id);

C. ALTER TABLE orders
MODIFY customer_id CONSTRAINT orders_cust_nn NOT NULL (customer_id);

D. ALTER TABLE orders
ADD customer_id NUMBER(6)CONSTRAINT orders_cust_id_nn NOT NULL;

答案：C

解析：约束的内联声明和外部定义、NOT NULL约束的声明参见第六题解析。

后续陆续更新，转载请注明出处。

本人水平有限，欢迎指正。