Oracle Oracle Certifications 1Z0-071 Questions & Answers

• Question 231:

  Which two are true about a SQL statement using SET operators such as UNION? (Choose two.)

  A. The data type group of each column returned by the second query must match the data type group of the corresponding column returned by the first query.

  B. The names and number of columns must be identical for all select statements in the query.

  C. The data type of each column returned by the second query must be implicitly convertible to the data type of the corresponding column returned by the first query.

  D. The data type of each column returned by the second query must exactly match the data type of the corresponding column returned by the first query.

  E. The number, but not names, of columns must be identical for all select statements in the query.

  Correct Answer: AE

• Question 232:

  Examine the description of the EMPLOYEES table:

  

  Which two queries will execute successfully? (Choose two.)

  A. SELECT dept_id, AVG(MAX(salary)) FROM employees GROUP BY dept_id HAVING hire_date > '01-JAN-19';

  B. SELECT dept_id, SUM(salary) FROM employees WHERE hire_date > '01-JAN-19' GROUP BY dept_id;

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

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

  E. SELECT AVG(MAX(salary)) FROM employees GROUP BY salary;

  Correct Answer: BE

• Question 233:

  You must find the number of employees whose salary is lower than employee 110.

  Which statement fails to do this?

  

  A. Option A

  B. Option B

  C. Option C

  D. Option D

  Correct Answer: A

• Question 234:

  Which two are true about queries using set operators such as UNION? (Choose two.)

  A. An expression in the first SELECT list must have a column alias for the expression.

  B. CHAR columns of different lengths used with a set operator return a VARCHAR2 whose length equals the longest char value.

  C. Queries using set operators do not perform implicit conversion across data type groups (e.g. character, numeric).

  D. In a query containing multiple set operators, INTERSECT always takes precedence over UNION and UNION ALL.

  E. All set operators are valid on columns of all data types.

  Correct Answer: BC

• Question 235:

  Which two statements are true about the WHERE and HAVING clauses in a SELECT statement? (Choose two.)

  A. WHERE and HAVING clauses can be used in the same statement only if applied to different table columns.

  B. The WHERE clause can be used to exclude rows after dividing them into groups.

  C. The HAVING clause can be used with aggregating functions in subqueries.

  D. The WHERE clause can be used to exclude rows before dividing them into groups.

  E. Aggregating functions and columns used in HAVING clauses must be specified in the SELECT list of a query.

  Correct Answer: CD

• Question 236:

  Examine the description of the CUSTOMERS table:

  

  Which three statements will do an implicit conversion?

  A. SELECT * FROM customers WHERE insert_date=DATE'2019-01-01';

  B. SELECT * FROM customers WHERE customer_id='0001';

  C. SELECT * FROM customers WHERE TO_DATE(insert_date)=DATE'2019-01-01';

  D. SELECT * FROM customers WHERE insert_date'01-JAN-19';

  E. SELECT * FROM customers WHERE customer_id=0001;

  F. SELECT * FROM customers WHERE TO_CHAR(customer_id)='0001';

  Correct Answer: BCD

• Question 237:

  Examine the data in the COLORS table:

  

  Examine the data in the BRICKS table:

  

  Which three privileges can be restricted to a subset of columns in a taS?

  A. SELECT. FROM bricks b RIGHT JOIN colors c ON b. color _rgb_ hex_ value = c. rgb hex_ value;

  B. SELECT EROM colors C LEFT JOIN bricks USING (rgb _ hex_ value) ;

  C. SELECT FROM bricks b FULL JOIN colors C ON b. color rgb _ hex_ value = c. rgb _hex_ value;

  D. SELECT * EROM bricks | b JOIN colors C ON b. color_ rgb_ hex_ value =c. rgb _hex value;

  E. SELECT EROM colors C LEET JOIN bricks b ON b. color_ rgb_ hex value = c. rgb. hex. value WHERE b. brick_ id > 0;

  Correct Answer: AC

• Question 238:

  Which two are true about external tables that use the ORACLE_DATAPUMP access driver? (Choose two.)

  A. Creating an external table creates a directory object.

  B. When creating an external table, data can be selected only from a table whose rows are stored in database blocks.

  C. When creating an external table, data can be selected from another external table or from a table whose rows are stored in database blocks.

  D. Creating an external table creates a dump file that can be used by an external table in the same or a different database.

  E. Creating an external table creates a dump file that can be used only by an external table in the same database.

  Correct Answer: AD

• Question 239:

  Examine this statement:

  

  What is returned upon execution?

  A. 0 rows

  B. an error

  C. 1 row

  D. 2 rows

  Correct Answer: C

• Question 240:

  Examine the description of the EMPLOYEES table:

  

  Which two queries return rows for employees whose manager works in a different department?

  

  A. Option A

  B. Option B

  C. Option C

  D. Option D

  E. Option E

  Correct Answer: BE