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 27, 2026

Add To Cart

Question 1

Which statement will return a comma-separated list of employee names in alphabetical order for each department in the EMP table?

A : SELECT deptno,LISTAGG(ename, ' , ') WITHIN GROUP AS employee_list FROM emp GROUP BY deptno;

B : SELECT deptno,LISTAGG(ename, ', ') WITHIN GROUP AS employee_list FROM emp GROUP BY deptno ORDER BY ename;

C : SELECT deptno,LISTAGG(ename, ', ') WITHIN GROUP (GROUP BY deptno) AS employee_list FROM emp ORDER BY ename;

D : SELECT deptno,LISTAGG(ename, ', ') WITHIN GROUP (ORDER BY ename) AS employee_list FROM emp GROUP BY deptno;

Answer: D

Question 2

View the Exhibit and examine the details of the ORDER_ITEMS table.

Evaluate the following SQL statements:

Statement 1:

SELECT MAX(unit_price*quantity) “Maximum Order”

FROM order_items;

Statement 2:

SELECT MAX(unit_price*quantity) “Maximum Order”

FROM order_items

GROUP BY order_id;

Which statements are true regarding the output of these SQL statements? (Choose all that apply.)

A : Statement 2 would return multiple rows of output.

B : Both statements would ignore NULL values for the UNIT_PRICE and QUANTITY columns.

C : Statement 1 would not return give the same output.

D : Both the statements would give the same output.

E : Statement 1 would return only one row of output.

Answer: A,B

Question 3

View the exhibit for the structure of the STUDENT and FACULTY tables.

You need to display the faculty name followed by the number of students handled by the faculty at

the base location.

Examine the following two SQL statements:

Statement 1

SQL>SELECT faculty_name, COUNT(student_id)

FROM student JOIN faculty

USING (faculty_id, location_id)

GROUP BY faculty_name;

Statement 2

SQL>SELECT faculty_name, COUNT(student_id)

FROM student NATURAL JOIN faculty

GROUP BY faculty_name;

Which statement is true regarding the outcome?

A : Only statement 2 executes successfully and gives the required result.

B : Only statement 1 executes successfully and gives the required result.

C : Both statements 1 and 2 execute successfully and give different results.

D : Both statements 1 and 2 execute successfully and give the same required result.

Answer: B

Question 4

You issued this command: SQL > DROP TABLE employees; Which three statements are true? (Choose three.)

A : Sequences used in the EMPLOYEES table become invalid.

B : If there is an uncommitted transaction in the session, it is committed.

C : All indexes and constraints defined on the table being dropped are also dropped.

D : The space used by the EMPLOYEES table is always reclaimed immediately.

E : The EMPLOYEES table can be recovered using the ROLLBACK command.

F : The EMPLOYEES table may be moved to the recycle bin.

Answer: A,B,C

Question 5

Examine the description of the CUSTOMERS table:

Which three statements will do an implicit conversion?

A : SELECT * FROM customers WHERE TO_ CHAR (customer_ id) = '0001';

B : SELECT * FROM customers WHERE customer id = '0001';

C : SELECT * FROM customers WHERE customer_ id = 0001;

D : SELECT FROM customers WHERE insert date = '01-JAN-19';

E : SELECT. FROM customers WHERE insert_ date = DATE *2019-01-01';

F : SELECT. FRON customers WE TO DATE (Insert _ date) = DATE ‘2019-01-01’;

Answer: B,D,E

Page: 1 / 113

Total 561 Questions | Updated On: Oct 27, 2026

Add To Cart