You work as a database developer at Domain.com. The Domain.com network consists of a single Active Directory domain named Domain.com that manufactures bicycles. At present you are creating a Web application that will retrieve data from a database named Certkiller -DB01. Currently the application connects to a database server that is configured to run SQL Server 2005. One of the functions of this application will permit shift managers to see the amount of bicycles that was manufactured during the current shift. A Domain.com employee named Kara Lang creates a stored procedure named usp_GetPersonData in the dbo schema. You create an HTTP endpoint that exposes a Web method named PersonData. This Web method permits client applications to access the stored procedure.
Kara Lang makes use of the following statement to create the endpoint:- CREATE ENDPOINT SQLEP_AWPersons
AS HTTP
(PATH = ‘/AWpersons’,
AUTHENTICATION = (INTEGRATED)
PORTS = (CLEAR),
SITE = ‘SQL1’)
FOR SOAP
(WEBMETHOD ”
(NAME=”),
BATCHES = DISABLED,
WSDL = DEFAULT,
DATABASE = ‘ Certkiller ‘,
NAMESPACE = ‘http://Adventure-Works/Persons’)
You need to identify the correct WEBMETHOD and NAME to create the endpoint.
What command should you 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 Windows Server 2003 and all client computers run Windows XP Professional.
The Domain.com network contains a SQL Server 2005 database server named Certkiller -DB01. The database instance running on Certkiller -DB01 uses the Active Directory account named CK_db01. You need to enable one of the databases to accept Simple Object Access protocol (SOAP) requests. You plan to accomplish this by creating a Hypertext Transfer Protocol (HTTP) endpoint that uses Kerberos authentication. You need to ensure that the Service Principal Name (SPN) is automatically associated with the database instance running on Certkiller -DB01.
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. All servers on the Domain.com network run Windows Server 2003 and all client computers run Windows XP Professional. The Domain.com network contains a SQL Server 2005 database server named Certkiller -DB01 that hosts a database named CK_Projects. The CK_Projects database contains a table named Documents that contains an XML column named DocLocation. Domain.com users complain that queries against the DocLocation column are processed very slowly. You want to improve the performance of queries that are run against the DocLocation column by creating an index on the column.
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 CIO to create an online catalog application that will display the product information on the Domain.com Web site. The product information is stored in a SQL Server 2005 database server named Certkiller -DB01. The CIO informs you that the data is stored as relational data. However, the data should be passed to the application as an XML document by using FOR XML.
A Domain.com employee named Clive Wilson works in the Research and Development department. You instruct him, to test the application. Clive Wilson detects that not all the items matching the query appear in the XML document. He determines that only the products that have values for all elements in the schema appear in the XML document. You instruct him to modify the Transact-SQL statement in order that all products matching the query appear in the XML document.
What should Clive Wilson do?

You work as the database administrator at Domain.com. The Domain.com network consists of a single Active Directory domain named Domain.com. Domain.com has its headquarters in Washington and branch offices in Detroit and Delaware. All servers on the Domain.com network run Windows Server 2003 and all client computers run Windows XP Professional.
The Domain.com network contains a SQL Server 2005 database server named Certkiller -DB01 that is located at headquarters. Certkiller -DB01 hosts a database named CK_Sales that stores sales data for the entire company. While Certkiller -DB01 is offline for maintenance purposes, all sales data are collected in XML documents. Once Certkiller -DB01 is back online, you must add the data from the XML documents to the CK_Sales database.
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. Your duties include administrating a SQL Server 2005 database server named Certkiller -DB01.
A Domain.com user named Ally Wagner is a member of the Research and Development department. You instruct her to create a stored procedure that will delete data from the Contact table in a Certkiller -DB01. The stored procedure encompasses the subsequent Transact-SQL statement to handle any errors that occur.
BEGIN TRY
BEGIN TRANSACTION
DELETE FROM CK_Sales.Products
WHERE ProductID = @ProductID
COMMIT TRANSACTION
END TRY
BEGIN CATCH
DECLARE @ErrorMessage nvarchar(2000)
DECLARE @ErrorSeverity int
DECLARE @ErrorState int
SELECT @ErrorMessage = ERROR_MESSAGE(),
@ErrorSeverity = ERROR_SEVERITY(),
@ErrorState = ERROR_STATE()
RAISERROR (@ErrorMessage, @ErrorSeverity, @ErrorState) END CATCH
Ally Wagner tests the stored procedure and detects that it leaves open transactions. You inform her to modify the stored procedure in order that it properly handles the open transactions.
What should she do?

You work as the database administrator at an investment company named Domain.com. Domain.com has its headquarters in New York and branch offices in Boston, Atlanta, Miami, and New Orleans. The Domain.com network contains a SQL Server 2005 database server named Certkiller -DB01 that is located at headquarters. Certkiller -DB01 contains a large database named CK_Investments. The CK_Investments database stores investment data for each office. You need to implement a parameterized stored procedure that will decrement the AvailableInvestments table when a real estate investment is secured. The stored procedure must accept the OfficeID, InvestmentTypeID, StartDate and EndDate as input parameters.
What stored procedure should you use?

You are employed as a database administrator at Domain.com. The Domain.com network consists of a single Active Directory domain named Domain.com. Domain.com has its headquarters in London and a branch office in Paris. You are based at headquarters. Your duties include administrating a SQL Server 2005 database server named Certkiller -DB01
You have been informed by the CIO that the Research and Development department wants a report that evaluates the customer activity in the previous quarter between the office in London and the branch office in Paris. Domain.com wants the information to be sorted by surname and first name. A Domain.com user named Amy Walsh is a junior technician in the IT department. You inform her to restore a recent backup of the Paris database onto your server. Amy Walsh writes the queries to build the report, ordering the information by the LastName and FirstName columns. Amy Walsh reviews the information and detects that the client lists from the Paris database is sorted different to the one in the headquarters. You receive a notification from the Research and Development department that they require the revised data within 15 minutes for a presentation. Amy Walsh needs to implement the fastest possible solution that will guarantee that the information from both databases is sorted identically.
What should Amy Walsh 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. Your duties include administrating a SQL Server 2005 database server named Certkiller -DB01.
Your institute is well known for the sale of their textbooks. You work for a company that sells books. You are creating a report for a SQL Server 2005 database. This report will indicate the all the sales representatives and their total sales for the present month. A Domain.com user named Dean Austin is a junior technician in the Sales department. You inform him that the report should only include those sales representatives who met their sales quota for the present month. The monthly sales quota for Domain.com is $2,000. Domain.com makes use of date parameters that are passed in variables named @StartDate and @EndDate. You instruct Dean Austin to create a report that will meet those requirements.
Which SQL query should Dean Austin use?

You are employed as a database administrator at Domain.com. The Domain.com network consists of a single Active Directory domain named Domain.com. Domain.com makes use of multiple servers in a distributed environment. The Domain.com network contains two SQL Server 2005 database server named Certkiller -DB01 and Certkiller -DB02. Both Certkiller -DB01 and Certkiller -DB02 makes use of SQL Server Authentication and make use of different logins. A Domain.com employee named Kara Lang works in the Research and Development department. You instruct her to write a distributed query that joins the data on Certkiller -DB01 with the data on Certkiller -DB02.
What should she do?