Exam DP-700 All Questions

View all questions & answers for the DP-700 exam

Exam DP-700 topic 1 question 6 discussion

Actual exam question from Microsoft's DP-700

Question #: 6
Topic #: 1

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

Suggested Answer: A

by mixonfreddy at Dec. 16, 2024, 4:59 p.m.

Comments

Submit Cancel

mixonfreddy

Highly Voted 4 months, 1 week ago

Selected Answer: A

SELECT YEAR ROLLUP(YEAR(SO.ModifiedDATE), P.Name)

upvoted 16 times

Madhu2023

2 months, 1 week 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

1 month, 2 weeks 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

shmmini

1 month ago

Rollup adds extra row for grand total (all years, all products), which is not requested. Grouping Sets works better

upvoted 1 times

...

robertlavigne

Highly Voted 3 months 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 9 times

rnd_27

1 month, 2 weeks 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

2 months ago

For example, GROUP BY ROLLUP (Country, Region) and GROUP BY GROUPING SETS ( ROLLUP (Country, Region) ) return the same results.

upvoted 1 times

...

yourock43mfamily

Most Recent 3 weeks, 1 day ago

ROLLUP is correct

upvoted 1 times

...

hebertorosillo

2 months ago

SELECT YEAR GROUPING SETS(...)

upvoted 5 times

...

clux

2 months, 2 weeks ago

1.Year 2. Grouping sets

upvoted 5 times

...

sakis213

2 months, 4 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

2 months, 3 weeks ago

But ROLLUP performs better for these hierarchical aggregations, it might be better to go with ROLLUP

upvoted 1 times

realexamguru

2 months, 2 weeks 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

3 months 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

...

Exam DP-700 All Questions

View all questions & answers for the DP-700 exam

Exam DP-700 topic 1 question 6 discussion

Comments

mixonfreddy

Madhu2023

rnd_27

shmmini

robertlavigne

rnd_27

tpositive

yourock43mfamily

hebertorosillo

clux

sakis213

sakis213

realexamguru

tomaszstaroszczyk

SY0-701