Oracle Oracle Database 1Z0-071 Questions & Answers

• Question 1:

  View the exhibit and examine the structure and data in the INVOICE table.

  

  Which two SQL statements would execute successfully? (Choose two.)

  A. SELECT MAX(AVG(SYSDATE -inv_date)) FROM invoice

  B. SELECT AVG(inv_date) FROM invoice

  C. SELECT MAX(inv_date), MIN(cust_id) FROM invoice

  D. SELECT AVG( inv_date -SYSDATE), AVG(inv_amt) FROM invoice

  Correct Answer: CD

• Question 2:

  Which two statements best describe the benefits of using the WITH clause? (Choose two.)

  A. It can improve the performance of a large query by storing the result of a query block having the WITH clause in the session's temporary tablespace.

  B. It enables sessions to reuse the same query block in a SELECT statement, if it occurs more than once in a complex query.

  C. It enables sessions to store a query block permanently in memory and use it to create complex queries.

  D. It enables sessions to store the results of a query permanently.

  Correct Answer: AB

• Question 3:

  Examine these two queries and their output SELECT deptno, dnair.e FROM dept;

  

  SELECT emetine, job, deptno FROM emp ORDER 3Y deptno:

  

  Now examine this query:

  

  How many rows will be displayed?

  A. 64

  B. 6

  C. 3

  D. 12

  Correct Answer: B

• Question 4:

  View the Exhibit and examine the data in the PRODUCTS table.

  

  You must display product names from the PRODUCTS table that belong to the 'Software/other' category with minimum prices as either $2000 or $4000 and with no unit of measure. You issue this query:

  

  Which statement is true?

  A. It executes successfully but returns no result.

  B. It executes successfully and returns the required result.

  C. It generates an error because the condition specified for PROD_UNIT_OF_MEASURE is not valid.

  D. It generates an error because the condition specified for the PROD_CATEGORY column is not valid.

  Correct Answer: A

• Question 5:

  Which three statements are true regarding the usage of the WITH clause in complex correlated subqueries? (Choose three.)

  A. It can be used only with the SELECT clause.

  B. The WITH clause can hold more than one query.

  C. If the query block name and the table name are the same, then the table name takes precedence.

  D. The query name in the WITH clause is visible to other query blocks in the WITH clause as well as to the main query block

  Correct Answer: ABD

• Question 6:

  You must display details of all users whose username contains the string 'ch_'. (Choose the best answer.)

  Which query generates the required output?

  A. SELECT * FROM users Where user_name LIKE '%ch_';

  B. SELECT * FROM users Where user_name LIKE '%ch_%'ESCAPE'%';

  C. SELECT * FROM users Where user_name LIKE 'ch\_%' ESCAPE '_';

  D. SELECT * FROM users Where user_name LIKE '%ch\_%' ESCAPE '\';

  Correct Answer: D

• Question 7:

  View the Exhibit and examine the structure of the ORDERS table.

  

  You must select ORDER_ID and ORDER_DATE for all orders that were placed after the last order placed by CUSTOMER_ID 101.

  Which query would give you the desired result?

  A. SELECT order_id, order_date FROM orders WHERE order_date > ANY (SELECT order_date FROM orders WHERE customer_id = 101);

  B. SELECT order_id, order_date FROM orders WHERE order_date > ALL (SELECT MAX(order_date) FROM orders ) AND customer_id = 101;

  C. SELECT order_id, order_date FROM orders WHERE order_date > ALL (SELECT order_date FROM orders WHERE customer_id = 101);

  D. SELECT order_id, order_date FROM orders WHERE order_date > IN (SELECT order_date FROM orders WHERE customer_id = 101);

  Correct Answer: C

• Question 8:

  Which two statements are true regarding single row functions? (Choose two.)

  A. MOD : returns the quotient of a division.

  B. TRUNC : can be used with NUMBER and DATE values.

  C. CONCAT : can be used to combine any number of values.

  D. SYSDATE : returns the database server current date and time.

  E. INSTR : can be used to find only the first occurrence of a character in a string.

  F. TRIM : can be used to remove all the occurrences of a character from a string.

  Correct Answer: BD

• Question 9:

  Which three statements are true regarding subqueries? (Choose three.)

  A. The ORDER BY Clause can be used in a subquery.

  B. A subquery can be used in the FROM clause of a SELECT statement.

  C. If a subquery returns NULL, the main query may still return rows.

  D. A subquery can be placed in a WHERE clause, a GROUP BY clause, or a HAVING clause.

  E. Logical operators, such as AND, OR and NOT, cannot be used in the WHERE clause of a subquery.

  Correct Answer: ABC

• Question 10:

  Examine the structure of the ORDERS table: (Choose the best answer.)

  

  You want to find the total value of all the orders for each year and issue this command:

  SQL> SELECT TO_CHAR(order_date,'rr'), SUM(order_total) FROM orders

  GROUP BY TO_CHAR(order_date, 'yyyy');

  Which statement is true regarding the result?

  A. It executes successfully but does not give the correct output.

  B. It executes successfully and gives the correct output.

  C. It returns an error because the TO_CHAR function is not valid.

  D. It return an error because the datatype conversion in the SELECT list does not match the data type conversion in the GROUP BY clause.

  Correct Answer: D