Oracle Oracle Database 1Z0-071 Questions & Answers

• Question 11:

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

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

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

  Examine the description of the CUSTOMERS table:

  

  Which three statements will do an implicit conversion?

  A. SELECT * FROM customers WHERE TO_ CHAR (customer_ id) = '0001';

  B. SELECT * FROM customers WHERE customer id = '0001';

  C. SELECT * FROM customers WHERE customer_ id = 0001;

  D. SELECT FROM customers WHERE insert date = '01-JAN-19';

  E. SELECT. FROM customers WHERE insert_ date = DATE *2019-01-01';

  F. SELECT. FRON customers WE TO DATE (Insert _ date) = DATE `2019-01-01';

  Correct Answer: BDF

• Question 15:

  You execute this query:

  SELECT TO CHAR (NEXT_DAY(LAST_DAY(SYSDATE),'MON' ),' dd"Monday for" fmMonth rrr') FROM DUAL;

  What is the result?

  A. It executes successfully but does not return any result.

  B. It returns the date for the first Monday of the next month.

  C. It generates an error.

  D. It returns the date for the last Monday of the current month.

  Correct Answer: B

• Question 16:

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

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

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

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

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

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

  D. Views have no segment.

  E. Views have no object number.

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

  Correct Answer: BDF

• Question 19:

  Examine the description of the EMPLOYEES table:

  

  Which statement will fail?

  A. SELECT department_id, COUNT (*) FROM employees HAVING department_ id <> 90 AND COUNT(*) >= 3 GROUP BY department_id;

  B. SELECT department_id, COUNT (*) FROM employees WHERE department_ id <> 90 AND COUNT(*) >= 3 GROUP BY department_id;

  C. SELECT department_id, COUNT(*) FROM employees WHERE department_id <> 90 HAVING COUNT(*) >= 3 GROUP BY department_id;

  D. SELECT department_id, COUNT(*) FROM employees WHERE department_id <> 90 GROUP BY department_id HAVING COUNT(*) >= 3;

  Correct Answer: B

• Question 20:

  Which statement will return the last sequence number generated by the EMP_ SEQ sequence?

  A. SELECT NEXTVAL FROM emp_ seq;

  B. SELECT CURRVAL FROM emp_ seq;

  C. SELECT emp_ seq. CURRVAL FROM DUAL;

  D. SELECT emp_ seq . NEXTVAL FROM DUAL;

  Correct Answer: D