You have a Microsoft SQL Server database that includes two tables named EmployeeBonus and BonusParameters. The tables are defined by using the following Transact-SQL statements:
The tables are used to compute a bonus for each employee. The EmployeeBonus table has a non- null value in either the Quarterly, HalfYearly or Yearly column. This value indicates which type of bonus an employee receives. The BonusParameters table contains one row for each calendar year that stores the amount of bonus money available and a company performance indicator for that year.
You need to calculate a bonus for each employee at the end of a calendar year.
Which Transact-SQL statement should you use?
A. SELECT
CAST(CHOOSE((Quarterly * AvailableBonus * CompanyPerformance)/40, (HalfYearly * AvailableBonus * CompanyPerformance)/20,
(Yearly * AvailableBonus * CompanyPerformance)/10) AS money) AS ‘Bonus’
FROM
EmployeeBonus, BonusParameters
B. SELECT “Bonus” = CASE EmployeeBonus
WHEN Quarterly=1 THEN (Quarterly * AvailableBonus * CompanyPerformance)/40
WHEN HalfYearly=1 THEN (HalfYearly * AvailableBonus * CompanyPerformance)/20 WHEN Yearly=1 THEN (Yearly * AvailableBonus *
CompanyPerformance)/10 END
FROM EmployeeBonus,BonusParameters
C. SELECT
CAST(COALESCE((Quarterly * AvailableBonus * CompanyPerformance)/40, (HalfYearly * AvailableBonus * CompanyPerformance)/20,
(Yearly * AvailableBonus * CompanyPerformance)/10) AS money) AS ‘Bonus’
FROM
EmployeeBonus, BonusParameters
D. SELECT
NULLIF(NULLIF((Quarterly * AvailableBonus * CompanyPerformance)/40,(HalfYearly * AvailableBonus * CompanyPerformance)/20), (Yearly *
AvailableBonus * CompanyPerformance)/10) AS ‘Bonus’
FROM
EmployeeBonus, BonusParameters
