View the Exhibit and examine the structure of the PRODUCTS table.
You want to display only those product names with their list prices where the list price is at least double the minimum price. The report should start with the product name having the maximum list price satisfying this condition.
Evaluate the following SQL statement:
SQL>SELECT prod_name,prod_list_price
FROM products
WHERE prod_list_price >= 2 * prod_min_price
Which ORDER BY clauses can be added to the above SQL statement to get the correct output?
(Choose all that apply.)
A.
ORDER BY prod_list_price DESC, prod_name;
B.
ORDER BY (2*prod_min_price)DESC, prod_name;
C.
ORDER BY prod_name, (2*prod_min_price)DESC;
D.
ORDER BY prod_name DESC, prod_list_price DESC;
E.
ORDER BY prod_list_price DESC, prod_name DESC;
Anyone can explain why a,e are the right answers? Thanks!
Because the task is “start with the product name having the maximum list price”
if we order by prod_list_price desc
and have 2 top products with the same price list then it’s irrelevant which is shown first
Hi,
I still do not understand, why the condition prod_name desc in option E?
And why option B is incorrect?
Query will be executed successfully but Result will be different from expected. Because the expression 2*prod_min_price gives u more than one row
‘The report should start with the product name having the “maximum list price” satisfying this condition’. So, the ORDER BY clause must contain prod_list_price DESC first then prod_name. It does not matter whether prod_name is DESC or ASC. However, prod_min_price and prod_list_price are not same.