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 a list of
Customers who live in a city that does not have a ABC.com store, along with the customer’s City.
The result set must be sorted alphabetically by City name.
Which of the following Transact-SQL statements would return the required information?
A.
SELECT CustomerName, CustomerCity
FROM Customers
WHERE CustomerCity NOT EXISTS (SELECT StoreCity FROM Stores)
ORDER BY CustomerCity
B.
SELECT CustomerName, CustomerCity
FROM Customers
WHERE CustomerCity < > ALL (SELECT StoreCity FROM Stores)
ORDER BY StoreCity
C.
SELECT CustomerName, CustomerCity
FROM Customers
WHERE CustomerCity < > ANY (SELECT StoreCity FROM Stores)
ORDER BY CustomerCity
D.
SELECT CustomerName, CustomerCity
FROM Customers
WHERE CustomerCity NOT IN (SELECT StoreCity FROM Stores)
ORDER BY StoreCity
Explanation:
B, C and D.
0
0
SELECT CustomerName, CustomerCity
FROM Customers
WHERE NOT EXISTS (SELECT StoreCity FROM Stores where CustomerCity = StoreCity)
ORDER BY CustomerCity
0
0
B&D but not A&C
1
0
A is syntax error
B – ALL means that all items are different
http://msdn.microsoft.com/en-us/library/bb510741.aspx
C – Any means If even one of the items is different return true
so it would work, but I would prefer D which the obvious.
0
0
B and D are incorrect, because Customers table doesn’t have column named StoreCity
0
0
There is no correct answer
0
1
Aglee.
0
1
The answer is C. B and D are obviously invalid because they are ordering by the cities where stores exist. The object of this query is to obtain a sorted list of cities that do not have existing stores, therefore you must ORDER BY the cities of the Customer.
0
0
I agree to tomazzi there is no correct answer
A- Syntax error
B- would be correct option if sorting would be on CustomerCity. Otherwise error
C- would return all cities either stores exists or not
D- would be correct, if sorting would be on CustomerCity. Otherwise error
0
0
!
0
0
Besides, part of that new 200Q 70-461 dumps are available here:
https://drive.google.com/open?id=0B-ob6L_QjGLpfnJldlZxTklTaHM0akpJUzhja2pETHJOS0owMzd4eVk1UTVNQUpvdlVxVWM
Best Regards!
1
0