What would be the outcome of executing the above SQL statement?

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?

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.



Leave a Reply 5

Your email address will not be published. Required fields are marked *


arya

arya

can anyone xplain me how it gets the list price second highest in table

waiting from all ur responses

regards
arya

Justyna

Justyna

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").

arya

arya

thanks a lot for ur detail xplanation JUSTYNA

warm greets

Arya

trinath

trinath

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

saurabh

saurabh

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.