Question 1

Which two statements are true about transactions in the Oracle Database server?

Options :

A : An uncommitted transaction commits automatically if the user exits SQL*Plus

B : Data Manipulation Language (DML) statements always start a new transaction.

C : A user can always see uncommitted updates made by the same user in a different session.

D :

A Data Definition Language (DDL) statement does a commit automatically only for the data dictionary updates caused by the DDL

E : A session can always see uncommitted updates made by itself.

F :

If a session has an uncommitted transaction, then a DDL statement issue a COMMIT before starting a new transaction.

Answer: A,B,E

Question 2

Evaluate the following SQL statements that are issued in the given order:

CREATE TABLE emp

(emp_no NUMBER(2) CONSTRAINT emp_emp_no_pk PRIMARY KEY,

ename VARCHAR2(15),

salary NUMBER (8,2),

mgr_no NUMBER(2) CONSTRAINT emp_mgr_fk REFERENCES emp(emp_no));

ALTER TABLE emp

DISABLE CONSTRAINT emp_emp_no_pk CASCADE;

ALTER TABLE emp

ENABLE CONSTRAINT emp_emp_no_pk;

What would be the status of the foreign key EMP_MGR_PK?

Options :

A : It would remain disabled and can be enabled only by dropping the foreign key constraint and recreating it.

B : It would remain disabled and has to be enabled manually using the ALTER TABLE command.

C : It would be automatically enabled and immediate.

D : It would be automatically enabled and deferred.

Answer: B

Question 3

Examine the description of the ORDER_ITEMS table:

Examine this incomplete query:

SELECT DISTINCT quantity * unit_price total_paid FROM order_items ORDER BY ; Which two can replace so the query completes successfully?

Options :

A : quantity

B : quantity, unit_price

C : total_paid

D : product_id

E : quantity * unit_price

Answer: B,E

Question 4

View the Exhibit and examine the description for the PRODUCTS and SALES table.

PROD_ID is a primary key in the PRODUCTS table and foreign key in the SALES table with ON DELETE

CASCADE option. The SALES table contains data for the last three years. You want to remove all the

rows from the PRODUCTS table for which no sale was done for the last three years.

Which is the valid DELETE statement?

Options :

A : DELETEFROM productsWHERE prod_id = (SELECT prod_idFROM salesWHERE time_id - 3*365 = SYSDATE );

B : DELETEFROM productsWHERE prod_id = (SELECT prod_idFROM salesWHERE SYSDATE >= time_id - 3*365 );

C : DELETEFROM productsWHERE prod_id IN (SELECT prod_idFROM salesWHERE SYSDATE - 3*365 >= time_id);

D : DELETEFROM productsWHERE prod_id IN (SELECT prod_idFROM salesWHERE time_id >= SYSDATE - 3*365 );

Answer: C

Question 5

.No user-defined locks are used in your database.

Which three are true about Transaction Control Language (TCL)?

Options :

A : COMMIT erases all the transaction-s savepoints and releases its locks.

B : COMMIT ends the transaction and makes all its changes permanent.

C : ROLLBACK without the TO SAVEPOINT clause undoes all the transaction-s changes but does not release its locks.

D :

ROLLBACK to SAVEPOTNT undoes the transaction-s changes made since the named savepoint and then ends the transaction.

E : ROLLBACK without the TO SAVEPOINT clause undoes alt the transaction-s changes, releases its locks, and erases all its savepoints.

F : ROLLBACK without the TO SAVEPOINT clause undoes all the transaction-s changes but does not erase its savepoints.

Answer: A,B,E

Free 1Z0-071 Mock Exam – Practice Online Confidently

Buying Options: