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

QUESTION 1

Choose the best answer.

Exanine the desciption of the EMPLOYEES
table:

Nane Null Type

---

EMP_ID NOT NUL NUMBER

EMP_NAME VARCHAR2
(40)

DEPT_ID NUMBER(2)

SALARY NUMBER(8,2)

JOIN_DATE DATE

Which query is valid?

A) SELECT dept_id, join_date, SUM(salary)
FROM employees GROUP BY dept_id, join_date;

B) SELECT depe_id,join_date, SUM(salary) FROM
employees GROUP BY dept_id:

C) SELECT dept_id,MAX (AVG (salary)) FROM
employees GROUP BY dept_id;

D) SELECT dept_ id,AVG (MAX (salary)) FROM
employees GROUP BY dapt_id;

Correct Answer: A

QUESTION 2

choose three

Which three are true about the CREATE TABLE
command?

A) It can include the CREATE…INDEX
statement for creating an index to enforce the primary key constraint.

B) The owner of the table should have space
quota available on the tablespace where the table is defined.

C) It implicitly executes a commit.

D) It implicitly rolls back any pending
transactions.

E) A user must have the CREATE ANY TABLE
privilege to create tables,

F) The owner of the table must have the
UNLIMITED TABLESPACE system privilege

Correct Answer: ABC

QUESTION 3

choose two

The CUSTOMERS table has a CUST_CREDT_LIMIT
column of data type number.

Which two queries execute successtully?

A) SELECT TO_CHAR(NVL(cust_credit_limit *
.15,‘Not Available’)) FROM customers;

B) SELECT NVL2(cust_credit_limit * .15,‘Not
AvailabIe’) FROM customers;

C) SELECT NVL(cust_credit_limit * .15, ‘Not
Available’) FROM customers;

D) SLECT NVL(TO_CHAR(cust_credit_limit *
.15),‘Not available’) from customers;

E) SELECT
NVL2(cust_credit_limit,TO_CHAR(cust_credit_limit * .15),‘NOT Available’) FROM
customers;

Correct Answer: DE

解析：NVL 函数的参数数据类型需一样，NVL2
函数的第二个参数与第三个参数数据类型需要一样。

NVL（表达式1，表达式2） 如果表达式1为空值，NVL返回值为表达式2的值，否则返回表达式1的值

NVL2(表达式1，表达式2，表达式3） 如果表达式1为空，返回值为表达式3的值。如果表达式1不为空，返回值为表达式2的值。

QUESTION 4

choose two

Exammine the desciption of the PRODUCT DETALS
table:

NAME NULL
TYPE

---

PRODUCT_ID NOT NULL NUMBER(2)

PRODUCT_NAME NOT NULL VARCHAR2(25)

PRODUCT_PRICE NUMBER(8,2)

EXPIRY_DATE DATE

Which two statements are true?

A) PRODUCT_ID can be assigned the PEIMARY KEY
constraint.

B) EXPIRY_DATE cannot be used in arithmetic
expressions.

C) EXPIRY_DATE contains the SYSDATE by defalt
if no date is assigned to it

D) PRODUCT_PRICE can be used in an arithmetic
expression even if it has no value stored in it

E) PRODUCT_PRICE contains the value zero by
default if no value is assigned to it.

F) PRODUCT_NAME cannot contain dupicate
values.

Correct Answer: AD

解析：D 选项当价格列没有值的时候，做算术表达式运算依然为没有值，但是不影响做算术表达式运算。

QUESTION 5

choose the best answer

The CUSTOMERS table has a CUST_LAST_NAME
column of data type VARCHAR2.

The table has two rows whose COST_LAST_MANE
values are Anderson and Ausson.

Which query produces output for
CUST_LAST_SAME containing Oder for the first row

and Aus for the second?

A) SELECT REPLACE (REPLACE(cust_last_name,‘son’,’’),‘An’,‘O’)
FROM customers;

B) SELECT REPLACE (TRIM(TRALING ‘son’ FROM
cust_last_name), ‘An’,‘O’) FROM customers;

C) SELECT INITCAP (REPLACE(TRIM(‘son’ FROM
cust_last_name),‘An’,‘O’)) FROM customers;

D) SELECT REPLACE (SUBSTR(cust_last_name,-3),
‘An’, ‘O’) FROM customers;

Correct Answer: A

只能处理过滤掉一个字符，不能过滤多个字符，所以BC
可以排除。

TRIM

我们看下trim函数的语法描述：trim(
[ { {leading|trailing|both} [trim_character]|trim_character} from] trim_source

1)、不使用任何参数

SQL> select trim(’
11 ') aa from dual;

2)、使用both参数，效果等同于方法一不使用任何参数

SQL> select trim(both from ’ 11
') aa from dual;

3)、使用leading与trailing参数

SQL> select trim(leading from ’ 11 ')
aa from dual;

4)、使用trim_character参数

trim_character参数改变了“删除空格”的默认行为。如果想要删除字符串’xxxxWORLDxxxx’前后出现的“x”，“trim_character”参数就派上用场了。

SQL> select trim(‘x’ from ‘xxxxWORLDxxxx’) aaaaa from
dual;

trim_character配合“both”、“trailing”和“leading”三个参数使用效果如下，与之前演示类似。看结果，不赘述。

SQL> select trim(both ‘x’ from ‘xxxxWORLDxxxx’) aaaaa
from dual;

必须注意的一点是这里的“trim_character”参数只允许包含一个字符，不支持多字符。trim不能满足我们去除多字符要求，但是我们可以使用rtrim和ltrim来处理。

1）使用rtrim

SQL> select rtrim(‘ORxxxxWORLDxxxxOR’,‘OR’) aaaaa from
dual;