Oracle 1Z0-071 Quiz 69 Topic 3 Questions 341-345

Question: 1

View the Exhibit and examine the structure of the ORDER_ITEMS table. (Choose the best answer.)

q1_1Z0-071

You must select the ORDER_ID of the order that has the highest total value among all the orders in the ORDER_ITEMS table.

Which query would produce the desired result?

ASELECT order_idFROM order_itemsGROUP BY order_idHAVING SUM(unit_price*quantity) = (SELECT MAX (SUM(unit_price*quantity))FROM order_items GROUP BY order_id);

BSELECT order_idFROM order_itemsWHERE(unit_price*quantity) = (SELECT MAX (SUM(unit_price*quantity)FROM order_items) GROUP BY order_id);

CSELECT order_idFROM order_itemsWHERE(unit_price*quantity) = MAX(unit_price*quantity)GROUP BY order_id);

DSELECT order_idFROM order_itemsWHERE (unit_price*quantity) = (SELECT MAX(unit_price*quantity)FROM order_itemsGROUP BY order_id)

Show Answer

Question: 2

View the exhibit and examine the structure and data in the invoice table. (Choose two.)

q2_1Z0-071

Which two SQL statements would execute successfully?

ASELECT MAX(AVG(SYSDATE -inv_date)) FROM invoice

BSELECT AVG(inv_date) FROM invoice

CSELECT MAX(inv_date), MIN(cust_id) FROM invoice

DSELECT AVG( inv_date -SYSDATE), AVG(inv_amt) FROM invoice

Show Answer

Question: 3

View the Exhibit and examine the structure of the ORDERS table. (Choose the best answer.)

q3_1Z0-071

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?

ASELECT order_id, order_date FROM ordersWHERE order_date > ANY(SELECT order_date FROM orders WHERE customer_id = 101);

BSELECT order_id, order_date FROM ordersWHERE order_date > ALL(SELECT MAX(order_date) FROM orders ) AND customer_id = 101;

CSELECT order_id, order_date FROM ordersWHERE order_date > ALL(SELECT order_date FROM orders WHERE customer_id = 101);

DSELECT order_id, order_date FROM ordersWHERE order_date > IN(SELECT order_date FROM orders WHERE customer_id = 101);

Show Answer

Question: 4

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

Which query generates the required output?

ASELECT * FROM users Where user_name LIKE '%ch_';

BSELECT * FROM usersWhere user_name LIKE '%ch_%'ESCAPE'%';

CSELECT * FROM users Where user_name LIKE 'ch\_%' ESCAPE '_';

DSELECT * FROM users Where user_name LIKE '%ch\_%' ESCAPE '\';

Show Answer

Question: 5

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

AIt can be used only with the SELECT clause.

BThe WITH clause can hold more than one query.

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

DThe query name in the WITH clause is visible to other query blocks in the WITH clause as well as to the main query block

Show Answer

Oracle 1Z0-071 Quiz:69 Topic:3 Questions:341-345