New Updated Microsoft MCITP 70-451 Real Exam Questions and Answers Download 31-40

Ensurepass

QUESTION 31

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?

 

A.

Use an XQuery expression along with the LET clause and the count function. Return the count in

XML format.

B.

Use an XML index. Aggregate the number of vacation days for each employee, and then return the

total count in XML format.

C.

Use the OPENXML function to convert XML data into a standard table format. Execute the Transact- SQL

count function on the vacation days, and then return the count in XML format.

D.

Use an XQuery expression to write the information from XML format to a SQL Server table.

Aggregate the number of vacation days from the tables, and then return the count in XML format.

 

Correct Answer: A

 

 

QUESTION 32

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?

 

A.

FOR XML AUTO

B.

FOR XML PATH

C.

OPENXML ( )

D.

XQUERY

 

Correct Answer: B

 

 

QUESTION 33

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?

 

A.

FOR XML PATH

B.

sp_xml_preparedocument

C.

XPATH query

D.

XSD schema

 

Correct Answer: A

 

 

QUESTION 34

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?

 

A.

.exist()

B.

.query()

C.

FOR XML PATH

D.

sp_xml_preparedocument

 

Correct Answer: A

 

 

QUESTION 35

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?

 

A.

Use a single Exists() method.

B.

Use a single XPATH statement.

C.

For each value, use the Exists() method.

D.

For each value, use an XPATH statement.

 

Correct Answer: D

 

 

QUESTION 36

You need to design a method for storing large XML-formatted data. The design must meet the following requirements:

 

– Minimize the page I/O

– Minimize the response time for data manipulation language (DML) queries

 

What should you do?

 

A.

Store the XML data by using the filestream data type.

B.

Store the XML data by using the nvarchar(max) data type.

C.

Create columns based on XML elements. Shred the XML data into the individual columns.

D.

Create columns based on Extensible Stylesheet Language Transformations (XSLT). Store the XML

data by using the XML data type.

 

Correct Answer: C

 

 

QUESTION 37

You are a database developer. You plan to design a database solution by using SQL Server 2008. A stored procedure uses the INSERT, UPDATE, and DELETE statements separately to load data into a table. You need to rewrite the stored procedure to use a single statement to load the data. What should you do?

 

A.

Write a MERGE statement by using a WHEN MATCHED clause and a WHEN NOT MATCHED

BY TARGET clause.

B.

Write a MERGE statement by using a WHEN MATCHED clause and a WHEN NOT MATCHED

BY SOURCE clause.

C.

Write a MERGE statement by using a WHEN MATCHED clause, a WHEN NOT MATCHED BY

TARGET clause, and a WHEN NOT MATCHED BY SOURCE clause.

D.

Write a MERGE statement by using a WHEN MATCHED clause and two WHEN NOT MATCHED

BY SOURCE clauses.

 

Correct Answer: C

 

 

QUESTION 38

You are a database developer. You plan to design a database solution by using SQL Server 2008. The database includes a table that contains the following product inventory information:

 

– Department

– Class

– Item

– Quantity

 

You plan to write a query that produces the sum of quantity data broken into the following groups:

 

– Department

– Department and Class

– Department and Item

– Department, Class, and Item

 

You need to write the query by using the minimum possible number of Transact-SQL statements.

What should you recommend?

 

A.

Write a single query that contains a GROUP BY clause.

B.

Write a single query that contains a GROUP BY WITH CUBE clause.

C.

Write a single query that contains a GROUP BY WITH ROLLUP clause.

D.

Write a single query that contains a GROUP BY GROUPING SETS clause.

 

Correct Answer: D

 

 

QUESTION 39

You are a database developer. You plan to design a database solution by using SQL Server 2008. The database will contain a table that has a parent-child relationship to itself. Each child might also be a parent. This might exist up to 10 levels deep. You need to retrieve all levels by using a single Transact-SQL query. What should you do?

 

A.

Write a query to return the first level, and then add a correlated subquery to get the remaining levels.

B.

Write a query to return the first level, and then use the CROSS JOIN operator to join the table back

to itself to get the remaining levels.

C.

Create a common-table expression to return the first level and then union back to itself to get the

remaining levels.

D.

Create a view that returns the first level, and then use the FULL OUTER JOIN operator to join the

table back to the view to get the remaining levels.

 

Correct Answer: C

 

 

QUESTION 40

You have a table named Table1. A sample of the data in Table1 is shown in the following table.

 

70-451-demo-5

 

There is a defined nonclustered index on the SalesOrderNumber column.

The following query executes against the table.

WHERE `SO3′ = LEFT (SalesOrderNumber,3)

You need to minimize the amount page I/O that is generated when the query runs.

What should you do?

 

A.

Use a query hint.

B.

Add a non-filtered index.

C.

Rewrite the WHERE clause to use a LIKE statement.

D.

Rewrite the WHERE clause to use a substring function on the SalesOrderNumber column.

 

Correct Answer: C

 

Instant Access to Download Latest Complete Collection of Microsoft MCITP 70-451 Real Exam

Try Microsoft MCITP 70-451 Free Demo