Exam 70-461 topic 1 question 29 discussion

Actual exam question from Microsoft's 70-461

Question #: 29
Topic #: 1

You administer a Microsoft SQL Server database that supports a shopping application.
You need to retrieve a list of customers who live in territories that do not have a sales person.
Which Transact- SQL query or queries should you use? (Each correct answer presents a complete solution. Choose all that apply.)

A. SELECT CustomerID FROM Customer WHERE TerritoryID <> SOME(SELECT TerritoryID FROM Salesperson)
B. SELECT CustomerID FROM Customer WHERE TerritoryID <> ALL(SELECT TerritoryID FROM Salesperson)
C. SELECT CustomerID FROM Customer WHERE TerritoryID <> ANY(SELECT TerritoryID FROM Salesperson)
D. SELECT CustomerID FROMCustomer WHERE TerritoryID NOT IN(SELECT TerritoryID FROM Salesperson)

Show Suggested Answer

Suggested Answer: BD 🗳️

by dennix at April 5, 2020, 12:35 p.m.

Comments

Submit Cancel

dennix

5 years, 3 months ago

Try these queries in your adventureworks database. You won't get any results because there are no customers with a territory that has no salesperson. You can fix this by changing the territoryid of the salesperson with territoryid 2 into 1. Now there are customers from '2' but no salesperson for '2'. Still no result! THis is bercause of three-valued logic. Some salespersons have territoryid NULL, and thus the predicate "territoryid <> all(select TerritoryID from sales.SalesPerson)" evaluates to UNKNOWN, and not to true for any of the customers. :-( . So you have deal with the nulls, either in the source data or in the predicate: "territoryid <> all(select isnull(TerritoryID,0) from sales.SalesPerson)"

upvoted 1 times

Sugar

5 years, 2 months ago

I agree!

upvoted 1 times

...