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

QUESTION 1

Evaluate the following 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

Identify three ORDER BY clauses either one of which can complete the query.

A. ORDER BY "Last name"

B. ORDER BY 2, cust_id

C. ORDER BY CUST_NO

D. ORDER BY 2, 1

E. ORDER BY "CUST_NO"

答案：ABD

解析：

集合操作符情况下的ORDER BY，可以根据位置进行排序（ORDER BY 2），可以根据引用进行排序（ORDER BY Column_name）。但是需要注意：

1. ORDER BY 子句只能出现所有SELECT语句结尾处，而且只能出现一次。
2. 每个单独的SELECT 语句中不能有ORDER BY 子句
3. 当使用ORDER BY 引用时，起作用的列名是第一条SELECT语句中的列名

QUESTION 2

Which three statements are true regarding the WHERE and HAVING clauses in a SQL statement? (Choose three.)

A. WHERE and HAVING clauses cannot be used together in a SQL statement.

B. The HAVING clause conditions can have aggregate functions.

C. The HAVING clause conditions can use aliases for the columns.

D. The WHERE clause is used to exclude rows before the grouping of data.

E. The HAVING clause is used to exclude one or more aggregated results after grouping data.

答案：BDE

解析：HAVING子句是SELECT中特有的可选子句，用于排除GROUP BY子句定义的特定行组，需要注意：

1. GROUP BY子句和HAVING子句不是必须的，如果使用它们，则必须位于WHERE 子句之后，但是两者的顺序不固定。
2. HAVING子句本身不定义行组，这些行组已经有GROUP BY 定义，所以HAVING自己中使用的表达式只能引用GROUP BY 中定义的组（列）和聚合函数。（聚合函数中引用的列/组可以是GROUP BY未指定的）
3. GROUP BY不能使用列别名，因为在执行过程中内存中还没有列别名：

SQL语句执行的顺序：

(7) SELECT

(8) DISTINCT <select_list>

(1) FROM <table_name>

(3) <join_type> JOIN <table_name>

(2) ON <join_condition>

(4) WHERE <where_condition>

(5) GROUP BY <group_by_list>

(6) HAVING <having_condition>

(9) ORDER BY <order_by_condition>

(10) LIMIT <limit_number>

Oracle 严格执行该顺序，Mysql可能有变化。

QUESTION 3

Which statement is true regarding external tables?

A. The CREATE TABLE AS SELECT statement can be used to upload data into a normal table in the

database from an external table.

B. The data and metadata for an external table are stored outside the database.

C. The default REJECT LIMIT for external tables is UNLIMITED.

D. ORACLE_LOADER and ORACLE_DATAPUMP have exactly the same functionality when used with an external table.

答案：A

解析：外部表是一个只读表，这个表在数据库内部定义，但是存在数据库外部，所以，外部表的元数据（metadata）存在数据库内部，数据则存在外部。注意点：

1. 不允许再外部表中创建LOB列。
2. 不能再外部表中添加约束
3. 不能将外部表中的列设置为UNUSED。如果这么设置了，SQL会执行这条语句，实际上是将该列删除。
4. REJECT LIMIT的默认值是0.

QUESTION 4

Which two statements are true about Data Manipulation Language (DML) statements?

A. An INSERT INTO...VALUES.. statement can add multiple rows per execution to a table.

B. An UPDATE... SET... statement can modify multiple rows based on multiple conditions on a table.

C. A DELETE FROM..... statement can remove rows based on only a single condition on a table.

D. An INSERT INTO... VALUES..... statement can add a single row based on multiple conditions on a

table.

E. A DELETE FROM..... statement can remove multiple rows based on multiple conditions on a table.

F. An UPDATE....SET.... statement can modify multiple rows based on only a single condition on a table.

答案：BE

解析：

1. INSERT一次可以插入一行值，必须明确的值或是有明确结果的表达式
2. UPDATE/DELETE，可以使用一个表达式或多个表达式，更新或删除0行、1行或多行。

QUESTION 5

Which two statements are true regarding roles? (Choose two.)

A. A role can be granted to itself.

B. A role can be granted to PUBLIC.

C. A user can be granted only one role at any point of time.

D. The REVOKE command can be used to remove privileges but not roles from other users.

E. Roles are named groups of related privileges that can be granted to users or other roles.

答案：BE

解析：角色只是一个由权限组成的集合，这组权限可以通过这个名字授予用户或者其他的角色。但是一个角色不能授予自己，也不能循环授予。

一个角色即包括系统权限，也可以包括对象权限。每一个角色在系统中必须是唯一的，即不能与任何现有的用户名和角色名重用（USER和ROLE共用一个命名空间），而且角色不属于任何用户，也不存在任何用户模式中。角色的描述存放在数据字典中。

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

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