Online 1Z0-071 Practice Test

Question 1

View the exhibit and examine the description of SALES and PROMOTIONS tables.

You want to delete rows from the SALES table, where the PROMO_NAME column in the

PROMOTIONS table has either blowout sale or everyday low price as values.

Which three DELETE statements are valid? (Choose three.)

Options :

A : DELETEFROM salesWHERE promo_id = (SELECT promo_idFROM promo_name = ‘blowout sale’)AND promo_id = (SELECT promo_idFROM promotionsWHERE promo_name = ‘everyday low price’)FROM promotionsWHERE promo_name = ‘everyday low price’);

B : DELETEFROM salesWHERE promo_id = (SELECT promo_idFROM promotionsWHERE promo_name = ‘blowout sale’)OR promo_id = (SELECT promo_idFROM promotionsWHERE promo_name = ‘everyday low price’)

C : DELETEFROM salesWHERE promo_id = (SELECT promo_idFROM promotionsWHERE promo_name = ‘blowout sale’)OR promo_name = ‘everyday low price’);

D : DELETEFROM salesWHERE promo_id IN (SELECT promo_idFROM promotionsWHERE promo_name IN = ‘blowout sale’,‘everyday low price’));

Answer: B,C,D

Question 2

Which two are true about external tables that use the ORACLE _DATAPUMP access driver?

Options :

A : Creating an external table creates a directory object.

B : When creating an external table, data can be selected only from a table whose rows are stored in database blocks.

C : When creating an external table, data can be selected from another external table or from a table whose rows are stored in database blocks.

D : Creating an external table creates a dump file that can be used by an external table in the same or a different database.

E : Creating an external table creates a dump file that can be used only by an external table in the same database.

Answer: B,D

Question 3

Which statement is true about an inner join specified in the WHERE clause of a query?

Options :

A : It must have primary-key and foreign-key constraints defined on the columns used in the join condition.

B : It requires the column names to be the same in all tables used for the join conditions.

C : It is applicable for equijoin and nonequijoin conditions.

D : It is applicable for only equijoin conditions.

Answer: C

Question 4

Examine these statements executed in a single Oracle session:

Which three statements are true?

Options :

A : The code for pen is 10.

B : There is no row containing fountain pen.

C : There is no row containing pencil.

D : The code for pen is 1.

E : The code for fountain pen is 3

F : There is no row containing pen

Answer: A,C,F

Question 5

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

You want to extract only those customer names that have three names and display the * symbol in

place of the first name as follows:

Which two queries give the required output?

Options :

A : SELECT LPAD(SUBSTR(cust_name, INSTR(cust_name, ' ')),LENGTH(cust_name),'*') “CUST NAME”FROM customersWHERE INSTR(cust_name, ' ',1,2)<>0;

B : SELECT LPAD(SUBSTR(cust_name, INSTR(cust_name, ' ')),LENGTH(cust_name),'*') “CUST NAME”FROM customersWHERE INSTR(cust_name, ' ',-1,2)<>0;

C : SELECT LPAD(SUBSTR(cust_name, INSTR (cust_name ' ')),LENGTH(cust_name) - INSTR(cust_name, ' '), '*') “CUST NAME”FROM customersWHERE INSTR(cust_name, ' ',1,-2)<>0;

D : SELECT LPAD(SUBSTR(cust_name, INSTR (cust_name ' ')),LENGTH(cust_name) - INSTR(cust_name, ' '), '*') “CUST NAME”FROM customersWHERE INSTR(cust_name, ' ',1,2)<>0;

Answer: A,B

EXPERTY DESIGNED 1Z0-071 PRACTICE TEST