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?

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), ())

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

C. SELECT SalesPerso

n.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)

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)

Explanation:

Be careful with this question, because on exam can be different options for answer.

Reference: http://www.grapefruitmoon.net/diving-into-t-sql-grouping-sets/

Reference: http://msdn.microsoft.com/en-us/library/ms177673.aspx