Oracle Oracle Certifications 1Z0-071 Questions & Answers

• Question 391:

  Which four statements are true regarding primary and foreign key constraints and the effect they can have on table data?

  A. Only the primary key can be defined at the column and table level.

  B. The foreign key columns and parent table primary key columns must have the same names.

  C. It is possible for child rows that have a foreign key to remain in the child table at the time the parent row is deleted.

  D. A table can have only one primary key but multiple foreign keys.

  E. Primary key and foreign key constraints can be defined at both the column and table level.

  F. A table can have only one primary key and one foreign key.

  G. It is possible for child rows that have a foreign key to be deleted automatically from the child table at the time the parent row is deleted

  Correct Answer: CDEG

• Question 392:

  Examine the description of the PRODUCTS table Which contains data:

  

  Which two are true?

  A. The PROD_NAME column cannot have a DEFAULT clause added to it.

  B. The EXPIRY_DATE column cannot be dropped.

  C. The EXPIRY_DATE column data type can be changed to TIMESTAMP.

  D. The PROD_ID column can be renamed.

  E. The PROD_ID column data type can be changed to VARCHAR2(2).

  Correct Answer: CD

• Question 393:

  Which three statements are true about GLOBAL TEMPORARY TABLES?

  A. GLOBAL TEMPORARY TABLE rows inserted by a session are available to any other session whose user has been granted select on the table.

  B. A TRUNCATE command issued in a session causes all rows In a GLOBAL TEMPORARY TABLE for the issuing session to be deleted.

  C. A DELETE command on a GLOBAL TEMPORARY TABLE cannot be rolled back.

  D. A GLOBAL TEMPORARY TABLE's definition is available to multiple sessions.

  E. Any GLOBAL TEMPORARY TABLE rows existing at session termination will be deleted.

  F. GLOBAL TEMPORARY TABLE space allocation occurs at session start.

  Correct Answer: BDE

• Question 394:

  Which two statements are true about dropping views? (Choose two.)

  A. The creator of a view to be dropped must have the DROP ANY VIEW privilege.

  B. Data selected by a view's defining query is deleted from its underlying tables when the view is dropped.

  C. Views referencing a dropped view become invalid.

  D. Read only views cannot be dropped.

  E. CASCADE CONSTRAINTS must be specified when referential integrity constraints on other objects refer to primary or unique keys in the view to be dropped.

  Correct Answer: CE

• Question 395:

  You currently have an active transaction in your session and have been granted SELECT access to

  V$TRANSACTION.

  Executing:

  SELECT xid, status FROM v$transaction;

  in your session returns:

  

  In which three situations will re-executing this query still return a row but with a different XID, indicating a new transaction has started? (Choose three.)

  A. after successfully executing a CREATE TABLE statement followed by a CREATE INDEX statement

  B. after successfully executing a TRUNCATE statement followed by a DML statement

  C. after successfully executing a DML statement following a failed DML statement

  D. after successfully executing a CREATE TABLE AS SELECT statement followed by a SELECT FOR UPDATE statement

  E. after successfully executing a COMMIT or ROLLBACK followed by a DML statement

  F. after successfully executing a COMMIT or ROLLBACK followed by a SELECT statement

  Correct Answer: BDE

• Question 396:

  Which two are true about using the FOR UPDATE clause in a SELECT statement? (Choose two.)

  A. It can be used with SET operators (UNION, INTERSECT etc.).

  B. It cannot be used with the DISTINCT keyword.

  C. If the NOWAIT clause is added, the statement will automatically acquire locks from their owning transactions and not wait.

  D. The statement skips rows locked by other transactions.

  E. It can be used with joins.

  Correct Answer: BE

• Question 397:

  Which two are true about constraints? (Choose two.)

  A. A column with a FOREIGN KEY constraint can never contain a NULL value.

  B. A constraint can be disabled even if the constrained column contains data.

  C. Constraints are enforced only during INSERT operations.

  D. All constraints can be defined at the table or column level.

  E. A column with a UNIQUE constraint can contain a NULL value.

  Correct Answer: BE

• Question 398:

  Which two statements are true about indexes and their administration in an Oracle database? (Choose two.)

  A. A new index can be created or an existing one reused when a primary key constraint is created.

  B. An INVINSIBLE index is maintained by DML operations on the underlying table.

  C. If a query filters on an indexed column, the index will always be accessed during execution of the query.

  D. A DROP INDEX statement always prevents updates to the table during the drop operation.

  E. The same table column cannot be part of a unique and non-unique index.

  Correct Answer: AB

• Question 399:

  Examine this incomplete query:

  SELECT DATE '2019-01-01' + FROM DUAL;

  Which three clauses can replace to add 12 hours to the date? (Choose three.)

  A. INTERVAL '0.5' DAY

  B. INTERVAL '720' MINUTE

  C. INTERVAL '11:60' HOUR TO MINUTE

  D. INTERVAL '12:00' HOUR TO SECOND

  E. INTERVAL '0 12' DAY TO HOUR

  F. INTERVAL '12' HOUR

  Correct Answer: BEF

• Question 400:

  Examine these statements and the result:

  

  Now examine this command:

  

  What must replace MISSING CLAUSE for CUSTOMER_SEQ.NEXTVAL to return 11?

  A. NOCACHE

  B. INCREMENT BY 10

  C. START WITH 11

  D. MINVALUE 11

  E. CYCLE 11

  Correct Answer: B