Oracle Oracle Certifications 1Z0-071 Questions & Answers

• Question 111:

  In the PROMOTIONS table, the PROMO_BEGIN_DATE column is of data type DATE and the default date format is DD-MON-RR.

  Which two statements are true about expressions using PROMO_BEGIN_DATE contained in a query? (Choose two.)

  A. TO_NUMBER(PROMO_BEGIN_DATE) ?5 will return a number.

  B. PROMO_BEGIN_DATE ?SYSDATE will return a number.

  C. PROMO_BEGIN_DATE ?SYSDATE will return an error.

  D. PROMO_BEGIN_DATE ?5 will return a date.

  E. TO_DATE(PROMO_BEGIN_DATE * 5) will return a date.

  Correct Answer: BD

• Question 112:

  You have the privileges to create any type of synonym.

  Which statement will create a synonym called EMP for the HCM.EMPLOYEE_RECORDS table that is accessible to all users?

  A. CREATE PUBLIC SYNONYM emp FOR hcm.employee_records;

  B. CREATE GLOBAL SYNONYM emp FOR hcm.employee_records;

  C. CREATE SYNONYM emp FOR hcm.employee_records:

  D. CREATE SYNONYM SYS.emp FOR hcm.employee_records;

  E. CREATE SYNONYM PUBLIC.emp FOR hcm.employee_records;

  Correct Answer: A

• Question 113:

  You and your colleague Andrew have these privileges on the EMPLOYEE_RECORDS table:

  1.

  SELECT

  2.

  INSERT

  3.

  UPDATE

  4.

  DELETE

  You connect to the database instance and perform an update to some of the rows in EMPLOYEE_RECORDS, but do not commit yet.

  Andrew connects to the database instance and queries the table.

  No other users are accessing the table.

  Which two statements are true at this point? (Choose two.)

  A. Andrew will be unable to see the changes you have made.

  B. Andrew will be unable to perform any INSERTs, UPDATEs, or DELETEs on the table.

  C. Andrew will be able to SELECT from the table, but be unable to modify any existing rows.

  D. Andrew will be able to see the changes you have made.

  E. Andrew will be able to modify any rows in the table that have not been modified by your transaction.

  Correct Answer: AE

• Question 114:

  Which two queries execute successfully? (Choose two.)

  A. SELECT NULLIF (NULL, 100) FROM DUAL;

  B. SELECT NULLIF (100, `A') FROM DUAL;

  C. SELECT NULLIF (100, 100) FROM DUAL;

  D. SELECT COALESCE (100, NULL, 200) FROM DUAL;

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

  Correct Answer: CD

• Question 115:

  Which statement executes successfully?

  A. SELECT TO_NUMBER(INTERVAL `800' SECOND, `HH24:MM') FROM DUAL;

  B. SELECT TO_CHAR(INTERVAL `800' SECOND, `HH24:MM') FROM DUAL;

  C. SELECT TO_NUMBER(TO_DATE(INTERVAL `800' SECOND)) FROM DUAL;

  D. SELECT TO_DATE(TO_NUMBER(INTERVAL `800' SECOND)) FROM DUAL;

  E. SELECT TO_DATE(INTERVAL `800' SECOND, `HH24:MM') FROM DUAL;

  Correct Answer: B

• Question 116:

  Which statement executes successfully?

  A. SELECT TO_NUMBER(INTERVAL `800' SECOND, `HH24:MM') FROM DUAL;

  B. SELECT TO_CHAR(INTERVAL `800' SECOND, `HH24:MM') FROM DUAL;

  C. SELECT TO_NUMBER(TO_DATE(INTERVAL `800' SECOND)) FROM DUAL;

  D. SELECT TO_DATE(TO_NUMBER(INTERVAL `800' SECOND)) FROM DUAL;

  E. SELECT TO_DATE(INTERVAL `800' SECOND, `HH24:MM') FROM DUAL;

  Correct Answer: B

• Question 117:

  Which three statements are true about GLOBAL TEMPORARY TABLES? (Choose three.)

  A. A GLOBAL TEMPORARY TABLE can have only one index.

  B. A GLOBAL TEMPORARY TABLE can be referenced in the defining query of a view

  C. DML on GLOBAL TEMPORARY TABLES generates no REDO.

  D. A GLOBAL TEMPORARY TABLE cannot have a PUBLIC SYNONYM.

  E. A GLOBAL TEMPORARY TABLE can have multiple indexes.

  F. A trigger can be created on a GLOBAL TEMPORARY TABLE.

  Correct Answer: BEF

• Question 118:

  Which two statements are true about Oracle synonyms? (Choose two.)

  A. Users must have the required privileges on the underlying objects to use public synonyms.

  B. Synonyms can be created for roles.

  C. Synonyms cannot be created for sequences.

  D. Synonyms cannot be created for synonyms.

  E. Synonyms can be created for packages.

  F. Users must have the DBA role to create public synonyms.

  Correct Answer: AE

• Question 119:

  Which three statements are true about time zones, date data types, and timestamp data types in an Oracle database? (Choose three.)

  A. A TIMESTAMP WITH LOCAL TIMEZONE data type column is stored in the database using the time zone of the session that inserted the row.

  B. The SESSIONTIMEZONE function can return an offset from Universal Coordinated Time (UTC).

  C. The DBTIMEZONE function can return an offset from Universal Coordinated Time (UTC).

  D. The CURRENT_TIMESTAMP function returns data without time zone information.

  E. A TIMESTAMP data type column contains information about year, month, and day.

  Correct Answer: BCE

• Question 120:

  Which two statements about INVISIBLE indexes are true? (Choose two.)

  A. You use ALTER INDEX to make an INVISIBLE index VISIBLE.

  B. An INVISIBLE index consumes no storage.

  C. The query optimizer never considers INVISIBLE indexes when determining execution plans.

  D. You can only create one INVISIBLE index on the same column list.

  E. All INSERT, UPDATE, and DELETE statements maintain entries in the index.

  Correct Answer: AE