1z0-071 Practice Test

The following are the steps for a correlated subquery, listed in random order:
The WHERE clause of the outer query is evaluated.
The candidate row is fetched from the table specified in the outer query.
This is repeated for the subsequent rows of the table, till all the rows are
processed.
Rows are returned by the inner query, after being evaluated with the value from
the candidate row in the outer query.
Which is the correct sequence in which the Oracle server evaluates a correlated subquery?

A.

2, 1, 4, 3

B.

4, 1, 2, 3

C.

4, 2, 1, 3

D.

2, 4, 1, 3

Examine the structure of the MEMBERS table:
NameNull?Type
------------------------------
MEMBER_IDNOT NULLVARCHAR2 (6)
FIRST_NAMEVARCHAR2 (50)
LAST_NAMENOT NULLVARCHAR2 (50)
ADDRESSVARCHAR2 (50)
CITYVARCHAR2 (25)
STATEVARCHAR2 (3)
You want to display details of all members who reside in states starting with the letter A
followed by exactly one character.
Which SQL statement must you execute?

A.

SELECT * FROM MEMBERS WHERE state LIKE '%A_*;

B.

SELECT * FROM MEMBERS WHERE state LIKE 'A_*;

C.

SELECT * FROM MEMBERS WHERE state LIKE 'A_%';

D.

SELECT * FROM MEMBERS WHERE state LIKE 'A%';

View the Exhibit and examine the structure of the EMP table which is not partitioned and
not an index-organized table. (Choose two.)

Evaluate this SQL statement:
ALTER TABLE emp
DROP COLUMN first_name;
Which two statements are true?

A.

The FIRST_NAME column can be dropped even if it is part of a composite PRIMARY
KEY provided the CASCADE option is added to the SQL statement.

B.

The FIRST_NAME column would be dropped provided at least one column remains in
the table.

C.

The FIRST_NAME column would be dropped provided it does not contain any data.

D.

The drop of the FIRST_NAME column can be rolled back provided the SET UNUSED
option is added to the SQL statement.

View the exhibit and examine the structure of the EMPLOYEES table

You want to display all employees and their managers having 100 as the MANAGER_ID.
You want the output in two columns: the first column would have the LAST_NAME of the
managers and the second column would have LAST_NAME of the employees.
Which SQL statement would you execute?

A.

SELECT m.last_name "Manager", e.last_name "Employee"FROM employees m JOIN
employees eON m.employee_id = e.manager_idWHERE m.manager_id = 100;

B.

SELECT m.last_name "Manager", e.last_name "Employee"FROM employees m JOIN
employees eON m.employee_id = e.manager_idWHERE e.manager_id = 100;

C.

SELECT m.last_name "Manager", e.last_name "Employee"FROM employees m JOIN
employees eON e.employee_id = m.manager_idWHERE m.manager_id = 100;

D.

SELECT m.last_name "Manager", e.last_name "Employee"FROM employees m JOIN
employees eWHERE m.employee_id = e.manager_id and AND e.manager_id = 100

View the Exhibit and examine the structure of ORDERS and ORDER_ITEMS tables.
ORDER_ID is the primary key in the ORDERS table. It is also the foreign key in the
ORDER_ITEMS table wherein it is created with the ON DELETE CASCADE option.

A.

DELETE orders o, order_items IWHERE o.order_id = i.order_id;

B.

DELETEFROM ordersWHERE (SELECT order_idFROM order_items);

C.

DELETE ordersWHERE order_total < 1000;

D.

DELETE order_idFROM ordersWHERE order_total < 1000;