Free Oracle 1Z0-071 Exam Questions

Become Oracle Certified with updated 1Z0-071 exam questions and correct answers

Page: 1 / 113

Total 561 Questions | Updated On: Aug 13, 2026

Add To Cart

Question 1

Examine the description of the PRODUCT_STATUS table:

The STATUS column contains the values ‘IN STOCK’ or ‘OUT OF STOCK’ for each row.

Which two queries will execute successfully?

A : SELECT prod_id "CURRENT AVAILABILITY" || q'(-s not available)' FROM product_status WHERE status = ’OUT OF STOCK';

B : SELECT prod_id || q-s not available'' FROM product_status WHERE status=’OUT OF STOCK’;

C : SELECT prod_id || q'(-s not available)’ "CURRENT AVAILABILITY" FROM product_status WHERE status = 'OUT OF STOCK';

D : SELECT prod_id || q'(-s not available)' FROM product_status WHERE status = ’OUT OF STOCK’;

E : SELECT prod_id || q’(’s not available)' 'CURRENT AVAILABILITY' FROM product_status WHERE status = 'OUT OF STOCK';

F : SELECT prod_id || q"-s not available" FROM product_status WHERE status = 'OUT OF STOCK';

Answer: C,D

Question 2

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 &col1, &col2FROM &&tableWHERE &condition;

B : SELECT &col1, &col2FROM “&table”WHERE &condition;

C : SELECT &&col1,&&col2FROM &tableWHERE &&condition= &&cond;

D : SELECT'&co11','&&co12'FROM &tableWHERE'&&condition' ='&cond';

E : SELECT&&col1, &&col2FROM &tableWHERE &&condition;

Answer: A

Question 3

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%'

Answer: B,C

Question 4

Which three statements are true about performing Data Manipulation Language (DML) operations on a view with no INSTEAD OF triggers defined? (Choose three.)

A : Insert statements can always be done on a table through a view.

B : Views cannot be used to add rows to an underlying table if the table has columns with NOT NULL constraints lacking default values which are not referenced in the defining query of the view

C : Views cannot be used to query rows from an underlying table if the table has a PRIMARY KEY and the PRIMARY KEY columns are not referenced in the defining query of the view.

D : Delete statements can always be done on a table through a view

E : The WITH CHECK clause has no effect when deleting rows from the underlying table through the view.

F : Views cannot be used to add or modify rows in an underlying table if the defining query of the view contains the DISTINCT keyword.

Answer: A,C,D

Question 5

View the exhibit and examine the descriptions of the DEPT and LOCATIONS tables.

You want to update the CITY column of the DEPT table for all the rows with the corresponding value

in the CITY column of the LOCATIONS table for each department.

Which SQL statement would you execute to accomplish the task?

A : UPDATE dept dSET city = ALL (SELECT cityFROM locations lWHERE d.location_id = l.location_id);

B : UPDATE dept dSET city = (SELECT cityFROM locations l)WHERE d.location_id = l.location_id;

C : UPDATE dept dSET city = ANY (SELECT cityFROM locations l)

D : UPDATE dept dSET city = (SELECT cityFROM locations lWHERE d.location_id = l.location_id);

Answer: D

Page: 1 / 113

Total 561 Questions | Updated On: Aug 13, 2026

Add To Cart