You are a database developer. You plan to design a database solution by using SQL Server
2008. A stored procedure in a database uses a transaction to retrieve data from a table and
produces aggregations. You must design a solution that meets the following requirements:
·Update operations cannot be performed on the retrieved data while the stored procedure is
being executed. ·Insert operations in the table can be performed while the stored procedure
is being executed. You need to ensure that the solution meets the requirements. What
isolation level should you use?

You have a database that contains two tables. Both the XACT_ABORT database option and
the IMPLICIT_TRANSACTIONS database option are set to OFF. You need to update both
tables. If an update fails on either table, neither table should be updated. What should you
do?

You use SQL Server 2008 to design a database that will hold incoming XML responses for
an EDI system. You have the following requirements: ·The data is accessible to
heterogeneous platforms. ·The database stores various types of reports from multiple
sources. ·The solution allows search by keywords. ·The database stores large amounts of
data. ·The database is scalable. You need to design the database to meet the given
requirements. What should you do?

You are a database developer. You plan to design a database solution by using SQL Server
2008. The database will contain a table that will store customer data as XML data. The data
supports an application that cannot be altered. You plan to prevent the following types of
errors in the XML data. ·NULL values in the Customer Name field ·Non-numeric values in
the Customer Telephone field. ·Invalid values in the Gender field You need to implement the
plan without modifying the application. What should you do?

You are designing a database that contains a data definition language (DDL) trigger. The
DDL trigger will provide the maximum amount of data available when any attempt is made
to change the database schema. You need to design a table to meet the following
requirements: ·Accept the EVENTDATA information that is provided by the trigger ·Support
the searching and retrieval of nodes and values ·Minimize development time Which data
type should you use?

You are a database developer. You plan to design a database solution by using SQL Server
2008. A database contains a table named Employee_Vacation. You are given an updated
list of employee vacations used. The list is in an XML formatted file. The extract of the XML
format is written in the following manner. <Company Name =”ABC Company Pvt Ltd”>
<EmployeeLeave> <Employee ID = “1” Name=”Jim Reeves” /> <Leaves> <Leave
Date=”2008-02-12″ /> <Leave Date=”2008-02-13″ /> <Leave Date=”2008-02-14″ />
</Leaves> </EmployeeLeave> You plan to import the data and update the
Employee_Vacation table. You design a query to calculate the number of vacation days
used by each employee. You need to ensure that vacation days are accurately counted for
each employee. What should you do?

Your company has the following development policy for XML data: ·The data must be
element-centric ·The data must be well-formed XML ·The data must have a root element
·The data must contain the parent table name You need to recommend guidelines for
generating well-formed XML result sets. What should you recommend?

You have a table that has five varchar columns. You are designing an application that
requires data in well-formed XML. You need to design a query statement that will produce
the data in well-formed XML. What should you use?

You plan to implement a Web-based application that will save XML data to a column in a
table. You need to design a query that ensures that before saving the XML data to the table,
the data contains valid elements. The solution must be developed by using the minimum
amount of effort. What should you include in the query?

You have a table that has an XML column named XMLOrderHeader. You need to design a
stored procedure that extracts the order header values and stores them in a table. The
solution must meet the following requirements: ·Extract many values ·Minimize the
development effort What should the solution include?