Exam 70-761 All Questions

View all questions & answers for the 70-761 exam

Exam 70-761 topic 1 question 137 discussion

Actual exam question from Microsoft's 70-761

Question #: 137
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: A 🗳️

by fabzo at Nov. 20, 2019, 10:30 a.m.

Comments

Submit Cancel

Mag53

Highly Voted 5 years, 3 months ago

GROUP BY CityName

upvoted 8 times

Andy7622

4 years, 4 months ago

Group by everything . You must include all the columns in Group By if they aren't aggreagates

upvoted 2 times

...

kimalto452

Most Recent 4 years, 4 months ago

6 Group by can used in Two way Majorly 1)in conjunction with SQL aggregation functions 2)to eliminate duplicate rows from a result set

upvoted 1 times

...

BabyBee

4 years, 5 months ago

Everything in the SELECT list must appear in the GROUP BY Clause unless it is an aggregate function. -- Using TSQLV4 Database SELECT [shipcountry], [shipregion], [shipcity], freight + 10 AS TotalSales FROM [Sales].[Orders] GROUP BY [shipcountry], [shipregion], [shipcity], freight + 10; /* If we don't add the mathematical operation to the GROUP BY it returns the below error: Msg 8120, Level 16, State 1, Line 151 Column 'Sales.Orders.freight' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

upvoted 1 times

...

zeinaa

4 years, 7 months ago

correct answer is group by cube

upvoted 2 times

tzaganczyk

4 years, 6 months ago

Nope. When you apply GROUP BY CUBE with all fields you'll get the dataset of all fields combinations with NULLs as well. The output shown has only 5 rows and looking at the list of fields in SELECT statement (without aggregation aplied) you have to list them all in GROUP BY clause. So answer A is correct.

upvoted 1 times

...

Andy7622

4 years, 4 months ago

no , You can't figure it out from the this partial data

upvoted 1 times

...

Oooo

4 years, 7 months ago

The correct answer is group by, there is an error in the question. All the previous questions of this series dont have aggregate function which is nothing but an error.

upvoted 1 times

...

Prides

5 years, 3 months ago

this is not an aggregate function, so should be left join

upvoted 3 times

Jiacheng

5 years, 2 months ago

no, group by can be used in non-aggregation statement. and in this case, you can easily find out that CITYs are unique, so absolutely they must group by name, city, price and so on

upvoted 2 times

ricky59

4 years, 11 months ago

I just tried grouping by a primary key without an aggregation and am getting an error... I think the only answer that could work is LEFT JOIN with all the columns in the SELECT list being unique...

upvoted 2 times

...

fabzo

5 years, 5 months ago

Would this be Group by totalsales?

upvoted 1 times

...

Exam 70-761 All Questions

View all questions & answers for the 70-761 exam

Exam 70-761 topic 1 question 137 discussion

Comments

Mag53

Andy7622

kimalto452

BabyBee

zeinaa

tzaganczyk

Andy7622

Oooo

Prides

Jiacheng

ricky59

fabzo

SY0-701