Oracle Oracle Certifications 1Z0-071 Questions & Answers

• Question 381:

  Examine these two queries and their output: SELECT deptno, dname FROM dept;

  

  SELECT ename, job, deptno FROM emp ORDER BY deptno;

  

  Now examine this query:

  SELECT ename, dname

  FROM emp CROSS JOIN dept WHERE job = andapos;MANAGERandapos;

  AND dept.deptno IN (10, 20) ;

  A. 64

  B. 6

  C. 3

  D. 12

  Correct Answer: B

• Question 382:

  Examine the description of the SALES1 table:

  

  SALES2 is a table with the same description as SALES1,

  Some sales data is duplicated In both tables.

  You want to display the rows from the SALES1 table Which are not present in the SALIES2 table.

  Which set operator generates the required output?

  A. SUBTRACT

  B. INTERSECT

  C. UNION ALL

  D. MINUS

  E. UNION

  Correct Answer: D

• Question 383:

  Examine the description of the CUSTOMERS table:

  

  Which two SELECT statements will return these results: (Choose two.)

  CUSTOMER_ NAME

  Mandy Mary

  A. SELECT customer_name FROM customers WHERE UPPER(customer_name) LIKE `MA*';

  B. SELECT customer_name FROM customers WHERE customer_name = `*Ma*';

  C. SELECT customer_name FROM customers WHERE customer_name LIKE `Ma*';

  D. SELECT customer_name FROM customers WHERE UPPER(customer_name) LIKE `MA%';

  E. SELECT customer_name FROM customers WHERE customer_name LIKE `%a%';

  F. SELECT customer_name FROM customers WHERE customer_name LIKE `Ma%';

  G. SELECT customer_name FROM customers WHERE customer_name LIKE `*Ma*';

  Correct Answer: EF

• Question 384:

  Examine the data in the EMP table:

  

  You execute this query:

  

  Why does an error occur?

  A. An alias name must not be used in an ORDER BY clause.

  B. An alias name must not contain space characters.

  C. An alias name must not be used in a GROUP BY clause.

  D. An alias name must always be specified in quotes.

  Correct Answer: C

• Question 385:

  Examine the description of the EMPLOYEES table:

  

  Which statement will execute successfully, returning distinct employees with non-null first names?

  A. SELECT first_name, DISTINCT last_name FROM employees WHERE first_name <> NULL;

  B. SELECT first_name, DISTINCT last_name FROM employees WHERE first_name IS NOT NULL;

  C. SELECT DISTINCT * FROM employees WHERE first_name IS NOT NULL;

  D. SELECT DISTINCT * FROM employees WHERE first_name <> NULL;

  Correct Answer: C

• Question 386:

  In Which three situations does a new transaction always start?

  A. When issuing a SELECT FOR UPDATE statement after a CREATE TABLE AS SELECT statement was issued in the same session

  B. When issuing a CREATE INDEX statement after a CREATE TABLE statement completed unsuccessfully in the same session

  C. When issuing a TRUNCATE statement after a SELECT statement was issued in the same session

  D. When issuing a CREATE TABLE statement after a SELECT statement was issued in the same session

  E. When issuing the first Data Manipulation Language (OML) statement after a COMMIT or ROLLBACK statement was issued in the same session

  F. When issuing a DML statement after a DML statement filed in the same session.

  Correct Answer: ABE

• Question 387:

  Which two statements are true about the COUNT function?

  A. It can only be used for NUMBER data types.

  B. COUNT (DISTINCT inv_amt) returns the number of rows excluding rows containing duplicates and NULLs in the INV_AMT column

  C. COUNT(*) returns the number of rows in a table including duplicate rows and rows containing NULLs in any column.

  D. A SELECT statement using the COUNT function with a DISTINCT keyword cannot have a WHERE clause.

  E. COUNT(inv_amt) returns the number of rows in a table including rows with NULL in the INV_AMT column.

  Correct Answer: BC

• Question 388:

  Which three statements are true about a self join?

  A. It must be an inner join.

  B. It can be an outer join.

  C. The ON clause must be used.

  D. It must be an equation.

  E. The query must use two different aliases for the table.

  F. The ON clause can be used.

  Correct Answer: BEF

• Question 389:

  Which two statements are true about Oracle synonyms?

  A. A synonym can have a synonym.

  B. A synonym has an object number.

  C. Any user can create a public synonym.

  D. All private synonym names must be unique in the database.

  E. A synonym can be created on an object in a package.

  Correct Answer: AE

• Question 390:

  Which statement is true about aggregate functions?

  A. The AVG function implicitly converts NULLS to zero

  B. The MAX and MIN functions can be used on columns with character data types

  C. Aggregate functions can be used in any clause of a SELECT statement

  D. Aggregate functions can be nested to any number of levels

  Correct Answer: B