Get Prepared with fully updated Microsoft 70-451 Real Exam Questions and Accurate Answers for 70-451 Exam Dumps. Braindump2go IT experts review the 70-451 newly added qustions and suggest Correct Microsoft 70-451 Exam Questions Answers in Real Time. 100% Pass easily!
Vendor: Microsoft
Exam Code: 70-451
Exam Name: PRO: Designing Database Solutions and Data Access Using Microsoft SQL Server 2008
Keywords: 70-451 Exam Dumps,70-451 Practice Tests,70-451 Practice Exams,70-451 Exam Questions,70-451 PDF,70-451 VCE Free,70-451 Book,70-451 E-Book,70-451 Study Guide,70-451 Braindump,70-451 Prep Guide
70-451 PDF Dumps & 70-451 VCE Dumps (147q) Full Version Download: http://www.braindump2go.com/70-451.html
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.
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
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
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
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.
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.
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.
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.
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.
Answer: C
QUESTION 40
You have a table named Table1.
A sample of the data in Table1 is shown in the following table.
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.
Answer: C
Braindump2go New Released Premium 70-451 Exam Dumps Guarantee You a 100% Exam Success Or We Promise Full Money Back! Download Microsoft 70-451 Exam Dumps Full Version From Braindump2go Instantly!
http://www.braindump2go.com/70-451.html (147Q&As)