Answer:
SELECT
item_ID, item_price,
discount_amount, quantity,
item_price * quantity AS price_total,
discount_amount * quantity AS discount_total,
(item_price - discount_amount) * quantity AS item_total
FROM Order_Items
WHERE (item_price - discount_amount) * quantity > 500
ORDER BY item_total DESC;
Explanation:
SELECT statement is used to select set of records data or from one or more tables.
So here SELECT statement selects data from the following columns:
item_ID
item_price
discount_amount
quantity
item_price * quantity AS price_total,
Here AS is used and it refers to alias which gives a name to a column or table. This name is temporary. Here the column which is the product of two columns item_price and quantity is given the name of price_total.
discount_amount
this is the product of discount_amount and quantity columns.
item_total
this column is obtained by subtracting the discount_amount from the item_price and then multiplying by the quantity.
These columns are selected from table named Order_Items using SELECT statement.
WHERE clause is used here to put a condition in order to filter the data. It allows to retrieve only those records which satisfies the condition given in this clause. Here only those records are fetched where the item_total is greater than 500. item_total is calculated by
(item_price - discount_amount) * quantity
ORDER BY specifies that the resultant records will be arranged in the specified order. Here DESC keyword is used to sort the resultant data in descending order. So the data will be returned in descending order.
