Best strategy

Get Help SQL
#1

Hi there guys!

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.

#2

Hi, welcome to the forums.
Is there an associated lesson for this? Can you link to it?

Are you getting an error? What’s the issue?

#3

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 ?

#4

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)?

(Please tell me if I got it wrong)

#5

Hi there @yizuhi. Thanks for the reply.

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

#6

You could try something like this:

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.

#7

Thanks @yizuhi and @lisalisaj for the support
Codecademy rocks

2 Likes