You work as a database developer at ABC.com. ABC.com has a SQL Server 2012 database
named SalesDB as illustrated in the following database diagram:
ABC.com has retail stores in a few major cities across the country. The company wants to
ascertain whether it would be advantageous to open a store in other cities based on feedback
from its customers.
You are required to provide the company’s CEO with a list of Customers who live in a city that
does not have a ABC.com store, along with the customer’s Phone Number and the customer’s
City, and arranged alphabetically by City name.
Which of the following Transact-SQL statements would return the required information?
A.
SELECT CustomerName, CustomerCity, CustomerPhone
FROM Customers
WHERE CustomerCity NOT EXISTS (SELECT StoreCity FROM Stores)
ORDER BY CustomerCity
B.
SELECT CustomerName, CustomerCity, CustomerPhone
FROM Customers
WHERE CustomerCity < > ALL (SELECT StoreCity FROM Stores)
ORDER BY StoreCity
C.
SELECT CustomerName, CustomerCity, CustomerPhone
FROM Customers
WHERE CustomerCity < > ANY (SELECT StoreCity FROM Stores)
ORDER BY CustomerCity
D.
SELECT CustomerName, CustomerCity, CustomerPhone
FROM Customers
WHERE CustomerCity NOT IN (SELECT StoreCity FROM Stores)
ORDER BY StoreCity
Explanation:
B, C, and D are all correct.
0
0
B and D are not correct, because Cutomers table does not have attribute called StoreCity
0
0
Correct answer is D.
C is not correct Read http://msdn.microsoft.com/en-us/library/ms175064.aspx
B is not correct because of the order by clause http://msdn.microsoft.com/en-us/library/ms178543.aspx
A is not correct because you do not have a column before the NOT EXISTS
0
0
B and D are the correct answers
0
0
There is no correct answer.
A: bad syntax
B: StoreCity is not in the select statement
C: would be good if there was ALL, not ANY
D: StoreCity is not int the select statement
0
0
tomazzi has the closest answer from the given choices.
https://technet.microsoft.com/en-us/library/ms187074(v=sql.105).aspx
From the link above it mentions that “The =ANY operator is equivalent to IN”.
However “The ANY operator differs from NOT IN”,
and “ALL operator is equivalent to NOT IN”.
So option C would be correct if it used ALL operator instead of ANY.
0
0
“The ANY operator differs from NOT IN”
“ALL operator is equivalent to NOT IN”
0
0
Correct answer is C.
In the above Query, CustomerCity needs to be Sorted.. which is not happening in B and D Points.
0
0
C is wrong. 3 ANY (1,2,3,4) gives you TRUE but 3 is in collection. 3 ALL(1,2,3,4) gives you false.
0
0
(..)3 ANY(..) 3 ALL (..)
0
0
(..) 3 != ANY (..) 3 != ALL (..)
0
0
c
0
0
By the way, part of that new 200Q 70-461 dumps FYI:
https://drive.google.com/open?id=0B-ob6L_QjGLpfnJldlZxTklTaHM0akpJUzhja2pETHJOS0owMzd4eVk1UTVNQUpvdlVxVWM
Best Regards!
0
0