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 5 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 20 times

rnd_27

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

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

Selected Answer: A

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

upvoted 20 times

Madhu2023

4 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

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

3 months ago

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

upvoted 3 times

...

Manish0427

Most Recent 1 week 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 week 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 weeks ago

SELECT YEAR GROUPING SETS(...)

upvoted 2 times

...

renan_moraes37

3 weeks, 3 days ago

First answer: YEAR Second answer: ROLLUP...

upvoted 1 times

...

Runu

1 month 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

1 month ago

Selected Answer: C

upvoted 1 times

...

yourock43mfamily

2 months, 3 weeks ago

ROLLUP is correct

upvoted 1 times

...

hebertorosillo

4 months ago

SELECT YEAR GROUPING SETS(...)

upvoted 9 times

...

clux

4 months, 2 weeks ago

1.Year 2. Grouping sets

upvoted 7 times

...

sakis213

4 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 8 times

sakis213

4 months, 3 weeks ago

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

upvoted 1 times

realexamguru

4 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

5 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 9 times

...