Exam 70-761 topic 1 question 46 discussion

Actual exam question from Microsoft's 70-761

Question #: 46
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 tables named Customer_CRMSystem and Customer_HRSystem. Both tables use the following structure:

The tables include the following records:

Customer_CRMSystem -

Customer_HRSystem -

Records that contain null values for CustomerCode can be uniquely identified by CustomerName.
You need to display a list of customers that do not appear in the Customer_HRSystem table.
Which Transact-SQL statement should you run?

A.

B.

C.

D.

E.

F.

G.

H.

Show Suggested Answer

Suggested Answer: D
EXCEPT returns distinct rows from the left input query that aren't output by the right input query.
References:
https://msdn.microsoft.com/en-us/library/ms188055.aspx

by Hoey at March 28, 2020, 3:47 a.m.

Comments

Submit Cancel

Popuri

Highly Voted 5 years, 1 month ago

D is the correct answer, only gives what is in CRM

upvoted 8 times

...

Oooo

Highly Voted 4 years, 9 months ago

D is the correct Answer. The question clearly says the CustomerNames can be uniquely identified even though the CustomerCode is null, so we need to to retrieve those customers as well whose customerCode is null . This is possible using except. Except will give you all the rows in CRM which are not present in HRS. This can be achieved by left outer join as well but the join condition should be on CustomerName but in option C its joined in CustoemrCode which contains nulll values hence whenever the join finds null in customerCode it straight away ignores it. so you would not get expected results with Left outer join in C option, so the answer is D. Hope this helps. Happy learning :)

upvoted 7 times

geekeek1

4 years, 5 months ago

yes agreed

upvoted 1 times

...

Billybob0604

Most Recent 4 years, 5 months ago

It should be D. Given the fact that the answer letters are below the possible solutions this causes confusion. But it is the one with the 'EXCEPT' in it.

upvoted 1 times

...