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 mia@contoso.com 01/24/2003 amy@contoso.com 06/12/2005 kara@contoso.com 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?

You are employed as a database administrator at Domain.com. The Domain.com network consists of a single Active Directory domain named Domain.com. You have been informed by the CIO that the msdb database on a SQL Server 2005 computer is corrupted. This database should therefore be restored. A Domain.com employee named Dean Austin is employed as a junior technician in the Finance department. You inform him that the databases should be backed up daily. The backup files of the database are written to a network share. However, the file names do not clearly specify which databases are in every file. Dean Austin needs to locate the right backup file as quick as possible. You inform him that the first file in the list is named DB_Backup.bak.
What Transact-SQL statement should Dean Austin use?

You work as the database administrator at Domain.com. The Domain.com network consists of a single Active Directory domain named Domain.com. All servers on the Domain.com network run on Windows Server2003 and all client computers run Windows XP Professional. The Domain.com network contains two SQL Server 2005 database servers that are configured in a cluster with two nodes named NodeA and NodeB.
NodeA contains two physical hard disk drives, one of which hosts the Quorum resource and contains a single partition while the other contains two logical partitions. NodeB also has two physical hard disk drives each of which contain a single logical partition. None of the hard disks in the cluster have any unallocated space left. The SQL Server 2005 cluster currently hosts four database instances. Domain.com wants to implement another SQL Server 2005 instance for a database named Manufacturing_DB. Manufacturing_DB will store data from the Domain.com Manufacturing department. You have been instructed to create the SQL Server 2005 instance for the Manufacturing_DB database.
What should you do?

Domain.com has employed you as a database administrator. The Domain.com network consists of a single Active Directory domain named Domain.com. Your duties at Domain.com are the administrating of the SQL Server 2005 computer named Certkiller -DB02.
You have received several reports from clients stating that the time required to run their queries on Certkiller -DB02 is gradually increasing. You have been informed by the CIO to decide on which computer to run SQL Server Profiler as well as where to store the trace.
You are required to investigate this matter by using the SQL Server Profiler to trace all client queries. You need to minimize the impact of this operation on Certkiller -DB02 performance.
What should you do?

You are employed as a database administrator at Domain.com. The Domain.com network consists of a single Active Directory domain named Domain.com. You have received instruction from the network CIO to create a Web-based application to manage data aggregation for reports. The Application that you create will connect to a SQL 2005 database named CK_Data and another page of your application has controls which are used to execute stored procedures in a database named CK_Sales.
Domain.com has an existing Service Broker connection between the CK_Data and CK_Sales databases. You have received additional instruction to add two new message types to the existing service. In each database, you create message types named CKReport and CKResult. You are required to add the two new message types to the existing service.
What should you do first?

You are employed as a database administrator at Domain.com. The Domain.com network consists of a single Active Directory domain named Domain.com. The Domain.com network uses a SQL Server 2005 database named CK_DATA, which is located on a server named Certkiller -DB01.
Domain.com also contains another SQL Server 2005 database server named Certkiller -DB02 on which a recent copy of CK_DATA exists. During routine monitoring you discover a high number of full scans on Certkiller -DB01. You come to a conclusion that extra indexes in CK_DATA are required. You have received instruction from the CIO to analyze the workload file by using DATA. You need to accomplish this without compromising the performance on Certkiller -DB01 during the analysis. You need to guarantee availability of any recommendations suggested by the DTA during implementation.
What should you do?

You work as the database administrator at Domain.com. The Domain.com network consists of a single Active Directory domain named Domain.com. The network contains a database server named Certkiller -DB01 that hosts a database named CK_Books. The CK_Books database has a table named SampleChapters that is used to store sample chapters from books published by Domain.com. The schema for the SampleChapters table is shown in the following exhibit:

The text for the sample chapters are stored in a column named SampleText. The SampleText column is defined as an xml data type column. The XML schema for the SampleText column is shown below:
<SampleChapter>
<BookTitle>title of book</BookTitle>
<Author>author of book</Author>
<ChapterText>
<ChapterTitle>title of chapter</ ChapterTitle >
<Intro>introduction or synopsis</Intro>
<Topic Title="topic title">topic text</Topic>
<Topic Title="next topic title">next topic text</Topic> </ChapterText>
</SampleChapter>
You need to create a stored procedure that will return a list of sample chapters from the Samples table. The returned data must include the book title and the sample chapter title but must not return any XML tags.
exhibit What stored procedure you create?