Free Practice Oracle 1Z0-071 Exam Questions 2025

Stay ahead with 100% Free Oracle Database 12c SQL 1Z0-071 Dumps Practice Questions

Page: 1 / 113

Total 561 Questions | Updated On: Feb 19, 2025

Add To Cart

Question 1

Which two statements are true regarding subqueries? (Choose two.)

A : Only two subqueries can be placed at one level.

B : A subquery in the WHERE clause of a SELECT statement can be nested up to three levels only.

C : A subquery can be used to access data from one or more tables or views.

D : The columns in a subquery must always be qualified with the name or alias of the table used.

E : If the subquery returns 0 rows, then the value returned by the subquery expression is NULL

Answer: A,C

Question 2

View the Exhibit and examine PRODUCTS and ORDER_ITEMS tables.

You executed the following query to display PRODUCT_NAME and the number of times the product

has been ordered:

SELECT p.product_name, i.item_cnt

FROM (SELECT product_id, COUNT (*) item_cnt

FROM order_items

GROUP BY product_id) i RIGHT OUTER JOIN products p

ON i.product_id = p.product_id;

What would happen when the above statement is executed?

A : The statement would execute successfully to produce the required output

B : The statement would not execute because inline views and outer joins cannot be used together.

C : The statement would not execute because the ITEM_CNT alias cannot be displayed in the outer query.

D : The statement would not execute because the GROUP BY clause cannot be used in the inline.

Answer: A

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 EMPLOYEES table:

Which query requires explicit data type conversion?

A : SELECT join_date FROM employees WHERE join_date > ’10-02-2018’;

B : SELECT salary + ‘120.50’ FROM employees;

C : SELECT SUBSTR(join_date, 1, 2) – 10 FROM employees;

D : SELECT join_date + ‘20’ FROM employees;

E : SELECT join_date || ‘ ‘ || salary FROM employees;

Answer: D

Question 5

View the Exhibit and examine the data in EMP and DEPT tables.

In the DEPT table, DEPTNO is the PRIMARY KEY.

In the EMP table, EMPNO is the PRIMARY KEY and DEPTNO is the FOREIGN KEY referencing the

DEPTNO column in the DEPT table.

What would be the outcome of the following statements executed in the given sequence?

DROP TABLE emp;

FLASHBACK TABLE emp TO BEFORE DROP;

INSERT INTO emp VALUES (2, ‘SCOTT’, 10);

INSERT INTO emp VALUES (3, ‘KING’, 55);

A : Both the INSERT statements would fail because the constraints are automatically retrieved when the table is flashed back.

B : Both the INSERT statements would succeed because none of the constraints on the table are automatically retrieved when the table is flashed back.

C : Only the first INSERT statement would succeed because all constraints except the primary key constraint are automatically retrieved after a table is flashed back.

D : Only the SECOND INSERT statement would succeed because all the constraints except referential integrity constraints that reference other tables are retrieved automatically after the table is flashed back.

Answer: D

Page: 1 / 113

Total 561 Questions | Updated On: Feb 19, 2025

Add To Cart