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: Oct 28, 2025

Add To Cart

Question 1

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 2

Examine the description of the ENPLYEES table:

Which two queries return all rows for employees whose salary is greater than the average salary in their department?

A : SELECT ”FROM employeesWHERE salary > ANYSELECT AVG (salary)EROM employeesGROUP BY department_ id);

B : SELECTFROM employeesWHERE salary > AVG (salary) OVER (PARTITION BY department _ id);

C : SELECT”FROM employees e1WHERE salary >!SELECT AVG (salary)FROM employees e2WHERE e1. Department _id = e2, department_ id

D : SELECT.FROMSELECT e.", AVG (salary) OVER (PARTITION BY department id) avg_ salFROM employees eWHERE salary > avg_ sal;

E : SELECT”FROM employeesWHERE salary >( SELECT AVG(salary) FROMemployeesGROUP BY department _ id

Answer: B,C

Question 3

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

Question 4

Examine the description of the PRODUCT_DETAILS table:

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 default 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 duplicate values.

Answer: A,D

Question 5

Examine the description of EMPLOYEES table:

Which three queries return all rows for which SALARY+COMMISSION is greate than 20000?

A : SELECT * FROM employees WHERE salary+NULLF(commission,0)>=20000;

B : SELECT * FROM employees WHERE salary+NVL2(commission,commission,0)>=20000;

C : SELECT * FROM employees WHERE NVL2(salary)+commission,salary+commission,

D : SELECT * FROM employees WHERE salary+NVL(commission,0)>=20000;

E : SELECT * FROM employees WHERE NVL(salary+commission,0)>=20000;

F : SELECT * FROM employees WHERE NVL(salary+commission,0)>==20000;

Answer: A,B,D

Page: 1 / 113

Total 561 Questions | Updated On: Oct 28, 2025

Add To Cart