Oracle Oracle Certifications 1Z0-071 Questions & Answers

• Question 221:

  Which two are true about rollbacks?

  A. The ROLLBACK statement does not release locks resulting from table updates.

  B. Data Control L anguage (DCL) statements, such as GRANT and REVOKE, can be rolled back.

  C. A transaction interrupted by a system failure is automatically rolled back.

  D. If the ROLLBACK statement is used without TO SAVEPOINT, then all savepoints in the transaction are deleted .

  E. Data consistency is not guaranteed after a rollback.

  Correct Answer: CD

• Question 222:

  Which two are true? (Choose two.)

  A. CONCAT joins two character strings together.

  B. CONCAT joins two or more character strings together.

  C. FLOOR returns the largest positive integer less than or equal to a specified number.

  D. INSTR finds the offset within a character string, starting from position 0.

  E. INSTR finds the offset within a string of a single character only.

  F. FLOOR returns the largest integer less than or equal to a specified number.

  Correct Answer: AF

• Question 223:

  Examine the description of the ORDERS table:

  

  Examine the description of the INVOICES table:

  

  Which three statements execute successfully? (Choose three.)

  A. SELECT * FROM orders ORDER BY order_id INTERSECT SELECT * FROM invoices ORDER BY invoice_id;

  B. SELECT * FROM orders UNION ALL SELECT * FROM invoices) ORDER BY order_id;

  C. SELECT order_id, order_date FROM orders

  UNION ALL

  SELECT invoice_id, invoice_date FROM invoices ORDER BY order_id;

  D. SELECT * FROM orders MINUS SELECT * FROM invoices ORDER BY 1;

  E. SELECT order_id invoice_id, order_date FROM orders MINUS SELECT invoice_id, invoice_date FROM invoices ORDER BY invoice_id;

  F. SELECT * FROM orders ORDER BY order_id UNION SELECT * FROM invoices;

  G. SELECT order_id, order_date FROM orders INTERSECT SELECT invoice_id, invoice_date FROM invoices ORDER BY invoice_id;

  Correct Answer: CDE

• Question 224:

  Examine this query:

  SELECT INTERVAL '100' MONTH DURATION FROM DUAL;

  What will be the output?

  A. DURATION +08-04

  B. DUFATION +100

  C. DURATION +08

  D. an error

  Correct Answer: A

• Question 225:

  Examine these statements and results:

  

  How many rows are retrieved by the last query?

  A. 28

  B. 0

  C. 14

  D. 42

  Correct Answer: C

• Question 226:

  Examine the description of the MEMBERS table:

  

  Examine the partial query:

  SELECT city, last_name LNAME FROM members ...;

  You want to display all cities that contain the string AN. The cities must be returned in ascending order, with the last names further sorted in descending order.

  Which two clauses must you add to the query? (Choose two.)

  A. ORDER BY 1,2.

  B. ORDER BY last_name DESC,city ASC

  C. ORDER BY 1, LNAME DESC

  D. WHERE city='%AN%;

  E. WHERE city LIKE '%AN%;

  F. WHERE city IN ('%AN%')

  Correct Answer: CE

• Question 227:

  Examine the BRICKS table:

  

  You write this query:

  SELECT

  FROM bricks b1 CROSS JOIN bricks b2

  WHERE b1. Weight < b2. Weight:

  How many rows will the query return?

  B. 16

  C. 10

  D. 6

  E. 4

  F. 0

  Correct Answer: D

• Question 228:

  Which two tasks require subqueries?

  A. Display the total number of products supplied by supplier 102 Which have a product status of obsolete.

  B. Display suppliers whose PROD_LIST_PRICE is less than 1000.

  C. Display the number of products whose PROD_LIST_PRICE is more than the average PROD_LIST_PRICE.

  D. Display the minimum PROD_LIST_PRICE for each product status.

  E. Display products whose PROD_MIN_PRICE is more than the average PROD_LIST_PRICE of all products, and whose status is orderable.

  Correct Answer: CE

• Question 229:

  Examine the description of the ORDER_ITEMS table:

  

  Examine this incomplete query:

  

  Which two can replace so the query completes successfully?

  A. quantity * unit_price

  B. quantity

  C. total_paid

  D. product_id

  E. quantity, unit_price

  Correct Answer: AC

• Question 230:

  Table ORDER_ITEMS contains columns ORDER_ID, UNIT_PRICE and QUANTITY, of data type NUMBER

  Statement 1:

  SELECT MAX (unit price*quantity) "Maximum Order FROM order items;

  Statement 2:

  SELECT MAX (unit price*quantity "Maximum order" FROM order items GROUP BY order id;

  Which two statements are true?

  A. Statement 2 returns only one row of output.

  B. Both the statement given the same output.

  C. Both statements will return NULL if either UNIT PRICE or QUANTITY contains NULL,

  D. Statement 2 may return multiple rows of output.

  E. Statement 1 returns only one row of output.

  Correct Answer: DE