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;
Here it asks for the highest TOTAL value of UNIT_PRICE *
QTY_IN_HAND.
But Option A gives only the highest value without group by or using SUM.
How is A correct
A returns only one row: the prod_name with MAX(unit_price*qty_in_hand).
For exmaple:
select ename from emp where sal+nvl(comm,0) = (select max(sal+nvl(comm,0)) from emp);
ENAME
———-
KING
But what if there is duplicate records for the same product name with different UNIT_PRICE *QTY_IN_HAND values?
we need not find the GROUP BY based on product name?
In this case you mean the record with max(UNIT_PRICE *QTY_IN_HAND) will be displayed irrespective of duplicates or not?
We only need to know what is the maximum value for all products in table not for each product. Functions like max, avg, min… return one record regardles of how many duplicate values is in table when ‘group by ‘ is not used.
Then it should be the name of PRODUCT right?its given as name of PRODUCTS(plural).
Is the question framed tricky ?
Should the ans be D
if the question would be “You want to display the name of the PRODUCT that have the highest total value for UNIT_PRICE * QTY_IN_HAND then the answer would be:
SELECT MAX(unit_price * qty_in_hand) FROM products
but we need to know the names of all products that have the highest total value for UNIT_PRICE * QTY_IN_HAND. To solve this problem you use subquery like in A. ‘Group by’ is not needed to get correct result.
http://www.devshed.com/c/a/Oracle/Stepping-through-SubQueries-in-Oracle/2/
D is not correct:
– there is max(sum(….)) so you have nested group functions there
If A is correct answer then Why B is incorrect?
Because A will not sort out inner query result set but B will , still comparison is done for every row in the inner query result set.
The PRODUCTS table has all the individual product names. There is no way having repeated product names. So, GROUP BY prod_name clause does not make any sense here as all rows are distinct. The question asked ‘nameS of the products’ meaning more than one products could have the highest total value for UNIT_PRICE * QTY_IN_HAND.
D tambien es correcta, si para cada uno de los registros en la tabla productos, sea único e irrepetible.
I think question is not framed correctly here. It says to display ‘Names of the products’ NOT ‘name of a product’. So as per the question, a single product may appar more than once which may require group by clause. OR in this case, table values should have been provided to avoid confusion. As per the question i do not think answer is A is correct.
I really liked your article post.