Question 1

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

Options :

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 2

Which two are true about the NVL, NVL2, and COALESCE functions?

Options :

A : The first expression in NVL2 is never returned.

B : NVL2 can have any number of expressions in the list.

C : COALESCE stops evaluating the list of expressions when it finds the first null value.

D : COALESCE stops evaluating the list of expressions when it finds the first non-null value.

E : NVL must have expressions of the same data type.

F : NVL can have any number of expressions in the list.

Answer: D,E

Question 3

Examine the data in the INVOICES table:

Examine the data in the CURRENCIES table:

CURRENCY_CODE

-------------

JPY

GPB

CAD

EUR

USD

Which query returns the currencies in CURRENCIES that are not present in INVOICES?

Options :

A :

SELECT currency_ code FROM currencies

MINUS

SELECT currency_ code FROM invoices;

B :

SELECT * FROM currencies

WHERE NOT EXISTS (

SELECT NULL FROM invoices WHERE currency_ code = currency_ code);

C :

SELECT currency_ code FROM currencies

INTERSECT

SELECT currency_ code FROM invoices;

D :

SELECT * FROM currencies

MINUS

SELECT * FROM invoices;

Answer: A

Question 4

Which statement will return the last sequence number generated by the EMP_ SEQ sequence?

Options :

A : SELECT NEXTVAL FROM emp_ seq;

B : SELECT CURRVAL FROM emp_ seq;

C : SELECT emp_ seq. CURRVAL FROM DUAL;

D : SELECT emp_ seq . NEXTVAL FROM DUAL;

Answer: D

Question 5

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

Free 1Z0-071 Mock Exam – Practice Online Confidently

Buying Options: