Oracle Oracle Certifications 1Z0-071 Questions & Answers

• Question 171:

  Which three statements are true about sequences in a single instance Oracle database? (Choose three.)

  A. A sequence's unallocated cached values are lost if the instance shuts down.

  B. A sequence number that was allocated can be rolled back if a transaction fails.

  C. A sequence can only be dropped by a DBA.

  D. A sequence can issue duplicate values.

  E. Sequences can always have gaps.

  F. Two or more tables cannot have keys generated from the same sequence.

  Correct Answer: ADE

• Question 172:

  Which two statements are true about the rules of precedence for operators? (Choose two.)

  A. The + binary operator has the highest precedence in an expression in a SQL statement.

  B. The concatenation operator || is always evaluated before addition and subtraction in an expression.

  C. Multiple parentheses can be used to override the default precedence of operators in an expression.

  D. NULLS influence the precedence of operators in an expression.

  E. Arithmetic operators with equal precedence are evaluated from left to right within an expression.

  Correct Answer: CE

• Question 173:

  Which two are true about granting privileges on objects? (Choose two.)

  A. An object privilege can be granted to other users only by the owner of that object.

  B. The owner of an object acquires all object privileges on that object by default.

  C. The WITH GRANT OPTION clause can be used only by DBA users.

  D. An object privilege can be granted to a role only by the owner of that object.

  E. A table owner must grant the REFERENCES privilege to allow other users to create FOREIGN KEY constraints using that table.

  Correct Answer: BE

• Question 174:

  Which two queries execute successfully? (Choose two.)

  A. SELECT INTERVAL '1' DAY - INTERVAL '1' MINUTE FROM DUAL

  B. SELECT SYSTIMESTAMP + INTERVAL '1' DAY FROM DUAL;

  C. SELECT INTERVAL '1' DAY - SYSDATE FROM DUAL;

  D. SELECT INTERVAL '1' DAY + INTERVAL '1' MONTH FROM DUAL;

  E. SELECT SYSDATE * INTERVAL '1' DAY FROM DUAL;

  Correct Answer: AB

• Question 175:

  Which two statements are true? (Choose two.)

  A. CASE is a function and DECODE is not.

  B. Neither CASE nor DECODE is a function.

  C. All conditions evaluated using CASE can also be evaluated using DECODE.

  D. All conditions evaluated using DECODE can also be evaluated using CASE.

  E. DECODE is a function and CASE is not.

  F. Both CASE and DECODE are functions.

  Correct Answer: DE

• Question 176:

  The PRODUCT_INFORMATION table has a UNIT_PRICE column of data type NUMBER(8,2). Evaluate this SQL statement:

  SELECT TO_CHAR(unit_price, '$9,999') FROM product_information;

  Which two statements are true about the output? (Choose two.)

  A. A row whose UNIT_PRICE column contains the value 10235.95 will be displayed as $1,0236.

  B. A row whose UNIT_PRICE column contains the value 1023.95 will be displayed as $1,024.

  C. A row whose UNIT_PRICE column contains the value 10235.95 will be displayed as $1,023.

  D. A row whose UNIT_PRICE column contains the value 10235.95 will be displayed as #######.

  E. A row whose UNIT_PRICE column contains the value 1023.99 will be displayed as $1,023.

  Correct Answer: BD

• Question 177:

  Which statement is true about TRUNCATE and DELETE?

  A. For tables with multiple indexes and triggers, DELETE is faster than TRUNCATE.

  B. You can never TRUNCATE a table if foreign key constraints would be violated.

  C. You can DELETE rows from a table with referential integrity constraints.

  D. For large tables, DELETE is faster than TRUNCATE.

  Correct Answer: B

• Question 178:

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

  A. The same table column can be part of a unique and non-unique index.

  B. A descending index is a type of function-based index.

  C. An INVINSIBLE index is not maintained when DML is performed on its underlying table.

  D. If a query filters on an indexed column then it will always be used during execution of the query.

  E. An index can be created as part of a CREATE TABLE statement.

  F. An UNUSABLE index is maintained when DML is performed on its underlying table.

  Correct Answer: ABF

• Question 179:

  Which two statements are true about the ORDER BY clause? (Choose two.)

  A. In a character sort, the values are case-sensitive.

  B. NULLS are not included in the sort operation.

  C. Numeric values are displayed in descending order if they have decimal positions.

  D. Column aliases can be used in the ORDER BY clause.

  E. Only columns that are specified in the SELECT list can be used in the ORDER BY clause.

  Correct Answer: AD

• Question 180:

  Examine the description of the EMPLOYEES table:

  

  Which query is valid?

  A. SELECT dept_id, join_date, SUM(salary) FROM employees GROUP BY dept_id;

  B. SELECT dept_id, join_date, SUM(salary) FROM employees GROUP BY dept_id, join_date;

  C. SELECT dept_id, MAX(AVG(salary)) FROM employees GROUP BY dept_id;

  D. SELECT dept_id, AVG(MAX(salary)) FROM employees GROUP BY dept_id;

  Correct Answer: B