You support a database structure shown in the exhibit. (Refer to 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 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 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)
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:
Verified answer as correct.
Reference: http://www.grapefruitmoon.net/diving-into-t-sql-grouping-sets/
Reference: http://msdn.microsoft.com/en-us/library/ms177673.aspx
I just took the exam today! OMG the qns are so similar! From the score it seems that I have only 1 wrong and this is the qn!
All the choices are the same (in different order) except A and B, even the qn is the same. The choices I mentioned below are pertaining to the qn above with the mentioned modification (pls ignore the different order in the exam).
For A it is “GROUP BY GROUPING SETS((SalesPerson.Name, Country, City, DatePart(yyyy,
SaleDate)), ())” instead of “GROUP BY GROUPING SETS((SalesPerson.Name, Country, City, DatePart(yyyy, SaleDate)), (Country, City), (Country), ())”! For B it is “ROLLUP” instead of “CUBE”.
It seems that all choices are impossible, so I chose C (the only one with CUBE). As I wanted to show the subtotals for city and country, I have to choose CUBE. But the qn said “Sub totals ONLY at the city level and country level”, so I actually pondered for a very long time before I chose C but I am still not confident. Can anyone tell me what should I choose instead and why am I wrong?
I suspect there is a typo in the choices. Can someone comment??
0
0
I think answer A (grouping sets) is correct.
Did try it in AdventureWorks2012 database:
SELECT T.[Group] AS N’Region’, T.CountryRegionCode AS N’Country’
,S.Name AS N’Store’, H.SalesPersonID
,SUM(TotalDue) AS N’Total Sales’
FROM Sales.Customer C
INNER JOIN Sales.Store AS S
ON C.StoreID = S.BusinessEntityId
INNER JOIN Sales.SalesTerritory AS T
ON C.TerritoryID = T.TerritoryID
INNER JOIN Sales.SalesOrderHeader AS H
ON C.CustomerID = H.CustomerID
WHERE T.[Group] = N’Europe’
AND T.CountryRegionCode IN(N’DE’, N’FR’)
AND H.SalesPersonID IN(287, 288, 290)
AND SUBSTRING(S.Name,1,4)IN(N’Vers’, N’Spa ‘)
GROUP BY GROUPING SETS
((T.[Group], T.CountryRegionCode, S.Name, H.SalesPersonID), (T.[Group], T.CountryRegionCode), (T.CountryRegionCode), ())
0
0
I also think it is C that’s the only one that makes sense to me.
0
0
https://technet.microsoft.com/en-us/library/bb522495(v=sql.105).aspx
I think this proves that A is not the correct answer and that C is indeed the correct answer.
0
0
answer A is correct, i tried by creating same tables in my db,
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), ())
–grouping sets.
0
0
Agree with A
though there seems to be a grouping set too much (Country)
0
0
Agree with A
CUBE returns every possible combination of the elements in the GROUP BY.
SalesPerson.Name, Country, City, DatePart(yyyy, SaleDate)
SalesPerson.Name, Country, City
SalesPerson.Name, Country
SalesPerson.Name
Country, City, DatePart(yyyy, SaleDate)
Country, City
Country
…
etc
0
0
BTW, part of that new 200Q 70-461 dumps are available here:
https://drive.google.com/open?id=0B-ob6L_QjGLpfnJldlZxTklTaHM0akpJUzhja2pETHJOS0owMzd4eVk1UTVNQUpvdlVxVWM
Best Regards!
0
0
Hi, did you verified those questions? BR, PC
0
0