AllExam Dumps

DUMPS, FREE DUMPS, VCP5 DUMPS| VMWARE DUMPS, VCP DUMPS, VCP4 DUMPS, VCAP DUMPS, VCDX DUMPS, CISCO DUMPS, CCNA, CCNA DUMPS, CCNP DUMPS, CCIE DUMPS, ITIL, EXIN DUMPS,


READ Free Dumps For Microsoft- 70-461





Question ID 15171

You support a database structure shown in the exhibit. (Click the Exhibit button.)

You need to write a query that displays the following details:
✑ Total sales made by sales people, year, city, and country
✑ Sub totals only at the city level and country level
✑ A grand total of the sales amount
Which Transact-SQL query should you use?

Option A

 SELECT SalesPerson.Name, Country, City, DatePart(yyyy, SaleDate) AS Year, Sum(Amount) AS Total FROM Sale INNER JOIN SalesPerson ON Sale.SalesPersonID = SalesPerson.SalesPersonID GROUP BY GROUPING SETS((SalesPerson.Name, Country, City, DatePart(yyyy, SaleDate)), (Country, City), (Country), ())

Option B

SELECT SalesPerson.Name, Country, City, DatePart(yyyy, SaleDate) AS Year, Sum(Amount) AS Total FROM Sale INNER JOIN SalesPerson ON Sale.SalesPersonID = SalesPerson.SalesPersonID GROUP BY CUBE(SalesPerson.Name, Country, City, DatePart(yyyy, SaleDate))

Option C

SELECT SalesPerson.Name, Country, City, DatePart(yyyy, SaleDate) AS Year, Sum(Amount) AS Total FROM Sale INNER JOIN SalesPerson ON Sale.SalesPersonID = SalesPerson.SalesPersonID GROUP BY CUBE(SalesPerson.Name, DatePart(yyyy, SaleDate), City, Country)

Option D

SELECT SalesPerson.Name, Country, City, DatePart(yyyy, SaleDate) AS Year, Sum(Amount) AS Total FROM Sale INNER JOIN SalesPerson ON Sale.SalesPersonID = SalesPerson.SalesPersonID GROUP BY ROLLUP(SalesPerson.Name, DatePart(yyyy, SaleDate), City, Country)

Correct Answer A
Explanation Explanation: Be careful with this question, because on exam can be different options for answer. And none of them is correct: D You should report this question. Reference: http://www.grapefruitmoon.net/diving-into-t-sql-grouping-sets/ Reference: http://msdn.microsoft.com/en-us/library/ms177673.aspx


Question ID 15172

You have a view that was created by using the following code:

You need to create an inline table-valued function named Sales.fn_OrdersByTerritory,
which must meet the following requirements:
✑ Accept the @T integer parameter.
✑ Use one-part names to reference columns.
✑ Filter the query results by SalesTerritoryID.
✑ Return the columns in the same order as the order used in OrdersByTerritoryView.
Which code segment should you use?
To answer, type the correct code in the answer area.

 

Option A

Answer : Please review the explanation part for this answer

Correct Answer A
Explanation Explanation: CREATE FUNCTION Sales.fn_OrdersByTerritory (@T int) RETURNS TABLE AS RETURN ( SELECT OrderID,OrderDate,SalesTerrirotyID,TotalDue FROM Sales.OrdersByTerritory WHERE SalesTerritoryID = @T )

Send email to admin@getfreedumps for new dumps request!!!