Oracle Oracle Certifications 1Z0-071 Questions & Answers

• Question 251:

  You execute these commands:

  SQL> DEFINE hiredate = '01-APR -2011';

  SQL> SELECT employee_id, first_name, salary FROM employees WHERE hire date > andhiredate AND manager_id >andmgr_id;

  For Which substitution variables will you be prompted?

  A. none

  B. andhiredate and andmgr_id

  C. only andhiredate

  D. only andmgr_id

  Correct Answer: D

• Question 252:

  Which two statements execute successfully?

  

  A. Option A

  B. Option B

  C. Option C

  D. Option D

  E. Option E

  Correct Answer: AB

• Question 253:

  Which two statements will return the names of the three employees with the lowest salaries? (Choose two.)

  

  A. Option A

  B. Option B

  C. Option C

  D. Option D

  E. Option E

  Correct Answer: BC

• Question 254:

  Examine the description of the EMPLOYEES table:

  

  Which statement will fail?

  

  A. Option A

  B. Option B

  C. Option C

  D. Option D

  Correct Answer: A

• Question 255:

  Which three statements are true about views in an Oracle Database? (Choose three.)

  A. A SELECT statement cannot contain a WHERE clause when querying a view containing a WHERE clause in its defining query.

  B. Views have no segment.

  C. Views have no object number.

  D. Views can join tables only if they belong to the same schema.

  E. A view can be created that refers to a non-existent table in its defining query.

  F. Rows inserted into a table using a view are retained in the table if the view is dropped.

  Correct Answer: BEF

• Question 256:

  Which three statements are true about views in an Oracle database?

  A. The WITH CHECK clause prevents certain rows from being displayed when querying the view.

  B. The WITH CHECK clause prevents certain rows from being updated or inserted.

  C. Tables in the defining query of a view must always exist in order to create the view.

  D. Date Manipulation Language (DML) can always be used on views.

  E. Deleting one or more rows using a view whose defining query contains a GROUP BY clause will cause an error.

  F. Views can be updated without the need to re-grant privileges on the view.

  G. Inserting one or more rows using a view whose defining query contains a GROUP BY clause will cause an error.

  Correct Answer: BEF

• Question 257:

  Examine the description of the EMPLOYEES table:

  

  Which two statements will run successfully?

  A. SELECT `The first_name is `` || first_name || `` FROM employees;

  B. SELECT `The first_name is ``` || first_name || ```` FROM employees;

  C. SELECT `The first_name is ``` || first_name || ``` FROM employees;

  D. SELECT `The first_name is ` || first_name || `` FROM employees;

  E. SELECT `The first_name is \`` || first_name || `\`` FROM employees;

  Correct Answer: BD

• Question 258:

  Which three are true about scalar subquery expressions?

  A. A scalar subquery expression that returns zero rows evaluates to zoro

  B. They cannot be used in the values clause of an insert statement*

  C. They can be nested.

  D. A scalar subquery expression that returns zero rows evaluates to null.

  E. They cannot be used in group by clauses.

  F. They can be used as default values for columns in a create table statement.

  Correct Answer: CDE

  Reference: https://docs oracle com/cd/B12037 _01/server.101/b10759/expressions010.htm

• Question 259:

  Examine this statement: Which two statements are true?

  

  A. All remaining employee names will appear in an ascending order

  B. The names of employees remaining the maximum salary will appear first in an ascending order

  C. All remaining employee names will appear in ascending order

  D. All remaining employee names will appear in descending order

  E. The names of employees maximum salary will appear fist to descending order

  F. The names of employees maximum salary will appear fist to ascending order

  Correct Answer: CE

• Question 260:

  Which two statements are true about the results of using the intersect operator in compound queries?

  A. intersect ignores nulls.

  B. Reversing the order of the intersected tables can sometimes affect the output.

  C. Column names in each select in the compound query can be different.

  D. intersect returns rows common to both sides of the compound query.

  E. The number of columns in each select in the compound query can be different.

  Correct Answer: CD