（140）Examine the structure of the PRODUCTS table:

name Null Type

PROD_ID NOT NULL NUMBER(4)

PROD_NAME VARCHAR2(20)

PROD_STATUS VARCHAR2(6)

QTY_IN_HAND NUMBER(8,2)

UNIT_PRICE NUMBER(10,2)

You want to display the names of the products that have the highest total value for UNIT_PRICE * QTY_IN_HAND.

Which SQL statement gives the required output?

A.

SELECT prod_name FROM products

WHERE (unit_price * qty_in_hand) = (SELECT MAX(unit_price * qty_in_hand) FROM products);

B.

SELECT prod_name FROM products

WHERE (unit_price * qty_in_hand) = (SELECT MAX(unit_price * qty_in_hand) FROM products GROUP BY prod_name);

C.

SELECT prod_name FROM products GROUP BY prod_name

HAVING MAX(unit_price * qty_in_hand) = (SELECT MAX(unit_price * qty_in_hand) FROM products GROUP BY prod_name);

D.

SELECT prod_name FROM products

WHERE (unit_price * qty_in_hand) = (SELECT MAX(SUM(unit_price * qty_in_hand)) FROM products) GROUP BY prod_name;

答案:（A）

ITPub博客

OCP 1Z0 051 140