Querying Microsoft SQL Server 2012/2014 — Question 6

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.)

Answer options

• 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)

Correct answer: B, D

Explanation

Option B is correct because it ensures the TerritoryID of the customers is not equal to any of the TerritoryIDs associated with salespeople, effectively filtering out customers in territories with salespeople. Option D is also correct as it directly excludes any customers whose TerritoryID is found in the list of TerritoryIDs from the Salesperson table. Options A and C do not correctly filter the customers based on the requirement of finding those in territories without a salesperson.