Oracle 1z0-071 2019.10最新题库&解答11-15

QUESTION 11

Choose two.

Which two statements are true about a self
join?

A) The join key column must have an index.

B) It can be a left outer join.

C) It must be a full outer join.

D) It can be an inner join.

E) It must be an equijoin.

Correct Answer: BD

QUESTION 12

Choone two

Which three statements are true about
dropping and unused columns in an Oracle database?

A) A primary key column referenced by another
column as a foreign key can be dropped if using the CASCADE option.

B) A DROP COLUMN command can be rolled back

C) An UNUSED column’s space is reclaimed
automatically when the block containing that column is next queried

D) An UNUSED column’s space is reclaimed
automatically when the row containing that column is next queried.

E) Partition key columns cannot be dropped.

F) A column that is set to NNUSED still
counts towards the limit of 1000 columns per table.

Correct Answer: AEF

ORA-12984: 无法删除分区列，E 对;

删除主键列时可以加 cascade 选项，同时把主外键约束一起删除。

官档：SQL langauage reference

20.7.8.2 Marking Columns Unused

If you are concerned about the length of time it could
take to drop column data from all of the rows in a large table, you can use the
ALTER TABLE…SET UNUSED statement.

This statement marks one or more columns as unused, but
does not actually remove the target column data or restore the disk space
occupied by these columns. However, a column that is marked as unused is not
displayed in queries or data dictionary views, and its name is removed so that
a new column can reuse that name. In most cases, constraints, indexes, and
statistics defined on the column are also removed. The exception is that any
internal indexes for LOB columns that are marked unused are not removed.

To mark the hiredate and mgr columns as unused, execute
the following statement:

ALTER TABLE hr.admin_emp SET UNUSED (hiredate, mgr);

ALTER TABLE TESTD2 DROP UNUSED COLUMNS;

You can later remove columns that are marked as unused by
issuing an ALTER TABLE…DROP UNUSED COLUMNS statement. Unused columns are also
removed from the target table whenever an explicit drop of any particular
column or columns of the table is issued.

The data dictionary views USER_UNUSED_COL_TABS,
ALL_UNUSED_COL_TABS, or DBA_UNUSED_COL_TABS can be used to list all tables
containing unused columns. The COUNT field shows the number of unused columns
in the table.

https://docs.oracle.com/en/database/oracle/oracle-database/19/admin/managing-tables.html#GUID-1AB2E206-EC3E-45CF-93F6-07C6C070FD5F

QUESTION 13

Choose the best answer.

Examine this query:

SELECT TRUNC (ROUND(156.00,-2),-1) FROM DUAL;

What is the result?

A) 16

B) 160

C) 150

D) 200

E) 100

Correct Answer: D

QUESTION 14

Choose two.

Examine this SQL statement:

UPDATE orders o SET customer_name =

(SELECT cust_last_name FROM customers

WHERE customer_id=o.customer_id);

Which two are true?

A) The subquery is executed before the UPDATE
statement is executed.

B) All existing rows in the ORDERS table are
updated.

C) The subquery is executed for every updated
row in the ORDERS table.

D) The UPDATE statement executes successfully
even if the subquery selects multiple rows.

E) The subquery is not a correlated subquery.

Correct Answer: BC

解析：

(解析：这道题考的就是关联子查询，类似题目以前有考过。实验证明 orders 的表所有行都会被更新到，但是不满足条件的是不会具体赋值，至少有更新的动作。

关联子查询：

1、 先执行主查询，对于主查询返回的每一行数据，都会造成子查询执行一次

2、 然后子查询返回的结果又传给主查询

3、 主查询根据返回的记录做出判断

) bd 似乎更正确

QUESTION 15

Choose three

Name
Null? Type

PRODUCT_ID
NOT NULL NUMBER(2)

PRODUCT_NANE
VARCHAR2(10)

UNIT_PRICE
NUMBER(3)

PURCHARGE
VARCHAR2(2)

EXPIRY_DATE

DATE

DELIVERY_DATE
DATE

Which three queres use valid expressions?

A) SELECT produet_id, unit_pricer, 5
“Discount”,unit_price+purcharge-discount FROM products;

B) SELECT product_id, (unit_price * 0.15 / (4.75 +
552.25)) FROM products;

C) SELECT ptoduct_id, (expiry_date - delivery_date) * 2
FROM products;

D) SPLECT product_id, expiry_date * 2 FROM products;

E) SELEGT product_id, unit_price, unit_price + Purcharge
FROM products;

F) SELECT product_id,unit_price || “Discount” ,
unit_price + surcharge - discount FROM products;

Correct Answer: BCE

解析：如果字符型的存储的是数字，那么查询的时候 oracle 会自动转化，否则会转化失败;

A 的 Answer：5
"Discount"就是添加一个新列，值为 5，但是虚拟的列不能放在数学表达式中。

Answer：F 中不能是双引号，字符串要用单引号，别名用双引号。

在Oracle中，||运算符可以将两个或两个以上的字符串连接在一起。本章节要为大家带来的就是||运算符的语法及使用示例。

string1 || string2 [ || string_n ]

string1： 第一个要连接的字符串。

string2：第二个要连接的字符串。

string_n：可选项，第n个要连接的字符串

select 151 || “char” from
dual; 00904. 00000 - "%s: invalid identifier

QUESTION 11

Choose two.

Which two statements are true about a self
join?

A) The join key column must have an index.

B) It can be a left outer join.

C) It must be a full outer join.

D) It can be an inner join.

E) It must be an equijoin.

Correct Answer: BD

QUESTION 12

Choone two

Which three statements are true about
dropping and unused columns in an Oracle database?

A) A primary key column referenced by another
column as a foreign key can be dropped if using the CASCADE option.

B) A DROP COLUMN command can be rolled back

C) An UNUSED column’s space is reclaimed
automatically when the block containing that column is next queried

D) An UNUSED column’s space is reclaimed
automatically when the row containing that column is next queried.

E) Partition key columns cannot be dropped.

F) A column that is set to NNUSED still
counts towards the limit of 1000 columns per table.

Correct Answer: AEF

ORA-12984: 无法删除分区列，E 对;

删除主键列时可以加 cascade 选项，同时把主外键约束一起删除。

官档：SQL langauage reference

20.7.8.2 Marking Columns Unused

If you are concerned about the length of time it could
take to drop column data from all of the rows in a large table, you can use the
ALTER TABLE…SET UNUSED statement.

This statement marks one or more columns as unused, but
does not actually remove the target column data or restore the disk space
occupied by these columns. However, a column that is marked as unused is not
displayed in queries or data dictionary views, and its name is removed so that
a new column can reuse that name. In most cases, constraints, indexes, and
statistics defined on the column are also removed. The exception is that any
internal indexes for LOB columns that are marked unused are not removed.

To mark the hiredate and mgr columns as unused, execute
the following statement:

ALTER TABLE hr.admin_emp SET UNUSED (hiredate, mgr);

ALTER TABLE TESTD2 DROP UNUSED COLUMNS;

You can later remove columns that are marked as unused by
issuing an ALTER TABLE…DROP UNUSED COLUMNS statement. Unused columns are also
removed from the target table whenever an explicit drop of any particular
column or columns of the table is issued.

The data dictionary views USER_UNUSED_COL_TABS,
ALL_UNUSED_COL_TABS, or DBA_UNUSED_COL_TABS can be used to list all tables
containing unused columns. The COUNT field shows the number of unused columns
in the table.

https://docs.oracle.com/en/database/oracle/oracle-database/19/admin/managing-tables.html#GUID-1AB2E206-EC3E-45CF-93F6-07C6C070FD5F

QUESTION 13

Choose the best answer.

Examine this query:

SELECT TRUNC (ROUND(156.00,-2),-1) FROM DUAL;

What is the result?

A) 16

B) 160

C) 150

D) 200

E) 100

Correct Answer: D

QUESTION 14

Choose two.

Examine this SQL statement:

UPDATE orders o SET customer_name =

(SELECT cust_last_name FROM customers

WHERE customer_id=o.customer_id);

Which two are true?

A) The subquery is executed before the UPDATE
statement is executed.

B) All existing rows in the ORDERS table are
updated.

C) The subquery is executed for every updated
row in the ORDERS table.

D) The UPDATE statement executes successfully
even if the subquery selects multiple rows.

E) The subquery is not a correlated subquery.

Correct Answer: BC

解析：

(解析：这道题考的就是关联子查询，类似题目以前有考过。实验证明 orders 的表所有行都会被更新到，但是不满足条件的是不会具体赋值，至少有更新的动作。

关联子查询：

1、 先执行主查询，对于主查询返回的每一行数据，都会造成子查询执行一次

2、 然后子查询返回的结果又传给主查询

3、 主查询根据返回的记录做出判断

) bd 似乎更正确

QUESTION 15

Choose three

Name
Null? Type

PRODUCT_ID NOT NULL NUMBER(2)

PRODUCT_NANE VARCHAR2(10)

UNIT_PRICE NUMBER(3)

PURCHARGE VARCHAR2(2)

EXPIRY_DATE date
DELIVERY_DATE date

Which three queres use valid expressions?

A) SELECT produet_id, unit_pricer, 5
“Discount”,unit_price+purcharge-discount FROM products;

B) SELECT product_id, (unit_price * 0.15 / (4.75 +
552.25)) FROM products;

C) SELECT ptoduct_id, (expiry_date - delivery_date) * 2
FROM products;

D) SPLECT product_id, expiry_date * 2 FROM products;

E) SELEGT product_id, unit_price, unit_price + Purcharge
FROM products;

F) SELECT product_id,unit_price || “Discount” ,
unit_price + surcharge - discount FROM products;

Correct Answer: BCE

解析：如果字符型的存储的是数字，那么查询的时候 oracle 会自动转化，否则会转化失败;

A 的 Answer：5
"Discount"就是添加一个新列，值为 5，但是虚拟的列不能放在数学表达式中。

Answer：F 中不能是双引号，字符串要用单引号，别名用双引号。

在Oracle中，||运算符可以将两个或两个以上的字符串连接在一起。本章节要为大家带来的就是||运算符的语法及使用示例。

string1 || string2 [ || string_n ]

string1： 第一个要连接的字符串。
string2：第二个要连接的字符串。
string_n：可选项，第n个要连接的字符串
select 151 || “char” from
dual; 00904. 00000 - "%s: invalid identifier