View the Exhibit and examine the structure of the PRODUCTS table.
Evaluate the following query:
SQL> SELECT prod_name
FROM products
WHERE prod_id IN (SELECT prod_id FROM products
WHERE prod_list_price =
(SELECT MAX(prod_list_price)FROM products
WHERE prod_list_price <
(SELECT MAX(prod_list_price)FROM products)));
What would be the outcome of executing the above SQL statement?
A.
It produces an error.
B.
It shows the names of all products in the table.
C.
It shows the names of products whose list price is the second highest in the table.
D.
It shows the names of all products whose list price is less than the maximum list price.
can anyone xplain me how it gets the list price second highest in table
waiting from all ur responses
regards
arya
The most inner query is:
(SELECT MAX(prod_list_price)FROM products
and as result of
WHERE prod_list_price < (SELECT MAX(prod_list_price)FROM products
we get list of prod_list_price without maxmimum prod_list_price.
Then we get again maximum prod_list_price what is in fact the second highest prod_list_price:
SELECT MAX(prod_list_price)FROM products
WHERE prod_list_price < (SELECT MAX(prod_list_price)FROM products)
and we get condition, describing in words:
WHERE prod_list_price = "the second highest prod_list_price".
It is just one value not a list of prod_list_price any more.
Condition:
WHERE prod_id IN (SELECT prod_id FROM products
WHERE prod_list_price = ……..
is in fact
WHERE prod_id IN ("the second highest prod_list_price").
thanks a lot for ur detail xplanation JUSTYNA
warm greets
Arya
Hi,
last to first
SELECT MAX(prod_list_price)FROM products –>it selects 1st max price product
prod_list_price it selects all list except 1st max price product
SELECT MAX(prod_list_price)FROM products –>it selects 2nd max price product becoz 1st product is skipped in previous step
Last subquery will fetch the highest price …
second last subquery will fetch the records whose price less than last subquery (Max Price). that mean the prod_list_price will be second highest.