A company maintains records of sales made by its salespersons and pays them…
2005
A company maintains records of sales made by its salespersons and pays them commission based on each individual's total sales made in a year. This data is maintained in a table with following schema:
salesinfo = (salespersonid, totalsales, commission)
In a certain year, due to better business results, the company decides to further reward its salespersons by enhancing the commission paid to them as per the following formula:
If commission < = 50000, enhance it by 2%
If 50000 < commission < = 100000, enhance it by 4%
If commission > 100000, enhance it by 6%
The IT staff has written three different SQL scripts to calculate enhancement for each slab, each of these scripts is to run as a separate transaction as follows:
T1
Update salesinfo
Set commission = commission * 1.02
Where commission < = 50000;T2
Update salesinfo
Set commission = commission * 1.04
Where commission > 50000 and commission is < = 100000;T3
Update salesinfo
Set commission = commission * 1.06
Where commission > 100000;Which of the following options of running these transactions will update the commission of all salespersons correctly
- A.
Execute T1 followed by T2 followed by T3
- B.
Execute T2, followed by T3; T1 running concurrently throughout
- C.
Execute T3 followed by T2; T1 running concurrently throughout
- D.
Execute T3 followed by T2 followed by T1
Attempted by 22 students.
Show answer & explanation
Correct answer: D
Correct Answer : D
T3 followed by T2 followed by T1 will be correct execution sequence:
other cases some people will get two times increment
eg if we have T1 followed by T2
if initial commision is 49500
then he is belonging to < 50000
hence, 49500 * 1.02 = 50490
now, he is eligible in second category
then, 50490 * 1.04 = 52509.6
so, he wil get increment two times. but he is eligible for only one slab of commision.