Limited Time Discount Offer 30% Off - Ends in 02:00:00

×

70-461 question 15 discussion

Purchase Individually

  • Premium File

    247 Questions & Answers
    Last Update: Dec 5, 2019

    $21.41
    $14.99
  • Training Course

    147 Lectures

    $14.27
    $9.99
  • Study Guide

    123 Pages

    $14.27
    $9.99

Download Free Microsoft 70-461 Exam Questions

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
  • Aug 16, 2018, 12:25 PM

B has syntax errors and is therefore wrong.

  • Aug 9, 2018, 02:53 AM

Why there Company Performance and number 40/20/10 is used? I am not following the logic...Could someone help me plz

  • Jul 3, 2018, 06:37 PM

As others have pointed out, the three fields are defined as NULLable and there is only one of those fields per employee that have a non-null value. Coalese is the correct way to go here.

  • Jul 3, 2018, 06:35 PM

C is correct for reasons others have pointed out. There are MANY questions here... I don't think anyone asked "where is the YEAR field in the BonusParameters table?". If this is a real world problem with a real world solution, we would need to know the year and it says that this table has one row for each calendar year. This is now the 5th really incorrect answer in the first 15 questions. The good news is that their answers, really make you think (since you spend a bunch of time figuring out which one is least wrong).

  • Mar 15, 2018, 07:39 PM

B

  • Nov 2, 2017, 05:55 PM

C

  • Oct 10, 2017, 06:01 PM

I think B is the correct answer. C will fail if any of the other columns not having value = 1 also have a value other than null. Although my personal style would be to write select the other way around.

  • Oct 7, 2017, 03:07 AM

C is the answer since the yearly ,quarterly and monthly are nullable the null values has to be evaluated with coalesce function

  • Sep 26, 2017, 05:12 AM

Can someone explain to my why by 40, 20 and 10?

  • Aug 28, 2017, 03:19 AM

This question is very contradicting with the table structure provided. The question sates that EmployeeBonus table has a non-null value in either the Quarterly, HalfYearly or Yearly column. However, the table structure defines Quarterly, HalfYearly or Yearly column as NULL. So if, Quarterly, HalfYearly or Yearly column are NULL, C is the correct answer. Quarterly, HalfYearly or Yearly column are NOT NULL then B is the correct answer provided the statement is CASE 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

  • Aug 17, 2017, 01:50 PM

Not exactly the best way to design the tables for such a task but B would work if the syntax was correct and the table name removed from the CASE statement as others have mentioned. The only correct answer is C and that will only work in real life if a few more rules are enforced around the data. Also, could use a YEAR column in the BonusParameter table if this is to be used for multiple years.

  • Aug 9, 2017, 03:37 AM

CASE is wrong. If you omit , statement will be a "searched" case expression and give correct results. Ans (C) will work without any changes. There can be only one NON NULL value in those 3 cols. "The EmployeeBonus table has a non-null value in either the Quarterly, HalfYearly or Yearly column"

  • Jul 25, 2017, 08:31 AM

where are you reading the fields are not null, the table example is showing all 3 ( quarterly / half yearly / yearly ) as NULL

  • Jul 17, 2017, 06:17 AM

Because COALESCE returns the first not null value, zero is still counted as a value rather than unknown.

  • Jul 16, 2017, 01:27 AM

b is correct, because you don't need coalesce

  • Jul 14, 2017, 04:18 AM

because the field is not null. coalesce is wrong here, as always not null

  • Jul 14, 2017, 04:18 AM

else is optional

  • Jul 14, 2017, 04:17 AM

B is correct, C is not. the quarterly / half yearly / yearly field is not null thus must assume 1 or 0. C will always go with quarterly, as is a 0 or 1

  • Jul 1, 2017, 08:18 AM

The answer is C

  • Jun 19, 2017, 10:48 PM

Of course the answer is B, C is wrong because of the EmployeeBonus table has a non-null value. That means it appears like this coalesce (0,1,0) or coalesce (0,0,1). if the coalesce was like this (null,null,1) the C was the answer.

  • Jun 9, 2017, 09:18 PM

Why is the return C wrong?

  • May 26, 2017, 07:03 AM

Doesn't Case normally have an else option as a 'catch all' option, as this statement would have no options if there was a row without a 1 value?

  • May 13, 2017, 05:38 PM

Has anyone wrote the exam recently or planning to write?

  • May 7, 2017, 07:25 PM

please any body can u explain detaily

  • May 3, 2017, 12:31 PM

C is the correct answer. B also works, but only when you fill the tables with 1 values. The task only said non-null values and so the Quarterly=1 fails.

  • Apr 25, 2017, 12:51 PM

I agree with you. I think C is the correct. Because the B answer has bad syntax. CASE is not recognized. I tried in sql server 2014.
  • Apr 23, 2017, 12:22 PM

why not C. i think C is the correct option here. if possible please explain it to me

Purchase Individually

  • Premium File

    247 Questions & Answers
    Last Update: Dec 5, 2019

    $21.41
    $14.99
  • Training Course

    147 Lectures

    $14.27
    $9.99
  • Study Guide

    123 Pages

    $14.27
    $9.99

Microsoft 70-461 Training Course

Try Our Special 30% Discount Offer for
Premium 70-461 VCE File

  • Verified by experts

70-461 Premium File

  • Real Questions
  • Last Update: Dec 5, 2019
  • 100% Accurate Answers
  • Fast Exam Update

$14.99

$21.41

WINTER SALE: 30% DISCOUNT
This is ONE TIME OFFER

You save
30%

Enter Your Email Address to Receive Your 30% Discount Code

WINTER SALE: 30% DISCOUNT

You save
30%

Use Discount Code:

A confirmation link was sent to your e-mail.

Please check your mailbox for a message from support@exam-labs.com and follow the directions.

Download Free Demo of VCE Exam Simulator

Experience Avanset VCE Exam Simulator for yourself.

Simply submit your e-mail address below to get started with our interactive software demo of your free trial.

  • Realistic exam simulation and exam editor with preview functions
  • Whole exam in a single file with several different question types
  • Customizable exam-taking mode & detailed score reports