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

robertlavigne

Highly Voted 6 months, 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 25 times

rnd_27

5 months 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 2 times

...

tpositive

5 months, 3 weeks ago

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

upvoted 1 times

...

mixonfreddy

Highly Voted 8 months ago

Selected Answer: A

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

upvoted 23 times

Madhu2023

5 months, 4 weeks 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

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

4 months, 3 weeks ago

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

upvoted 4 times

...

AndrewsOtis

Most Recent 6 days, 2 hours ago

SELECT YEAR... To extract the year like a field ROLLUP ... to achieve the full sales.

upvoted 1 times

...

jaysun

1 month, 1 week ago

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

upvoted 1 times

...

Manish0427

1 month, 3 weeks ago

Between ROLLUP and GROUPING_SETS in this case, GROUPING_SETS is the correct answer. Though both options will give us the rows stated in the requirement, ROLLUP will give an extra row with total_qty over all the products and all the years (rolling up the products and years). This is not part of the requirement.

upvoted 1 times

...

Wipag

1 month, 4 weeks ago

SELECT YEAR(SO.ModifiedDate) AS OrderDate, P.Name AS ProductName, SUM(SO.OrderQty) AS OrderQty FROM [dbo].[SalesOrderDetail] SO INNER JOIN [dbo].[Product] P ON P.ProductID = SO.ProductID GROUP BY ROLLUP(YEAR(SO.ModifiedDate), P.Name) ORDER BY OrderDate

upvoted 1 times

...

407a475

2 months ago

SELECT YEAR GROUPING SETS(...)

upvoted 2 times

...

renan_moraes37

2 months, 2 weeks ago

First answer: YEAR Second answer: ROLLUP...

upvoted 1 times

...

Runu

2 months, 3 weeks ago

Selected Answer: C Explanation: YEAR(SO.ModifiedDate) extracts the year. ROLLUP(YEAR(SO.ModifiedDate), P.Name) groups the data by year and product, and adds a subtotal at the year level (i.e., all products for a given year). The result will include rows with NULL in ProductName for the yearly totals.

upvoted 1 times

...

Runu

2 months, 3 weeks ago

Selected Answer: C

upvoted 1 times

...

yourock43mfamily

4 months, 1 week ago

ROLLUP is correct

upvoted 1 times

...

hebertorosillo

5 months, 3 weeks ago

SELECT YEAR GROUPING SETS(...)

upvoted 9 times

...

clux

6 months, 1 week ago

1.Year 2. Grouping sets

upvoted 7 times

...

sakis213

6 months, 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 8 times

sakis213

6 months, 2 weeks ago

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

upvoted 1 times

realexamguru

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

6 months, 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 9 times

...