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

QUESTION 31

Choose two

Examine the data in the CUST_NAME column of the CUSTOMERS
table:

CUST_NAME

---

Renske Ladwig

Jason Mallin

Samuel McCain

Allan MCEwen

Irene Mikkilineni

Julia Nayer

You want to display the CUST_NAME values where the last
name starts with Mc or MC.

Which two WHERE clauses give the required result?

A) WHERE INITCAP (SUBSTR(cust_name, INSTR(cust_name,’ ')
+1 ) ) IN (‘MC%’, 'Mc%)

B) WHERE UPPER (SUBSTR(cust_name, INSTR(cust_name, ’ ')
+1 ) ) LIKE UPPER(‘MC%’)

C) WHERE INITCAP(SUBSTR(cust_name, INSTR(cust_name,’ ')
+1 ) ) LIKE ‘Mc%’

D) WHERE SUBSTR(cust_name,INSTR(cust_name,’ ') +1
) LIKE ‘Mc%’ OR ‘MC%’

E) WHERE SUBSTR(cust_name, INSTR(cust_name,’ ') +1 ) LIKE ‘Mc%’

Correct Answer: BC

select cust_name,substr(cust_name,instr(cust_name,’ ')+1) from t_cusname

where  UPPER(substr(cust_name,instr(cust_name,’
')+1)) like UPPER(‘MC%’)

where 
INITCAP(substr(cust_name,instr(cust_name,’ ')+1)) like ‘Mc%’

假设c为一字符串.函数INITCAP()是将每个单词的第一个字母大写,其它字母变为小写返回.单词由空格,控制字符,标点符号等非字母符号限制.  select initcap(‘hello
world’) from dual;

A 查不出任何结果

1.188 NLS_COMP

Note:The value of this initialization parameter NLS_COMP is used to
initialize the session value of this parameter, which is the actual value
referenced by the SQL query processing. This initial value is overridden by a
client-side value if the client uses the Oracle JDBC driver or if the client is
OCI-based and the NLS_LANG client setting (environment variable) is defined.
The initialization parameter value is, therefore, usually ignored.

https://docs.oracle.com/en/database/oracle/oracle-database/12.2/refrn/NLS_LANGUAGE.html#GUID-0DD64F19-BEF2-4B4B-A232-3BA3F8494949

QUESTION 32

choose three

Examine this SQL statement:

SELECT cust_id, cus_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

Identify three ORDER BY clauses, any one of which can
complete the query successfully.

A) ORDERBY 2, 1

B) ORDER BY “CUST_NO”

C) ORDER BY 2,cust_id

D) ORDER BY CUST_NO

E) ORDER BY “Last Name”

Correct Answer: ACE

UNION 使用第一行的列名

QUESTION 33

Choose three.

Which three statements are true about a self join?

A) It must be an inner join.

B) It can be an outer join.

C) The ON clause must be used.

D) It must be an equijoin.

E) The query must use twodifferent aliases for the table.

F) The ON clause can be used.

Correct Answer: BEF

考察自连接，两个表必须有别名

QUESTION 34

Choose two.

Which two statements are true about the results of using
the INTERSECT operator in compound queres?

A) Reversing the order of the intersected tables can
sometimes affect the output.

B) Column names in each SELECT in the compound query can
be dfferent.

C) INTERSECT returns rows common to both sides of the
compound query.

D) The number of columns in each SELECT in the compound
query can be dfferent.

E) INTERSECT ignores NULLs

Correct Answer: BC

考察交集

intersect:　　返回查询结果中相同的部分（交集）。

union,union
all:　　将查询的结果组合后返回，
union会过滤重复，union all不过滤重复。

minus:　　返回在第一个查询结果中包含第二个查询结果中不包含的数据。

QUESTION 35

choose two

Which two statements ane true about the COUNT function?

A) It can only be used for NUMBER data types.

B) COUNT (DISTINCT inv_amt) returns the number of rows
excluding rows containing dupicates and NULLs in the INV_AMT column

C) COUNT(*) returns the number of rows in a table
including duplicate rows and rows containing NULLs in any column.

D) A SELECT statement using the COUNT function with a
DISTINCT keyword cannot have a WHERE clause.

E) COUNT(inv_amt) returns the number of rows in a table
including rows with NULL in the INV_AMT column.

Correct Answer: BC

解析：count 统计非空值，加上distinct 之后统计非空与惟一值