Exam 70-761 All Questions

View all questions & answers for the 70-761 exam

Exam 70-761 topic 1 question 73 discussion

Actual exam question from Microsoft's 70-761

Question #: 73
Topic #: 1

DRAG DROP -
You have a database that contains the following tables:

A delivery person enters an incorrect value for the CustomerID column in the Invoices table and enters the following text in the ConfirmedReceivedBy column:
"Package signed for by the owner Tim."
You need to find the records in the Invoices table that contain the word Tim in the CustomerName field along with the incorrectly entered row from the
Sales.Invoice table.
How should you complete the Transact-SQL statement? To answer, drag the appropriate Transact-SQL segments to the correct locations. Each Transact-SQL segment may be used once, more than once, or not at all. You may need to drag the split bar between panes or scroll to view content.
NOTE: Each correct selection is worth one point.
Select and Place:

Show Suggested Answer

Suggested Answer:

Box 1:SELECT CustomerID FROM Sales.Invoices
Box 2:INNER JOIN Sales.Customers.CustomerID = Sales.Invoices.CustomerID
Box 3:WHERE CustomerName LIKE '%tim%'
Box 4:WHERE ConfirmedReceiveBy IN (SELECT CustomerName FROM Sales.Customers)

by jmllopes at July 25, 2019, 3:13 p.m.

Comments

Submit Cancel

M4x

Highly Voted 5 years, 9 months ago

Read carefully the question: "You need to find the records in the __Invoices__ (__I think Customers__) table that contain the word Tim in the CustomerName field along with the incorrectly entered row from the Sales.Invoice table." No answer fill all box with JOIN, the only winning move is select customerid from sales.customers where customername like '%tim%' union select customerid from sales.invoices where confirmedreceivedby like '%tim%' No-Sense but this is the only answer

upvoted 45 times

Bartek

5 years, 9 months ago

agree with You in 100%

upvoted 1 times

...

Bartek

5 years, 9 months ago

Btw. Its difficult exercise

upvoted 1 times

...

anonimdom

5 years, 6 months ago

why union all can't be an option too? (e.g. if we want to see duplicates)

upvoted 2 times

Barbedx

5 years, 4 months ago

I think this Union because we need to see only incorrect customers from sales.Invoices, So, if there are more then one rows with "tim", it will shows it, if we make union and remove all duplicates its be easy to find what happens.

upvoted 6 times

...

Vanesa30

5 years, 3 months ago

I think so, but the examtopics's answers say the against, but i think the same of your answer

upvoted 1 times

...

ajitpatil

5 years, 4 months ago

This is correct.

upvoted 1 times

...

Load full discussion...

...

jmllopes

Highly Voted 5 years, 11 months ago

A SELECT with three WHERE? Since when sql accepts that syntax?

upvoted 19 times

...

Billybob0604

Most Recent 4 years, 5 months ago

"You need to find the records in the Invoices table that contain the word Tim in the CustomerName" This suggests you need to combine both tables because customername is not in the invoice table.

upvoted 1 times

...

Vermonster

4 years, 5 months ago

Agree need UNION because we are seeking two different data sets. However, the second statement should SELECT CustomerID WHERE confirmedreceivedby = ''Package signed for by the owner Tim.' because the instructions say to also return that one SINGLE record

upvoted 1 times

...

kiri2020

4 years, 8 months ago

SELECT CustomerID FROM Sales.Customers WHERE CustomerName LIKE '%tim%' UNION SELECT CustomerID FROM Sales.Invoices WHERE ConfirmedReceivedBy LIKE '%tim%';

upvoted 2 times

ASQL

4 years, 8 months ago

agreed that is the correct solution

upvoted 1 times

...

supermario

5 years, 3 months ago

:-) M4X is the right one. multiple where clause. Might be the exam accepts such syntax LoL

upvoted 2 times

...

BenAsare

5 years, 7 months ago

That solution provided is absolutely incorrect! M4X solution is the correct answer!

upvoted 1 times

...

raja1234567890

5 years, 7 months ago

This should be full outer join. Customer Name condition will not work as customer can be different person but package signed will be different. We need to add where condition as" where confirmedreceivedby like '%tim%' and sales.invoices.customerId is not null and sales.customers.customerId is null

upvoted 2 times

...

dragan

5 years, 9 months ago

select Invoices.customerID from Invoices Inner join customers on customers.customerID = Invoices.customerID where CustomerName like ‘%tim%’

upvoted 1 times

mlourinho

5 years, 7 months ago

It cannot be join by CustomerId field, because in the question it says: "A delivery person enters an incorrect value for the CustomerID column in the Invoices table"

upvoted 3 times

...

exam_taker5

5 years, 11 months ago

The join needs to be a full outer join because the ID was entered incorrectly. It may not have a match between tables.

upvoted 3 times

...