Oracle 1z0-071 2019.10最新题库&解答56-60 含上传地址

复制到BLOG图片等均不好处理，一次也不能复制太多，我直接将题库上传上。
https://download.youkuaiyun.com/download/QIDANIEL/12009829

QUESTION 55

Choose two

Examine this query:

SELECT employee_id,first_name,salary

FROM employees

WHERE hire_date > ‘&1’;

Which two methods should you use to prevent prompting for
a hire date value when this

query is executed?

A) Use the DEFINE command before executing the query.

B) Store the query in a script and pass the substitution
value to the script when executing it.

C) Replace ‘&1’ with ‘&&1’ in the query.

D) Execute the SET VERIFY OFF command before executing
the query.

E) Use the UNDEFINE command before executing the query.

F) Execute the SET VERIFY ON command before executing the
query.

Correct Answer: AB

考点：禁止提示输入&1 变量的值，那么提前声明变量赋值

QUESTION 56 passs

choose the best answer

Examine the desciption of the CUSTOMERS table:

Name Null? Type

CUST_ID Not NULL VARCHAR2(6)

FIRST_NAME VARCHAR2(50)

LAST_NAME Not NULL VARCHAR2(50)

ADDRRESS VARCHAR2(50)

CITY VARCHAR2(25)

You want to display detalls of all castomers who reside in cities starting
with the letter D

followed by at least two character

Which query can be used?

A) SELECT * FROM customers WHERE city = ‘D_%’;

B) SELECT * FROM customers WHERE city = ‘%D_’;

C) SELECT * FROM customers WHERE city LIKE ‘D_ _%’;

D) SELECT * FROM customers WHERE city LIKE ‘D_’;

Correct Answer: C

解析：考察like 模糊匹配

QUESTION 57

COST INCONE LEVEL

VARCHAR2 (30)

COST CREDIT LIMIT

NONBER

For Customers whose ncomne level has a vlue, you want to
dslay the ist name and due

amount 5% of thelir aedit limit.Qustomers whose due
amount is null should not be displayed.

Which query should be used?

A) SEILECT cust. fist name, cuat credit_imit*.o5 aS DOE ANONE

PROM customes

WHERE cust income level18 NOT NULL

AND due anount IS NOT NULLI

B) SELECT cust first namo, cust credit_1imit…05A
POE_AMOUNT

FROM customors

WHERE cust income_ lovel 1- NOLL

AND duo amount 1= NULL2

C) SELECT cuat first. name, cunt cedt_imitTOSNOOE-MOum

FROM customers

WHIERE cust incme Level IS NOT NUL

AND cust credit 1imit IS NOT WULE

D) sECT cust first sam cusE cetd-_iancC-OS-COT-MO

FROM customers

WHERE cust incom lovel< NOLL

AND due amount

amount < MULL

●.05 AS DUEAMOONT

E)sELecr cust first name, cat_cditima

FROM custcmers

WHERE Cust incme leovel 1 NUL

AND Cust credit 1eva1 1”NoULi

Correct Answer: C

QUESTION 58

Which two statements are true about a full outer join?

A) It includes rows that are returned by an inner join.

B) The Oracle join operator (+) must be used on both
sides of the join condition in the WHERE clause.

C) It includes rows that are returned by a Cartesian
product.

D) It returns matched and unmatched rows from both tables
being joined.

E) It returns only unmatched rows from both tables being
joined.

Correct Answer: AD

Cartesian product 笛卡尔乘积

CROSS JOIN就是笛卡尔乘积连接，不需要任何关联条件，实现M*N的结果集，其实这种SQL JOIN方式基本上只在理论上有意义，实际当中，很少有用的CORSS JOIN方式。

full outer join 全连接，返回左右表中的所有记录

QUESTION 59

You want to write a query that prompts for two column
names and the WHERE condition

each time It is executed in a session but only prompts
for the table name the first time it is

executed. The variables used in your query are never
undefined in your session. Which query

can be used?

A) SELECT scol1, scol2

FROM “table”

WHERE condit ion;

B) SELECT 56col1, 6&col2

FROM &table

WHERE condition;

C) SELECT ‘6co11’,

‘&&col2’

FROM &table

WHERE ‘G&condition’ = ‘econd’;

D) SELECT ssco11, 6&co12

FROM table

WHERE &&condition = s&cond;

E) SELECT coll, 6col2

FROM &&table

WHERE scondition;

Correct Answer: E

&是oracle里的替代变量。如果有一个&name，在sqlplus执行的时候，会提示你输入一个name对应的值。然后name的值会替代到sql里，这里是替代，不是参数绑定，所以可以替换sql中的静态部分，比如字段名，表名

&&也是替代变量，和&一样的功能，不过他是多次替代，

QUESTION 60

Which three actions can you perform by using the ORACLE DATAPUMP access
driver?

A) Create a directory object for an external table.

B) Read data from an external table and load it into a table in the
database.

C) Query data from an external table.

D) Create a directory object for a flat file.

E) Execute DML statements on an extemal table.

F) Read data from a table in the database and insert it into an extemal
table.

Correct Answer:ACF

建立外部表的步骤：

1、创建以“,”分隔的文件“TestTable.csv”至“D:\Test”

2、创建一个Directory：

create directory TestTable_diras

‘D:\Test’ ;

3、创建一个外部表：

create table TestTable(

  ID varchar2 ( 10 ),

  NAME varchar2 ( 20 ))

organization external (

  type oracle_loader

  default directory

TestTable_dir

  access parameters (fields

terminatedby ‘,’ )

  location ( 'TestTable.csv' )

  );

各类参数说明

1、type

oracle_loader

  数据转换驱动器，oracle_loader为默认，也可以改换其他

2、defaultdirectory

TestTable_dir

   location ('TestTable.csv')

  指定外部表所在文件夹以及指定文件

3、accessparameters

 设置转换参数，例如(fields

terminatedby’,’)表示以’,'为字段间的分隔符

  ● 参数由访问驱动程序定义

1.外部表可以加载和卸载数据泵格式的数据，只需把organization external里的参数type设置为oracle_datapump。

        create table

all_objects_unload

        organization external

               (

               type

oracle_datapump

              default directory

testdir

location(‘allobjects.dat’)

              )

        as

        select * from

all_objects

https://blog.youkuaiyun.com/danevc/article/details/54966331