You administer a Microsoft SQL Server database thatsupports a banking transaction management application.
You need to retrieve a list of account holders who live in cities that do not have a branch location. Which
Transact-SQL query or queries should you use? (Eachcorrect answer presents a complete solution. Choose all
that apply.)
A.
SELECT AccountHolderID
FROM AccountHolder
WHERE CityID NOT IN (SELECT CityID FROM BranchMaster)
B.
SELECT AccountHolderID
FROM AccountHolder
WHERE CityID <> ALL (SELECT CityID FROM BranchMaster)
C.
SELECT AccountHolderlD
FROM AccountHolder
WHERE CityID <> SOME (SELECT CityID FROM BranchMaster)
D.
SELECT AccountHolderID
FROM AccountHolder
WHERE CityID <> ANY (SELECT CityID FROM BranchMaster)
Explanation:
Verified the answers as correct.
Reference: http://msdn.microsoft.com/en-us/library/ms188047.aspx
Reference: http://msdn.microsoft.com/en-us/library/ms177682.aspx
Reference: http://msdn.microsoft.com/en-us/library/ms173545.aspx
It’s C and D. Null values make A and B fail.
0
0
Nobody says about possible NULLs. And I think, CityID in Branchmaster can’t be NULL, because the branch wouldn’t be in that table, if it didnt have a city to be in.
0
0
But B is not correct, because it will only return accounts if every account lives in the city, where Branch doesn’t exist.
0
0
A and B test it!
1
0
A and B
1
0