Just went through the content and got thinking how best for my scenario to the following:
Assume having a Sales table with the following fields:
Sales Order ID
Sales Order Item
Customer ID
Date
Transaction Value
And another table named Discounts with the following structure:
Sales Order ID
Customer ID
Discount Value
For a customer and a sales order there can exist a discount value in the Discounts table, however there is no information about the precise discount value per each item in the Sales table. Thus, for this exercise it is necessary to:
a)Define Create Table statements for both tables.
b)Define a strategy to allocate the discount values to the Sales table (e.g., in a new column) and define the necessary query/scripts to execute the logic.
Hi lisa.
Thanks for the answer.
There isnt a specific lesson associated with my question.
I just would like to explore how to build and explore that scenario.
Any ideas ?
Let me see if I got it right (so I can try to help you).
You wanna represent sales (therefore the Sales table). There can also exist discounts related to either a specific sale or a specific customer (therefore the Discounts table).
And your concern is that the Transaction value column of the Sales table won’t reflect the discounts made (if any)?
I think you’re correct, In the Sales table the Transaction value will not reflect the discount, so it asks to how to calculate the Transaction value based on the Discount table by joining them and maybe displaying as new column.
Cheers
SELECT sales.sales_order_id,
sales.transaction_value,
(sales.transaction_value * discounts.discount_value) AS transaction_value_with_discount
FROM sales
JOIN discounts
ON sales.sales_order_id = discounts.sales_order_id
(Assuming the Discount Value values will be like 0.5, 0.2, 0.8, etc. If not, you just have to fix that calculation I made there.)
This, however, will not alter the original table (no new columns are actually added to it), the results are only calculated for the query.
We are doing an inner join of the Sales and Discounts tables, then selecting the Sales Order Id of each sale, its Transaction Value, and then calculating the Transaction Value with Discount for each of the rows.