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 AS
SELECT 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 Ambassador
1 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_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)
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 products
ORA-00978: nested group function without GROUP BY
Answer: A