Oracle 1z0-071 2019.10最新题库&解答36-40

QUESTION 36

Choose three

Which three statements are true about sequences in a
single instance Oracle database?

A) A sequence’s unallocated cached values are lost if the
instance shuts down.

B) Two or more tables cannot have keys generated from the
same sequence.

C) A sequence number that was allocated can be rolled
back if a transaction fails.

D) A sequence can issue duplicate values.

E) Sequences can always have gaps.

F) A sequence can only be dropped by a DBA.

Correct Answer: ADE

QUESTION 37

Choose the best answer

Examine the desaiption of the PRODUCT_INFORMATION table:

Name NULL?
Type

---

PROD_ID NOT
NULL NUMBER(2)

PROD_NANE VARCRAR2
(10)

LIST_PRICE NUMBER(6,2)

Which query retrieves the number of products with a null
list price?

A) SELECT (COUNT(list_price) FERM Product_intormation
WHERE list_price=NULL;

B) SELECT Count(nvl( list_price,0) )FROM
pruduct_information WHERE list_price is null;

C) SELECT COUNT(DISTINCT list_price) FROM produet_infomation
WHERE list_price is nulll.

D) BELECT COUNT(list_price) from proceduct_information
where list_price is NULL;

Correct Answer: B

解析：直接用count 会排除空值，所以不对，必须要先对空值进行非空处理，再统计

QUESTION 38

choose two.

You need to calculate the number of days from 1st January 2019 until
today.

Dates are stored in the default format of DD-MON-RR.

Which two queries give the required output?

A) SELECT SYSDATE - TO_DATE (’ 01-JANUARY-2019’ ) FROM DUAL;

B) SELECT TO_DATE (SYSDATE, ’ DD/MONTH/YYYY’ ) - ‘01/JANUARY/2019’ FROM
DUAL;

C) SELECT ROUND (SYSDATE - TO_DATE(’ 01/JANUARY/2019’ ) ) FROM DUAL;

D) SELECT TO_CHAR (SYSDATE, ’ DD-MON-YYYY’) - ‘01-JAN-2019’ FROM DUAL;

E) SELECT ROUND (SYSDATE- ‘01-JAN-2019’) FROM DUAL:

Correct Answer: AC

解析：考察to_date 用法及数据类型的转换，运算

C SELECT ROUND(SYSDATE
-to_date(‘01/JAN/2019’)) from dual;

320

E “invalid number”

QUESTION 39 PASS

Choose two

Exanine the desatption of the BOOKS_TRANSACTIONS table

Name Null?
Type

---

THANSACTION_ID NOT
NULL VARCHAR2(6)

TRANSACTION_TYPE VARCHAR2(3)

BORROWED_DATE DATE

BOOK_ID VARCHAR2(6)

MEMBER_ID VARCHAR2(6)

Examine this partial SQL statement:

SELECT * FROM books_transctions;

Which two WHERE conditions give the same result?

A) WHERE borrowed_date = SYSDATE AND (transaction_type= ‘RM’ OR member_id
IN (‘A101’,‘A102’ ) );

B) WHERE borrowed_date = SYSDATE AND transaction_type= ‘RM’ OR member_id
IN (‘A101’,‘A102’ ) ;

C) WHERE borrowed_date = SYSDATE AND (transaction_type= ‘RM’ AND
member_id=‘A101’ OR member_id = ‘A102’) );

D) WHERE (borrowed_date = SYSDATE AND transaction_type= ‘RM’ ) OR
member_id IN (‘A101’,‘A102’ );

E) WHERE borrowed_date = SYSDATE AND (transaction_type= ‘RM’ AND (
member_id=‘A101’ OR member_id = ‘A102’ ) );

Correct Answer: BD

QUESTION 40

Choose two.

Which two statements are true about single row functions?

A) CONCAT : can be used to combine any number of values

B) MOD : returns the quotient of a division operation

C) CEIL : can be used for positive and negative numbers

D) FLOOR : returns the smallest integer greater than or equal to a
specified number

E) TRUNC : can be used with NUMBER and DATE values

Correct
Answer: CE

解析：
Positiv:正数 , quotient
商（除法所得的结果）

CONCAT returns char1 concatenated with char2. Both char1 and char2 can be
any of the data types CHAR, VARCHAR2, NCHAR, NVARCHAR2, CLOB, or NCLOB. The
string returned is in the same character set as char1. Its data type depends on
the data types of the arguments.

MOD returns the remainder of n2 divided by n1. Returns n2
if n1 is 0.

CEIL returns the smallest integer that is greater than or equal to n. The
number n can always be written as the difference of an integer k and a positive
fraction f such that 0 <= f < 1 and n = k - f. The value of CEIL is the
integer k. Thus, the value of CEIL is n itself if and only if n is precisely an
integer.

The TRUNC
(date) function returns date with the time portion of the day truncated to the unit specified by the format model fmt.