Oracle Database 12c SQL OCA/OCP 1Z0-071題庫（1-5題）

QUESTION 1

Evaluate the following SQL statement:

<code>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/<code>

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>

(1) FROM <table>

(3) <join> JOIN <table>/<join>

(2) ON <join>

(4) WHERE <where>

(5) GROUP BY <group>

(6) HAVING <having>

(9) ORDER BY <order>

(10) LIMIT <limit>

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共用一個命名空間），而且角色不屬於任何用戶，也不存在任何用戶模式中。角色的描述存放在數據字典中。

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

閱讀更多 洞察數據 的文章

關鍵字: BY2 SQL 題庫