Question

Write a SELECT statement that returns these column names and data from the Order_Items table:

item_id The item_id column
item_price The item_price column
discount_amount The discount_amount column
quantity The quantity column
price_total A column that’s calculated by multiplying the item price by the quantity
discount_total A column that’s calculated by multiplying the discount amount by the quantity
item_total A column that’s calculated by subtracting the discount amount from the item price and then multiplying by the quantity
Only return rows where the item_total is greater than 500. Sort the result set by item total in descending sequence.

Answer 1

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;

Step-by-step 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.