Exam 70-761 topic 1 question 96 discussion

Actual exam question from Microsoft's 70-761

Question #: 96
Topic #: 1

Note: This question is part of a series of questions that use the same or similar answer choices. An answer choice may be correct for more than one question in the series. Each question is independent of the other questions in this series. Information and details provided in a question apply only to that question.
You have a database that contains several connected tables. The tables contain sales data for customers in the United States only.
You have the following partial query for the database. (Line numbers are included for reference only.)

You need to complete the query to generate the output shown in the following table.

Which statement clause should you add at line 3?

A. GROUP BY
B. MERGE
C. GROUP BY ROLLUP
D. LEFT JOIN
E. GROUP BY CUBE
F. CROSS JOIN
G. PIVOT
H. UNPIVOT

Show Suggested Answer

Suggested Answer: C 🗳️
In the result sets that are generated by the GROUP BY operators, NULL has the following uses:
✑ If a grouping column contains NULL, all null values are considered equal, and they are put into one NULL group.
✑ When a column is aggregated in a row, the value of the column is shown as NULL.
Example of GROUP BY ROLLUP result set:

References:
https://technet.microsoft.com/en-us/library/bb522495(v=sql.105).aspx

by safiullah at Aug. 16, 2019, 1:53 p.m.

Comments

Submit Cancel

safiullah

Highly Voted 5 years, 10 months ago

This should be a GROUP BY CUBE.

upvoted 21 times

...

Vermonster

Most Recent 4 years, 5 months ago

GROUP BY CUBE to get NULL, NULL, NULL - assuming other combinations not shown

upvoted 1 times

...

Andy7622

4 years, 6 months ago

it's GROUP BY CUBE . TESTED . Sorry for typos and orphography CREATE TABLE Sales( CoutnryName varchar(50), StateProvinceName varchar(50), CityName varchar(50), Qwt int, Price decimal(16,4) ) INSERT INTO Sales VALUES ('USA', 'Florida', 'Miami', 4,1.36),('USA', 'Washington', 'Seattle', 5, 2.67),('USA', 'Colorado', 'Denver', 4,20.6), ('Canada', 'Ontario', 'Toronto', 6, 1.47),('Canada', 'Manitoba', 'Winnipeg', 20, 12.48),('Canada', 'Alberta', 'Edmonton', 20,38.4 ), ('Germany', 'Bavaria','Munchen', 22, 30.48),('Germany', 'BadenWuttemberg', 'Stuttgart', 30,1.84),('Germany', 'Hessen', 'FrankfutMain', 202, 12.20) SELECT * FROM Sales SELECT CoutnryName, StateProvinceName, CityName, SUM(Qwt*Price) AS TotalSales FROM Sales GROUP BY CUBE (CoutnryName, StateProvinceName, CityName) ORDER BY CoutnryName, StateProvinceName, CityName

upvoted 2 times

Skc20

4 years, 5 months ago

yes. Agreed

upvoted 1 times

...

mona1109

4 years, 8 months ago

I'd say it's GROUP BY CUBE. With an example given in docs under point E. : https://docs.microsoft.com/en-us/previous-versions/sql/sql-server-2008-r2/bb522495(v=sql.105)?redirectedfrom=MSDN it lists all the hierarchy lowest level (or the last column in Order By) first, here it's City Name. I think the lack of aggregation on the value is yet another typo in question.

upvoted 1 times

...

Oooo

4 years, 9 months ago

Answer is surely not Rollup and cube because it has to have all the columns in group by in select clause, here grouping is just done on City Column. We cannot just rollup or cube with single column and have multiple columns in select clause, it will throw an error. Not sure if this even possible by the options available here. This is little complex.

upvoted 1 times

...

getstoopid

4 years, 10 months ago

I think it is CUBE because in my opinion we could safely assume that the order of the grouping columns is equal to the input. Because if it is not so there would be no chance for us to determine what group by statement was used. I tested some of them

upvoted 1 times

getstoopid

4 years, 10 months ago

Can't post the code - seems to be too much? (bad gateway error) But if you build a simple table (Group1, Group2, Group3, Value) and insert values like (1, 'A', 'AA', 'AAA', 10) ,(2, 'A', 'AA', 'AAB', 10) ... ,(10, 'B', 'BA', 'BAA', 12) ... you will see pretty quick that cube looks exactly like rollup if you write it like this "group by rollup (group3, group2, group1) the same thing happens with grouping sets as AK80 stated

upvoted 2 times

...