1z0-071 Practice Test

You notice a performance change in your production Oracle 12c database. You want to
know which change caused this performance difference.
Which method or feature should you use?

A.

Compare Period ADDM report.

B.

AWR Compare Period report.

C.

Active Session History (ASH) report.

D.

Taking a new snapshot and comparing it with a preserved snapshot.

Examine the create table statements for the stores and sales tables.
SQL> CREATE TABLE stores(store_id NUMBER(4) CONSTRAINT store_id_pk PRIMARY
KEY, store_name VARCHAR2(12), store_address VARCHAR2(20), start_date DATE);
SQL> CREATE TABLE sales(sales_id NUMBER(4) CONSTRAINT sales_id_pk PRIMARY
KEY, item_id NUMBER(4), quantity NUMBER(10), sales_date DATE, store_id
NUMBER(4), CONSTRAINT store_id_fk FOREIGN KEY(store_id) REFERENCES
stores(store_id));
You executed the following statement:
SQL> DELETE from stores
WHERE store_id=900;
The statement fails due to the integrity constraint error:
ORA-02292: integrity constraint (HR.STORE_ID_FK) violated
Which three options ensure that the statement will execute successfully?

A.

Disable the primary key in the STORES table.

B.

Use CASCADE keyword with DELETE statement.

C.

DELETE the rows with STORE_ID = 900 from the SALES table and then delete rows
from STORES table.

D.

Disable the FOREIGN KEY in SALES table and then delete the rows.

E.

Create the foreign key in the SALES table on SALES_ID column with on DELETE
CASCADE option.

You want to display 5 percent of the rows from the SALES table for products with the
lowest AMOUNT_SOLD and also want to include the rows that have the same
AMOUNT_SOLD even if this causes the output to exceed 5 percent of the rows.
Which query will provide the required result?

A.

SELECT prod_id, cust_id, amount_soldFROM salesORDER BY amount_soldFETCH
FIRST 5 PERCENT ROWS WITH TIES;

B.

SELECT prod_id, cust_id, amount_soldFROM salesORDER BY amount_soldFETCH
FIRST 5 PERCENT ROWS ONLY WITH TIES;

C.

SELECT prod_id, cust_id, amount_soldFROM salesORDER BY amount_soldFETCH
FIRST 5 PERCENT ROWS WITH TIES ONLY;

D.

SELECT prod_id, cust_id, amount_soldFROM salesORDER BY amount_soldFETCH
FIRST 5 PERCENT ROWS ONLY;

Which task can be performed by using a single Data Manipulation Language (DML)
statement?

A.

adding a column constraint when inserting a row into a table

B.

adding a column with a default value when inserting a row into a table

C.

removing all data only from one single column on which a unique constraint is defined

D.

removing all data only from one single column on which a primary key constraint is
defined

Examine the structure of the CUSTOMERS table: (Choose two.)

CUSTNO is the PRIMARY KEY.
You must determine if any customers' details have been entered more than once using a
different CUSTNO, by listing all duplicate names.
Which two methods can you use to get the required result?

A.

Subquery

B.

Self-join

C.

Full outer-join with self-join

D.

Left outer-join with self-join

E.

Right outer-join with self-join