You work as a database administrator at Domain.com. The Domain.com network consists of a single Active Directory domain named Domain.com. Your duties include administrating a SQL Server 2005 database named Certkiller -DB01. At Domain.com the technician appointed in marketing department is responsible for creating a list of e-mail addresses of clients from information stored in Certkiller -DB01. The CIO wants the following information to appear on the list:-
1. It should encompass the date on which every client was last contacted.
2. The information in the result set must have column names.
3. The list should be ordered by date of last contact, with the latest contact first.
4. The CK_LastContact column should be stored as a datetime.
5. The date should be displayed as MM/DD/YYYY.
A junior technician named Amy Walsh develops the following query.
SELECT email_address AS EmailAddress,
CONVERT(nvarchar, lastcontact, 101) AS CK_LastContact FROM Contact ORDER BY CK_LastContact DESC
As the administrator you test the query and discover that the information is displayed in the following order:
EmailAddress CK_LastContact [email protected] 01/24/2003 [email protected] 06/12/2005 [email protected] 07/13/2004
You need to modify the query in order that the information is listed in the correct order and accomplish this without negatively affecting performance.
What should you do?
A.
You should change the alias on the CK_LastContact column.
B.
You should change the ORDER BY clause as follows:
ORDER BY CAST(lastcontact AS nvarchar(20)) DESC
C.
You should remove the column alias from the CK_LastContact column.
D.
You should change the ORDER BY clause as follows:
ORDER BY CONVERT(nvarchar, lastcontact, 101) DESC