Exam 70-761 topic 1 question 181 discussion

Actual exam question from Microsoft's 70-761

Question #: 181
Topic #: 1

A company's sales team is divided in two different regions, North and South. You create tables named SalesNorth and SalesSouth. The SalesNorth table stores sales data from the North region. The SalesSouth table stores sales data from the South region. Both tables use the following structure:

You need to create a consolidated result set that includes all records from both tables.
Which Transact-SQL statement should you run?

A. SELECT SalesNorth.salesID, SalesNorth.customer, SalesNorth.amount, SalesSouth.SalesID, SalesSouth.customer, SalesSouth.amount FROM SalesNorth JOIN SalesSouth ON SalesNorth.salesID = SalesSouth.salesID
B. SELECT SalesNorth.salesID, SalesNorth.customer, SalesNorth.amount, SalesSouth.salesID, SalesSouth.customer, SalesSouth.amount FROM SalesNorth LEFT JOIN SalesSouth ON SalesNorth.salesID=SalesSouth.salesID
C. SELECT salesID, customer, amount FROM SalesNorth UNION ALL SELECT salesID, customer, amount FROM SalesSouth
D. SELECT salesID, customer, amount FROM SalesNorth UNION SELECT salesID, customer, amount FROM SalesSouth

Show Suggested Answer

Suggested Answer: C 🗳️
References:
https://docs.microsoft.com/en-us/sql/t-sql/queries/from-transact-sql?view=sql-server-2017

by stm22 at July 7, 2020, 5:59 p.m.

Comments

Submit Cancel

Vermonster

4 years, 5 months ago

No primary key and two different databases - I go with UNION ALL to make sure I don't miss anything and add key later

upvoted 1 times

...

BabyBee

4 years, 7 months ago

Isn't it valid in the same weight using UNION or UNION ALL? Or is it because of all the Nulls allow not to miss any possible client getting products from both North and South regions?

upvoted 1 times

sssshhhh

4 years, 5 months ago

"all records" requires a union all because like you said, clients can potentially have sales from both regions. When requiring ALL RECORDS from multiple tables it's 'never' safe enough to just do a union.

upvoted 1 times

...

stm22

4 years, 11 months ago

seems odd that no answer includes the region field

upvoted 2 times

sssshhhh

4 years, 5 months ago

it's asking for a consolidated result; no region column needed.

upvoted 1 times

...