1z0-071 Practice Test

You issue the following command to drop the PRODUCTS table: (Choose all that apply.)
SQL > DROP TABLE products;
Which three statements are true about the implication of this command?

A.

All data along with the table structure is deleted.

B.

A pending transaction in the session is committed.

C.

C. All indexes on the table remain but they are invalidated.

D.

All views and synonyms on the table remain but they are invalidated.

E.

All data in the table is deleted but the table structure remains.

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);

View the exhibit and examine the description of the DEPARTMENTS and EMPLOYEES
tables.

The retrieve data for all the employees for their EMPLOYEE_ID, FIRST_NAME, and
DEPARTMENT NAME, the following SQL statement was written:
SELECT employee_id, first_name, department_name
FROM employees
NATURAL JOIN departments;
The desired output is not obtained after executing the above SQL statement. What could
be the reason for this?

A.

The table prefix is missing for the column names in the SELECT clause.

B.

The NATURAL JOIN clause is missing the USING clause.

C.

 The DEPARTMENTS table is not used before the EMPLOYEES table in the FROM
clause.

D.

The EMPLOYEES and DEPARTMENTS tables have more than one column with the
same column name and data type.

Evaluate the following SELECT statement and view the exhibit to examine its output:
SELECT constraint_name, constraint_type, search_condition, r_constraint_name,
delete_rule, status,
FROM user_constraints
WHERE table_name = 'ORDERS';
CONSTRAINT_NAME
CON
SEARCH_CONDITION
R_CONSTRAINT_NAME
DELETE_RULE
STATUS
ORDER_DATE_NN
C
"ORDER_DATE" IS NOT NULL
ENABLED
ORDER_CUSTOMER_ID_NN
"CUSTOMER_ID" IS NOT NULL
ENABLED
ORDER_MODE_LOV
C
order _mode in ('direct', 'online')
ENABLED
ORDER TOTAL MIN
C
order total >= 0
ENABLED
ORDER PK
P
ENABLED
ORDERS CUSTOMER ID
R
CUSTOMERS ID
SET NULL
ENABLED
ORDERS SALES REP
R
EMP EMP ID
SET NULL
ENABLED
Which two statements are true about the output? (Choose two.)

A.

The R_CONSTRAINT_NAME column gives the alternative name for the constraint.

B.

In the second column, 'c' indicates a check constraint.

C.

The STATUS column indicates whether the table is currently in use.

D.

The column DELETE_RULE decides the state of the related rows in the child table when
the corresponding row is deleted from the parent table.

View the exhibits and examine the structures of the COSTS and PROMOTIONS tables.

Evaluate the following SQL statement:
SQL> SELECT prod_id FROM costs
WHERE promo_id IN (SELECT promo_id FROM promotions
WHERE promo_cost < ALL
(SELECT MAX(promo_cost) FROM promotions
GROUP BY (promo_end_datepromo_
begin_date)));
What would be the outcome of the above SQL statement?

A.

It displays prod IDs in the promo with the lowest cost.

B.

It displays prod IDs in the promos with the lowest cost in the same time interval.

C.

It displays prod IDs in the promos with the highest cost in the same time interval.

D.

It displays prod IDs in the promos which cost less than the highest cost in the same time
interval.