Answer:
SELECT
product_name, list_price, discount_percent,
ROUND(list_price * (discount_percent / 100), 2) AS discount_amount,
ROUND(list_price - (discount_percent / 100 * list_price), 2) AS discount_price
FROM
Products
ORDER BY (list_price - (discount_percent / 100 * list_price)) DESC
LIMIT 5;
Explanation:
In the above SELECT statement is used to select columns from Products table.
The columns are product_name,list_price and discount_percent
Next a column discount_amount is selected which is the calculated from previous two columns that are list_price and discount_percent. The discount amount is basically calculated by multiplying list_price with discount_percent/100 and the resultant column is named as discount_amount using Alias which is used to give a temporary name to set of columns or a table.
Next another column discount_price is obtained from previous three columns that are list_price , discount_percent and discount_amount as: list_price - (discount_percent / 100 * list_price) This as a whole is given a column name discount_price.
FROM is used to refer to a table from which these columns are to be selected. The table name is Product.
The result set is sorted in descending order by discount_price so ORDER BY is used to order the resultant records and DESC is used to sort these records according to the discount_price in descending order.
LIMIT statement is used to extract the records from Product and limit the number of rows returned as a result based on a limit value which is 5 here.
