1z0-071 Oracle Database 12c SQL

1z0-071 Oracle Database 12c SQL 第1题 ORDER BY语句

Q1.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;

Which ORDER BY clause are valid for the above query?(Choose all that apply.)

 

A.ORDER BY 2,1

B.ORDER BY CUST_NO

C.ORDER BY 2,cust_id

D.ORDER BY "CUST_NO"

E.ORDER BY "Last Name"

Answer:A,C,E

联合查询，使用order by排序的话，以第一个查询的列为准，不能使用第二个查询的列。

解析：union合并的时候，是按照第一条sql语句走的。

A：正确，通过占位符合走的，与列别名没有关系

B：错误，第一条sql没有cust_no

C：正确，2是占位符合，cust_id第一条也存在

D：错误，与B一样的错误

E：正确，第一条sql存在Last Name

 

1z0-071 Oracle Database 12c SQL 第2题 WHERE和HAVING条件

Q2. Which statements are true regarding the WHERE and HAVING clauses in a SELECT statement? (Choose all that apply.)

 

A. The HAVING clause can be used with aggregate functions in subqueries.

B. The WHERE clause can be used to exclude rows after dividing them into groups.

C. The WHERE clause can be used to exclude rows before dividing them into groups.

D. The aggregate functons and columns used in the HAVING clause must be specifed in the SELECT list of the query.

E. The WHERE and HAVING clauses can be used in the same statement only if they are applied to difierent columns in the table.

 

Answer: A, C

 

对于 B 选项，子句要在 group 语句之前，不能之后排除。

对于 D 选项，聚合函数和 having 子句指定的列不一定要在 select 列中出现。

对于 E 选项，where 和 having 可以是同一列。

1z0-071 Oracle Database 12c SQL 第3题 external table外部表

Q3. Which statement is true regarding external tables?

A. The default REJECT LIMIT for external tables is UNLIMITED.
B. The data and metadata for an external table are stored outside the database.
C. ORACLE_LOADER and ORACLE_DATAPUMP have exactly the same functionality when used with an external table.
D. The CREATE TABLE AS SELECT statement can be used to upload data into regular table in the database from an external table.

Correct Answer: D

本题解释：

A 选项错误的原因是，该子句的默认值是 0 而不是无限；

B 错误是因为源数据是应存在于数据库中；

C 错误是因为他们的功能不一样，一个是 load 时用，一个是 unload 时用；

D CREATE TABLE AS SELECT语句可用于将数据从外部表中装载到数据库中的常规表中。