You support a database structure shown in the exhibit.
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 SalesPerson.Name, Country, City,
DatePart(yyyy, SaleDate)
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 ROLLUP(SalesPerson.Name, Country, City,
DatePart(yyyy, SaleDate))
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 ROLLUP(SalesPerson.Name, DatePart(yyyy, SaleDate),
City, Country)
D. SELECT SalesPerson.Name, Country, City,
DatePart(yyyy, SaleDate), Sum(Amount)
FROM Sale
INNER JOIN SalesPerson
ON Sale.SalesPersonId = SalesPerson.SalesPersonId
GROUP BY SalesPerson.Name, Country, City,
DatePart(yyyy, SaleDate),
GROUPING SETS((Country, City, DatePart(yyyy, SaleDate)),
( Country, City) (Country),())
