Oracle Database 12c SQL OCA/OCP 1Z0-071题库(41-45题)

QUESTION 41

Examine the structure of the INVOICE table.

Name                   NULL？                   Type
----------------  -----------              --------------
INV_NO             NOT NULL              NUMBER(3)
INV_DATE                                               DATE
INV_AMT                                            NUMBER(10,2)

Which two SQL statements would execute successfully?

A. SELECT inv_no, NVL2(inv_date, 'Pending', 'Incomplete')
FROM invoice;

B. SELECT inv_no, NVL2(inv_amt, inv_date, 'Not Available')
FROM invoice;

C. SELECT inv_no, NVL2(inv_date, sysdate-inv_date, sysdate)
FROM invoice;

D. SELECT inv_no, NVL2(inv_amt, inv_amt*.25, 'Not Available')
FROM invoice;

答案：AC

解析：NVL2(exp1,exp2,exp3)，如果exp1 为NULL,返回exp3，如果expr1非空，则返回exp2。另外要求exp2和exp3为相同类型，如果为不同类型，则将exp3转换为exp2，如果无法转化，则报错。

QUESTION 42

Which three statements are true about the ALTER TABLE....DROP COLUMN.... command?

A. A column can be dropped only if it does not contain any data.

B. A column can be dropped only if another column exists in the table.

C. A dropped column can be rolled back.

D. The column in a composite PRIMARY KEY with the CASCADE option can be dropped

E. A parent key column in the table cannot be dropped.

答案：BDE

解析：一个表中至少要有一列，drop为DDL语句，无法回滚。父表对应了子表的外键，无法直接删除。

QUESTION 43

View the exhibit and examine the description of the PRODUCT_INFORMATION table.

Which SQL statement would retrieve from the table the number of products having LIST_PRICE as NULL?

A. SELECT COUNT (DISTINCT list_price)
FROM product_information
WHERE list_price is NULL

B. SELECT COUNT (NVL(list_price, 0))
FROM product_information
WHERE list_price is NULL

C. SELECT COUNT (list_price)
FROM product_information
WHERE list_price i= NULL

D. SELECT COUNT (list_price)
FROM product_information
WHERE list_price is NULL

答案：B

解析：聚合函数对于NULL的处理：

1. COUNT AVG SUM MIN/MAX MEDIAN 默认忽略NULL；
2. 例外：MIN/MAX所有值都是NULL，则返回NULL，否则忽略NULL

QUESTION 44

Which three tasks can be performed using SQL functions built into Oracle Database?

A. displaying a date in a nondefault format

B. finding the number of characters in an expression

C. substituting a character string in a text expression with a specified string

D. combining more than two columns or expressions into a single column in the output

答案：ABC

解析：CONCAT只能连接两列，SQL函数；||可以连接多列，但不是SQL函数。

QUESTION 45

The user SCOTT who is the owner of ORDERS and ORDER_ITEMS tables issues this GRANT command:

GRANT ALL
ON orders, order_items
TO PUBLIC;

What must be done to fix the statement?

A. PUBLIC should be replaced with specific usernames.

B. ALL should be replaced with a list of specific privileges.

C. WITH GRANT OPTION should be added to the statement.

D. Separate GRANT statements are required for the ORDERS and ORDER_ITEMS tables.

答案：D

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

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