Oracle Oracle Certifications 1Z0-071 Questions & Answers

• Question 331:

  Which two are true about virtual columns? (Choose two.)

  A. They can be referenced In the where clause of an update or debete statement.

  B. They can be referenced in the set clause of an update statement as the name of the column To be updated.

  C. They can be indexed.

  D. They cannot have a data type explicitly specified.

  E. They can be referenced in the column expression of another virtxial column.

  Correct Answer: AC

• Question 332:

  Which three statements are true regarding single row subqueries?

  A. They must be placed on the left side of the comparison operator or condition.

  B. They must be placed on the right side of the comparison operator or condition.

  C. They must return a row to prevent errors in the SQL statement.

  D. A SQL statement may have multiple single row subquery blocks.

  E. They can be used in the HAVING clause.

  F. They can be used in the WHERE clause.

  Correct Answer: DEF

• Question 333:

  Which three items does a direction of a relationship contain?

  A. an attribute

  B. a cardinality

  C. label

  D. an optionality

  E. a unique identifier

  F. an entity

  Correct Answer: ABF

• Question 334:

  Which is true about the and and andand prefixes with substitution variables? (Choose all that apply.)

  A. and can prefix a substitution variable name only in queries. DML

  B. An and prefix to an undefined substitution variable, which is referenced twice in the same query, will prompt for a value twice .

  C. The andand prefix will not prompt for a value even if the substitution variable is not previously defined in the session.

  D. An andand prefix to an undefined substitution variable, Which is referenced multiple times in multiple queries, will prompt for a value once per query.

  E. Both and and andand can prefix a substitution variable name in queries and DML statements.

  Correct Answer: BE

• Question 335:

  Which two statements are true regarding a SAVEPOINT?

  A. Rolling back to a SAVEPOINT can undo a CREATE INDEX statement.

  B. Only one SAVEPOINT may be issued in a transaction.

  C. A SAVEPOINT does not issue a COMMIT

  D. Rolling back to a SAVEPOINT can undo a TRUNCATE statement.

  E. Rolling back to a SAVEPOINT can undo a DELETE statement

  Correct Answer: CE

• Question 336:

  Examine the ORDER _ITEms table:

  

  Which two queries return rows where QUANTITY is a multiple of ten? (Choose two.)

  A. SELECT * FROM order_items WHERE quantity / 10 - TRUNC(quantity)

  B. SELECT * FROM order_items WHERE MOD(quantity, 10) - 0;

  C. SELECT * FROM order_items WHERE FLOOR(quantity / 10) = TRUNC(quantity / 10);

  D. SELECT * FROM order_items WHERE quantity = TRUNC(quantity, -1);

  E. SELECT * FROM order_items WHERE quantity = ROUND(quantity, 1);

  Correct Answer: BD

• Question 337:

  View the Exhibits and examine the structure of the COSTS and PROMOTIONS tables.

  You want to display PROD IDS whose promotion cost is less than the highest cost PROD ID in a pro motion time interval.

  Examine this SQL statement:

  

  Exhibit 1.

  

  Exhibit 2.

  

  What will be the result?

  A. It executes successfully but does not give the required result.

  B. It gives an error because the ALL keyword is not valid.

  C. It gives an error because the GROUP BY clause is not valid

  D. It executes successfully and gives the required result.

  Correct Answer: A

• Question 338:

  Examine this incomplete query:

  SELECT DATA'2019-01-01'+

  FROM DUAL;

  Which three clauses can replaceti add 22 hours to the date?

  A. INTERVAL `12:00'

  B. INTERVAL'0,5'DAY

  C. INTERVAL'12' HOUR

  D. INTERVAL'720'MINUTE

  E. INTERVAL'0 12'DAY TO HOUR

  F. INTERVAL'11:60'HOUR TO MINUTE

  Correct Answer: CDE

• Question 339:

  Examine this query Which executes successfully;

  Select job,deptno from emp

  Union all

  Select job,deptno from jobs_history;

  What will be the result?

  A. It will return rows from both select statements after eliminating duplicate rows.

  B. It will return rows common to both select statements.

  C. It will return rows both select statements including duplicate rows.

  D. It will return rows that are not common to both select statements.

  Correct Answer: C

• Question 340:

  The EMPLOYEES table contains columns EMP_ID of data type NUMBER and HIRE_DATE of data type DATE

  You want to display the date of the first Monday after the completion of six months since hiring. The NLS_TERRITORY parameter is set to AMERICA in the session and, therefore, Sunday is the first day of the week Which query can be used?

  A. SELECT emp_id, NEXT_DAY(ADD_MONTHS(hire_date, 6), 'MONDAY') FROM employees;

  B. SELECT emp_id,ADD_MONTHS(hire_date,6), NEXT_DAY('MONDAY') FROM employees;

  C. SELECT emp_id,NEXT_DAY(MONTHS_BETWEEN(hire_date,SYSDATE),6) FROM employees;

  D. SELECT emp_id,NEXT_DAY(ADD_MONTHS(hire_date,6),1) FROM employees;

  Correct Answer: A