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

QUESTION 26

Which normal form is a table in if it has no multi-valued attributes and no partial dependencies?

A. second normal form

B. first normal form

C. third normal form

D. fourth normal form

答案：A

解析：范式normal form：

1. 第一范式：表中字段都是单一属性，不能再分
2. 第二范式：表中的每一列都与主键有关系，不能值与主键的某部分有关系

QUESTION 27

Sales data of a company is stored in two tables, SALES1 and SALES2, with some data being uplicated across the tables. You want to display the results from the SALES1 table, which are not present in the SALES2 table.

Which set operator generates the required output?

A. INTERSECT

B. UNION

C. PLUS

D. MINUS

E. SUBTRACT

答案：D

解析：集合操作符：

1. UNION ALL 不删除重复行的合并，UNION 删除重复行的合并；
2. MINUS 从一个行集中去除属于另一个行集；
3. INTERSECT 返回重复的行集。

QUESTION 28

Evaluate the following ALTER TABLE statement:

ALTER TABLE orders
SET UNUSED (order_date);

Which statement is true?

A. After executing the ALTER TABLE command, you can add a new column called ORDER_DATE to the ORDERS table.

B. The ORDER_DATE column must be empty for the ALTER TABLE command to execute successfully.

C. ROLLBACK can be used to get back the ORDER_DATE column in the ORDERS table.

D. The DESCRIBE command would still display the ORDER_DATE column.

答案：A

解析：SET UNUSED：

1. 这一列上的任何约束或索引都一并删除；
2. 无法回滚（ROLLBACK）；
3. 可添加重名的列；
4. 无法查看到该列的信息。

QUESTION 29

Evaluate the following SQL statements that are issued in the given order:

CREATE TABLE emp
(emp_no NUMBER(2) CONSTRAINT emp_emp_no_pk PRIMARY KEY,
ename VARCHAR2(15),
salary NUMBER (8,2),
mgr_no NUMBER(2) CONSTRAINT emp_mgr_fk REFERENCES emp(emp_no));

ALTER TABLE emp
DISABLE CONSTRAINT emp_emp_no_pk CASCADE;

ALTER TABLE emp
ENABLE CONSTRAINT emp_emp_no_pk;

What would be the status of the foreign key EMP_MGR_PK?

A. It would remain disabled and can be enabled only by dropping the foreign key constraint and recreating it.

B. It would remain disabled and has to be enabled manually using the ALTER TABLE command.

C. It would be automatically enabled and immediate.

D. It would be automatically enabled and deferred.

答案：B

解析：自关联，表的外键关联同一张表的主键；第二条语句执行后，主键失效，外键也失效；第三条语句执行后，主键生效，外键仍然失效

QUESTION 30

Which three statements are true regarding the data types? (Choose three.)

A. The minimum column width that can be specified for a VARCHAR2 data type column is one.

B. Only one LONG column can be used per table.

C. A TIMESTAMP data type column stores only time values with fractional seconds.

D. The BLOB data type column is used to store binary data in an operating system file.

E. The value for a CHAR data type column is blank-padded to the maximum defined column width.

答案：ABE

解析：数据类型：

1. CHAR（n）和VARCHAR2（n），两者的n默认值都是1，前者可省略，后者不能省略；
2. LONG类型的列，一个表中只能有一列；
3. BLOB：Binary，CLOB：Character，NCLOB：UNICODE Character。

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

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