ExamTopics Logo
Mail Us[email protected]
Menu
Microsoft Discussions
exam questions

Exam DP-700 All Questions

View all questions & answers for the DP-700 exam
Go to Exam

Exam DP-700 topic 1 question 6 discussion

Actual exam question from Microsoft's DP-700
Question #: 6
Topic #: 1
[All DP-700 Questions]

HOTSPOT -
You have a Fabric workspace that contains a warehouse named DW1. DW1 contains the following tables and columns.

You need to create an output that presents the summarized values of all the order quantities by year and product. The results must include a summary of the order quantities at the year level for all the products.
How should you complete the code? To answer, select the appropriate options in the answer area.
NOTE: Each correct selection is worth one point.

Show Suggested Answer Hide Answer
Suggested Answer: A
by mixonfreddy at Dec. 16, 2024, 4:59 p.m.

Comments

Chosen Answer:
This is a voting comment (?). It is better to Upvote an existing comment if you don't have anything to add.
Switch to a voting comment New
Submit Cancel
mixonfreddy
Highly Voted 3 months ago
Selected Answer: A
SELECT YEAR ROLLUP(YEAR(SO.ModifiedDATE), P.Name)
upvoted 12 times
Madhu2023
3 weeks, 3 days ago
Why not plain Group By when it serves the purpose. We can get the summary anyway without adding an additional clause ?
upvoted 2 times
rnd_27
3 days, 4 hours ago
Because it does not serve the purpose. Requirements are: 1. Summarize order quantities by year and product. 2. Include a summary of order quantities at the year level for all products. A simple GROUP BY cannot generate subtotals or a grand total. You would need to write multiple queries and combine them with UNION ALL, which is inefficient and cumbersome.
upvoted 1 times
...
...
...
robertlavigne
Highly Voted 1 month, 3 weeks ago
SELECT YEAR Grouping Sets: Best answer. No extra stuff. Just what we want Cube: Lots of extra combinations. Has a row with the total for all years and all products, and rows with the total for each product for all years. Rollup: Has the unnecessary total of all products and all years Normal Group by: No summary at the year level for all products. https://learn.microsoft.com/en-us/sql/t-sql/queries/select-group-by-transact-sql?view=sql-server-ver16
upvoted 6 times
rnd_27
3 days, 4 hours ago
But the requirement says: The results must include a summary of the order quantities at the year level for all the products. Therefore ROLLUP should be correct.
upvoted 1 times
...
tpositive
3 weeks, 2 days ago
For example, GROUP BY ROLLUP (Country, Region) and GROUP BY GROUPING SETS ( ROLLUP (Country, Region) ) return the same results.
upvoted 1 times
...
...
hebertorosillo
Most Recent 2 weeks, 5 days ago
SELECT YEAR GROUPING SETS(...)
upvoted 5 times
...
clux
1 month ago
1.Year 2. Grouping sets
upvoted 5 times
...
sakis213
1 month, 2 weeks ago
Question asks for summarized values of all the order quantities by year/product AND year alone , so grouping sets its correct 1.Year 2. Grouping sets
upvoted 5 times
sakis213
1 month, 1 week ago
But ROLLUP performs better for these hierarchical aggregations, it might be better to go with ROLLUP
upvoted 1 times
realexamguru
1 month ago
ROLLUP will add one extra Total row that is not requested in the question, so the only correct answer is grouping sets here.
upvoted 3 times
...
...
...
tomaszstaroszczyk
1 month, 3 weeks ago
SELECT YEAR GROUPING SETS(...) If you use ROLLUP then you will get an extra row that sums up the OrderQty total. This is not requested, ROLLUP returns exactly what we need.
upvoted 6 times
...
Community vote distribution
A (35%)
C (25%)
B (20%)
Other
Most Voted
A voting comment increases the vote count for the chosen answer by one.

Upvoting a comment with a selected answer will also increase the vote count towards that answer by one. So if you see a comment that you already agree with, you can upvote it instead of posting a new comment.

SaveCancel
exam
Someone Bought Contributor Access for:
SY0-701
London, 1 minute ago