OCP 1Z0 051 140

来源：互联网发布：毒品对社会的危害数据编辑：程序博客网时间：2024/06/05 14:11

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

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;

CREATE OR REPLACE VIEW test.products ASSELECT p.prod_id,       p.prod_name,       p.prod_status,       10                AS qty_in_hand,       p.prod_list_price AS unit_price  FROM sh.products p WHERE rownum <= 10;

要求取最高unit_price * qty_in_hand的name，得到的应该是一条数据

SQL> SELECT prod_name  2    FROM products  3   WHERE (unit_price * qty_in_hand) =  4         (SELECT MAX(unit_price * qty_in_hand) FROM products);PROD_NAME--------------------------------------------------Envoy Ambassador1 row selected

B C 子查询返回的显然不是最高，而是不同prod的最高unit_price * qty_in_hand，返回的是多条数据。=多行数据会报错

SQL> SELECT prod_name  2    FROM products  3   WHERE (unit_price * qty_in_hand) =  4         (SELECT MAX(unit_price * qty_in_hand)  5            FROM products  6           GROUP BY prod_name);SELECT prod_name  FROM products WHERE (unit_price * qty_in_hand) =       (SELECT MAX(unit_price * qty_in_hand)          FROM products         GROUP BY prod_name)ORA-01427: single-row subquery returns more than one row

SQL> SELECT prod_name  2  FROM products  3  GROUP BY prod_name  4  HAVING MAX(unit_price * qty_in_hand) = (SELECT MAX(unit_price * qty_in_hand)  5  FROM products  6  GROUP BY prod_name);SELECT prod_nameFROM productsGROUP BY prod_nameHAVING MAX(unit_price * qty_in_hand) = (SELECT MAX(unit_price * qty_in_hand)FROM productsGROUP BY prod_name)ORA-01427: single-row subquery returns more than one row

D 中的聚合函数嵌套会报错

SQL> SELECT MAX(SUM(unit_price * qty_in_hand)) FROM products;SELECT MAX(SUM(unit_price * qty_in_hand)) FROM productsORA-00978: nested group function without GROUP BY

Answer: A

0 0