Oracle Oracle Database 1Z0-071 Questions & Answers

• Question 381:

  Examine the description of the BOOKS_TRANSACTIONS table:

  

  Examine this partial SQL statement:

  SELECT * FROM books_transactions

  Which two WHERE conditions give the same result?

  A. WHERE (borrowed_date = SYSDATE AND transaction_type = 'RM') OR member_id IN ('A101','A102');

  B. WHERE borrowed_date = SYSDATE AND transaction_type = 'RM' OR member_id IN('A101','A102');

  C. WHERE borrowed_date = SYSDATE AND transaction_type = 'RM' OR member_id IN('A101','A102');

  D. WHERE borrowed_date = SYSDATE AND transaction_type = 'RM' AND (member_id = 'A101' OR member_id = 'A102'));

  E. WHERE borrowed_date = SYSDATE AND transaction_type = 'RM' AND member_id = 'A101' OR member_id = 'A102');

  Correct Answer: AB

• Question 382:

  Which two statements are true about CURRENT_TIMEITAMP?

  A. The date is in the time zone of DBTIMEZONE.

  B. The value varies depending on the setting of SESSIONTIMEZONE.

  C. It returns the same date as CURRENT_DATE.

  D. The time is in the time zone of DBTIMEZONE.

  E. It returns a value of data type TIMESTAMP

  F. It always returns the same value as SYSTIMESTAMP

  Correct Answer: DF

• Question 383:

  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 384:

  Which two queries execute successfully?

  A. SELECT NULLIF(100, 100) FROM DUAL

  B. SELECT COALESCE(100, NULL, 200) FROM DUAL

  C. SELECT NULLIF(100, 'A') FROM DUAL

  D. SELECT NULLIF(NULL, 100) FROM DUAL

  E. SELECT COALESCE(100, 'A' ) FROM DUAL

  Correct Answer: AB

• Question 385:

  Which three are true about multiple INSERT statements?

  A. They can be performed only by using a subquery.

  B. They can be performed on relational tables.

  C. They can be performed on views.

  D. They can be performed on remote tables.

  E. They can be performed on external tables using SQL*Loader.

  F. They can insert each computed row into more than one table.

  Correct Answer: ABF

• Question 386:

  Which four statements are true about constraints on Oracle tables?

  A. A Column can have only one CHECK Constraint.

  B. A NOT NULL Constraint can be defined at the table level.

  C. A UNIQUE constraint permits NULLS.

  D. A PRIMARY KEY Constraint can be added after a table has been created and populated.

  E. A CHECK Constraint can refer to values in other rows.

  F. A UNIQUE Constraint can use a pre-existing index on the constrained column or columns.

  G. A FOREIGN KEY Column can contain NULLS.

  Correct Answer: CDFG

• Question 387:

  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 388:

  Which three statements are true about performing Data Manipulation Language (DML) operations on a view In an Oracle Database?

  A. Insert statements can always be done on a table through a view.

  B. The WITH CHECK clause has no effect when deleting rows from the underlying table through the view.

  C. Views cannot be used to query rows from an underlying table if the table has a PRIPOARY KEY and the PRIMARY KEY columns are not referenced in the defining query of the view.

  D. Views cannot be used to add or modify rows in an underlying table if the defining query of the view contains the DISTINCT keyword.

  E. Views cannot be used to add on modify rows in an underlying table if the defining query of the view contains aggregating functions.

  F. Views cannot be used to add rows to an underlying table if the table has columns with NOT NULL constraints lacking default values Which are not referenced in the defining query of the view.

  Correct Answer: DEF