Exam 70-761 topic 1 question 97 discussion

Actual exam question from Microsoft's 70-761

Question #: 97
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: F 🗳️
A cross join that does not have a WHERE clause produces the Cartesian product of the tables involved in the join. The size of a Cartesian product result set is the number of rows in the first table multiplied by the number of rows in the second table.
References:
https://technet.microsoft.com/en-us/library/ms190690(v=sql.105).aspx

by AshleyLiang at Aug. 24, 2019, 9:57 a.m.

Comments

Submit Cancel

AshleyLiang

Highly Voted 5 years, 9 months ago

It should go for GROUP BY ROLLUP.

upvoted 22 times

Hasanjaf

5 years, 5 months ago

why? i think group by cube

upvoted 3 times

...

getstoopid

Most Recent 4 years, 9 months ago

last four rows of group by rollup (without order by the total line would be at the bottom)

upvoted 1 times

...

CristianCruz

4 years, 10 months ago

BUT SUM 7538.11 1983745.99 2387435981.22 ------------- 2389427265,32

upvoted 1 times

...

CristianCruz

4 years, 10 months ago

BUT THE SUM OF NUL NULL NULL NOT IS CORRECT... THEN?

upvoted 1 times

...

stm22

4 years, 11 months ago

i say CUBE bc the output looks like "subtotals for all combinations" as in: CUBE is an extension of the GROUP BY clause. CUBE allows you to generate subtotals like the ROLLUP extension. In addition, the CUBE extension will generate subtotals for all combinations of grouping columns specified in the GROUP BY clause.

upvoted 1 times

...

Mag53

5 years, 4 months ago

C. GROUP BY ROLLUP is correct ROLLUP sums the Sales at three levels. First it would return the sum of Sales at Country-Province-City level. Then it would sum the sales at Country-Province level and finally on Country level. It would also provide a grand total level (4th row). Thats what is shown in the table. NULL is a placeholder in a row generated as an aggregate result. E. GROUP BY CUBE is incorrect: CUBE groups data in all possible combinations of columns so the sales would be summed up in following levels: 1. Country-Province-City 2. Province-City 3. City 4. Country-Province 5. Province 6. Country-City 7. Country 8. All F. Cross JOIN is completely incorrect as we retrieve data from one table only

upvoted 2 times

...