Oracle 1z0-071 2019.10最新题库&解答16-20

QUESTION 16

Choose two.

Examine this partial command:

CREATE TABLE cust (

cust_id NUMBER(2),

credit_ limit NUMBER(10)

)

ORGANIZATION EXTERNAL

Which two clauses are required for this
command to execute successfully?

A) the DEFAULT DIRECTORY clause

B) the REJECT LIMIT clause

C) the LOCATION clause

D) the ACCESS PARAMETERS clause

E) the access driver TYPE clause

Answer：AC

(解析：外部表建表语句，上面 Answer：看起来都对。但是通过测试，发现 E Answer：可以不需要指定，但是
directory 必须要制定，还有访问参数也必须要指定，访问参数里面位置参数必须要指定，)

QUESTION 17

choose two

Examine this business rule:

Each student can work on multiple
projects and each project can have multiple students

You must design an Entity Relations (ER)
model for optional data storage and allow generating reports in this format:

STUDENT_ID  FIRST_NAME 
LAST_NAME

PROJECT_ID  PROJECT_NAME 
PROJECT_TASK

Which two statements are true?

A) An associative table must be created
with a composite key of STUDENT_ID and PROJECT_ID,which is the foreign key
linked to the STUDENTS and PROJECTS entities.

B) The ER must have a many-to-many relationship
between the STUDENTS and PROJECTS entities that must be resolved into 1-to-many
relationships.

C) PROJECT_ID must be the primary key in
the PROJECTS entity and foreign key in the STUDENTS entity.

D) The ER must have a 1-to-many
relationship between the STUDENTS and PROJECTS entities.

E) STUDENT_ID must be the primary key in
the STUDENTS entity and foreign key in the PROJECTS entity.

Answer：AB

(解析：这道题涉及到调优问题，应用中经常出现多对多之间的关系，如何解决 STUDENTS and PROJECTS 两张表的多对多关系，可以创建一个中间表，这个表包含那两张表的主键列，同时是另外两张表的外键约束。通常这个表建成索引组织表。)

resolve into  使分解为；归结为；还原成

QUESTION 18

Choose two

Which two statements are true about the WHERE and HAVING clauses
in a SELECT

statement?

A) The WHERE clause can be used to exclude rows after dividing
them into groups

B) WHERE and HAVING clauses can be used In the same
statement only if applied to different table columns.

C) The HAVING clause can be used with aggregating
functions in subqueries.

D) Aggregating functions and columns used in HAVING
clauses must be specified in this SELECT list of a query.

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

Correct Answer: CE

having 子句是可以跟子查询进行配合使用，而且子查询中也可以有分组函数;

Oracle 先过滤再分组，照样子可以减少数据的处理数量;

QUESTION 19

Choose two

The INVOICE table has a QTY_SOLD column of data type NUMBER and an
INVOTCE_DATE

column of data type DATE

NLS_DATE_FORMAT is set to DD-MON-RR.

Which two are true about data type conversions involving these columns in
query

expressions?

A) invoice_date > ‘01-02-2019’ : uses implicit conversion

B) qty_sold =’ 05549821 ’ : requires explicit conversion

C) CONCAT(qty_sold, invoice_date) : reuires explicit conversion

D) qty_sold BETWEEN ‘101’ AND ‘110’ : uses implicit convesion

E) invoice_date = ‘15-march-2019’ : uses implicit conversion

Correct Answer: DE

解析：A 选项不会执行成功，和时间格式不匹配

D 隐式转换，数值和字符的转换

Implicit conversion:隐式转换

Explicit conversion:显式转换

这道题考察的显式转换和隐式转换

QUESTION 20

Choose two

The PRODUCT_INFORMATION table has a UNIT_PRICE column of
data type NUMBER(8, 2).

Evaluate this SQL statement:

SELECT TO_CHAR(unit_price,’$9,999’) FROM
Product_intormation;

Which two statements are true about the output?

A) A row whose UNIT_PRICE column contains the value
1023.99 will be displayed as $1,024.

B) A row whose UNIT_PRICE column contains the value
1023.99 will be displayed a5 $1,023.

C) A row whose UNIT_PRICE column contains the value
10235.99 will be displayed as $1,0236.

D) A row whose UNIT_PRICE column contains the value
10235.99 will be displayed as $1,023.

E) A row whose UNIT_PRICE column contains the value
10235.99 will be displayed as #####

Correct Answer: AE