Ensurepass

QUESTION 111

You work for a company that provides marketing data to other companies.

 

You have the following Transact-SQL statement:

 

DECLARE @CustomerDemographics XML

 

SET @CustomerDemographics=N’

 

<CustomerDemographics>

 

<Customer CustomerID=”1″ Age=”21″ Education=”High School”>

 

<IsCoffeeDrinker>0</IsCoffeeDrinker>

 

</Customer>

 

<Customer CustomerID=”2″ Age=”27″ Education=”College”>

 

<IsCoffeeDrinker>1</IsCoffeeDrinker>

 

<IsFriendly>1</IsFriendly>

 

</Customer>

 

<Customer CustomerID=”3″ Age=”35″ Education=”Unknown”>

 

<IsCoffeeDrinker>1</IsCoffeeDrinker>

 

<IsFriendly>1</IsFriendly>

 

</Customer>

 

</CustomerDemographics>’

 

DECLARE @OutputAgeOfCoffeeDrinkers XML

 

SET @OutputAgeOfCoffeeDrinkers = @CustomerDemographics.query(‘

 

for $output in /child::CustomerDemographics/child::Customer[

 

( child::IsCoffeeDrinker[1] cast as xs:boolean )]

 

return

 

<CoffeeDrinkingCustomer>

 

{ $output/attribute::Age \}

 

</CoffeeDrinkingCustomer>’)

 

SELECT @OutputAgeOfCoffeeDrinkers

 

You need to determine the result of the query.

 

What result should you expect?

 

A.

<CoffeeDrinkingCustomer Age=”27″ />

<CoffeeDrinkingCustomer Age=”35″ />

B.

<CoffeeDrinkingCustomer Age=”21″ />

C.

<CustomerDemographics>

<Customer>

<CoffeeDrinkingCustomer Age=”21″ />

</Customer>

</CustomerDemographics>

D.

<CustomerDemographics>

<Customer>

<CoffeeDrinkingCustomer Age=”27″ />

</Customer>

<Customer>

<CoffeeDrinkingCustomer Age=”35″ />

</Customer>

</CustomerDemographics>

 

Correct Answer: A

 

 

QUESTION 112

You have a table named Stores that has an XML column named OpenHours.

 

This column contains the opening and closing times.

 

<hours dayofWeek=”Monday” open=”8:00″ closed=”18:00″ />

 

<hours dayofWeek=”Tuesday” open=”8:00″ closed=”18:00″ />

 

 

<hours dayofWeek=”Saturday” open=”8:00″ closed=”18:00″ />

 

You need to write a query that returns a list of stores and their opening time for a specified day.

 

Which code segment should you use?

 

A.

DECLARE @Day VARCHAR(10) = ‘Tuesday’

SELECT

StoreName,

OpenHours.value(‘/hours[1]/@open’,’time’)

FROM Stores

WHERE OpenHours.value(‘/hours[1]/@dayofWeek’,’varchar(20)’) = @Day

B.

DECLARE @Day VARCHAR(10) = ‘Tuesday’

SELECT

StoreName,

OpenHours.value(‘/hours[1]/@open’,’time’)

FROM Stores

WHERE OpenHours.exist(‘/hours[@dayofWeek=sql:variable(“@Day”)]’) = 1

C.

DECLARE @Day VARCHAR(10) = ‘Tuesday’

SELECT

Storename,

OpenHours.query(‘data(/hours[@dayofWeek=sql:variable(“@Day”)]/@open)’)

FROM Stores

D.

DECLARE @Day VARCHAR(10) = ‘Tuesday’

SELECT

StoreName,

OpenHours.value(‘/hours[1][@dayofWeek=sql:variable(“@Day”)]/@open’,’time’)

FROM Stores

 

Correct Answer: C

Explanation:

CREATE TABLE Stores(

StoreName VARCHAR(10)NOT NULL,

OpenHours [xml] NULL,

CONSTRAINT [PK_Stores] PRIMARY KEY CLUSTERED (StoreName))

GO

INSERT INTO Stores (StoreName, OpenHours)

VALUES

(

‘Store1’,

‘<hours dayofWeek= “Wednesday” open =”8:00 AM” closed=”8:00 PM”/>

<hours dayofWeek= “Tuesday” open =”9:00 AM” closed=”8:00 PM”/>

<hours dayofWeek= “Friday” open =”8:00 AM” closed=”8:00 PM”/>’),

(‘Store2’,

‘<hours dayofWeek= “Monday” open =”8:00 AM” closed=”8:00 PM”/>

<hours dayofWeek= “Tuesday” open =”8:00 AM” closed=”8:00 PM”/>

<hours dayofWeek= “Saturday” open =”8:00 AM” closed=”8:00 PM”/>’)

DECLARE @Day VARCHAR(10) = ‘Tuesday’

SELECT Storename, OpenHours.query(‘data(/hours[@dayofWeek=sql:variable(“@Day”)]/ @open)’)

FROM Stores

GO

 

QUESTION 113

You have the following XML document that contains Product information.

 

DECLARE @prodList xml =’

 

<ProductList xmlns=”urn:Wide_World_Importers/schemas/Products”>

 

<Product Name=”Product1″ Category=”Food” Price=”12.3″ />

 

<Product Name=”Product2″ Category=”Drink” Price=”1.2″ />

 

<Product Name=”Product3″ Category=”Food” Price=”5.1″ />

 

 

</ProductList>’;

 

You need to return a list of products that contains the Product Name, Category, and Price of each product.

 

Which query should you use?

 

A.

SELECT prod.value(‘.[1]/@Name’,’varchar(100)’),

prod.value(‘.[1]/@Category’,’varchar(20)’),

prod.value(‘.[1]/@Price’,’money’)

FROM @prodList.nodes(‘/ProductList/Product’) ProdList(prod);

B.

SELECT prod.value(‘@Name’,’varchar(100)’),

prod.value(‘@Category’,’varchar(20)’),

prod.value(‘@Price’,’money’)

FROM @prodList.nodes(‘/ProductList/Product’) ProdList(prod);

C.

WITH XMLNAMESPACES(DEFAULT ‘urn;Wide_World_Importers/schemas/Products’ as o)

SELECT prod.value(‘Name[1]’,’varchar(100)’),

prod.value(‘Category[1]’,’varchar(20)’),

prod.value(‘Price[1]’,’money’)

FROM @prodList.nodes(‘/o:ProductList/o:Product’) ProdList(prod);

D.

WITH XMLNAMESPACES(DEFAULT ‘urn:Wide_World_Importers/schemas/Products’)

SELECT prod.value(‘./@Name’,’varchar(100)’),

prod.value(‘./@Category’,’varchar(20)’),

prod.value(‘./@Price’,’money’)

FROM @prodList.nodes(‘/ProductList/Product’) ProdList(prod);

 

Correct Answer: D

 

 

QUESTION 114

You have a table named Products.Product. The table has columns ProductID, Name, Size, and Category. You have a variable named @XML with following XML value:

 

<Root>

 

<Category Name=”Socks” />

 

<Category Name=”Pants” />

 

<Category Name=”Shirts” />

 

</Root>

 

You are tasked to write a query that lists the products in Products.Product that match the categories listed in the XML document.

 

You need to write a query to accomplish the task.

 

Which query should you write?

 

A.

SELECT p.ProductID, p.Name, p.Size, p.Category

FROM Production.Product p

CROSS APPLY @XML.nodes(‘//Category’) as x(s)

B.

SELECT p.ProductID, p.Name, p.Size, p.Category

FROM Production.Product p

OUTER APPLY @XML.nodes(‘//Category’) as x(s)

C.

WITH XMLTable

AS

(

SELECT s.value(‘@Name’,’varchar(20)’) as Category

FROM @XML.nodes(‘//Category’) as x(s)

)

SELECT p.ProductID, p.Name, p.Size, p.Category

FROM Production.Product p

INNER JOIN XMLTable x ON p.Category = x.Category

D.

WITH XMLTable

AS

(

SELECT s.value(‘@Category’,’varchar(20)’) as Category

FROM @XML.nodes(‘//Category’) as x(s)

)

SELECT p.ProductID, p.Name, p.Size, p.Category

FROM Production.Product p

INNER JOIN XMLTable x ON p.Category = x.Category

 

Correct Answer: C

 

 

QUESTION 115

Your company exchanges information with other companies by using XML and Web services. Your manager asks you to remove a schema collection that is no longer used. Before dropping the schema, you should confirm that it is no longer in use. You need to use a catalog view to determine if the schema collection is being used. Which catalog view should you use?

 

A.

sys.xml_schema_components

B.

sys.xml_schema_namespaces

C.

sys.xml_schema_collections

D.

sys.column_xml_schema_collection_usages

 

Correct Answer: D

Explanation:

sys.column_xml_schema_collection_usages returns a row for each column that is validated by an XML schema.

 

 

QUESTION 116

You have an XML schema that you must use to validate XML data in your database. You need to store this XML schema. Which code segment should you use?

 

A.

CREATE SCHEMA CustomerSchema

B.

CREATE DEFAULT CustomerSchema AS ‘XML’

C.

CREATE PRIMARY XML INDEX CustomerSchema

D.

CREATE XML SCHEMA COLLECTION CustomerSchema

 

Correct Answer: D

 

 

QUESTION 117

You have a table named Customers that has an XML column named CustomerData. There are currently no indexes on the table.

 

You use the following WHERE clause in a query:

 

WHERE CustomerData.exist (‘/CustomerDemographic/@Age[.>=”21″]’) = 1

 

You need to create indexes for the query.

 

Which Transact-SQL statements should you use?

 

A.

CREATE CLUSTERED INDEX CL_IDX_Customer

ON Customers(CustomerID);

CREATE PRIMARY XML INDEX PXML_IDX_Customer

ON Customers(CustomerData);

CREATE XML INDEX SXML_IDX_Customer ON Customer(CustomerData)

USING XML INDEX PXML_IDX_Customer

FOR PATH;

B.

CREATE PRIMARY XML INDEX PXML_IDX_Customer

ON Customers(CustomerData);

CREATE XML INDEX SXML_IDX_Customer ON Customer(CustomerData)

USING XML INDEX PXML_IDX_Customer

FOR VALUE;

C.

CREATE PRIMARY XML INDEX PXML_IDX_Customer

ON Customers(CustomerData);

CREATE XML INDEX SXML_IDX_Customer ON Customer(CustomerData)

USING XML INDEX PXML_IDX_Customer

FOR PATH;

D.

CREATE CLUSTERED INDEX CL_IDX_Customer

ON Customers(CustomerID);

CREATE PRIMARY XML INDEX PXML_IDX_Customer

ON Customers(CustomerData);

CREATE XML INDEX SXML_IDX_Customer_Property ON Customer(CustomerData)

USING XML INDEX PXML_IDX_Customer

FOR VALUE;

 

Correct Answer: A

Explanation:

The primary XML index indexes all tags, values, and paths within the XML instances in an XML column. To create a primary XML index, the table in which the XML column occurs must have a clustered index on the primary key of the table. SQL Server uses this primary key to correlate rows in the primary XML index with rows in the table that contains the XML column.

 

The first index on the xml type column must be the primary XML index. Using the primary XML index, the following types of secondary indexes are supported: PATH, VALUE, and PROPERTY.

 

Following are some guidelines for creating one or more secondary indexes:

 

If your workload uses path expressions significantly on XML columns, the PATH secondary XML index is likely to speed up your workload. The most common case is the use of the exist() method on XML columns in the WHERE clause of Transact-SQL.

 

If your workload retrieves multiple values from individual XML instances by using path expressions, clustering paths within each XML instance in the PROPERTY index may be helpful. This scenario typically occurs in a property bag scenario when properties of an object are fetched and its primary key value is known.

 

If your workload involves querying for values within XML instances without knowing the element or attribute names that contain those values, you may want to create the VALUE index. This typically occurs with descendant axes lookups, such as //author[last- name=”Howard”], where <author> elements can occur at any level of the hierarchy. It also occurs in wildcard queries, such as /book [@* = “novel”], where the query looks for <book> elements that have some attribute having the value “novel”.

 

 

QUESTION 118

You need to capture the execution plan for a query. Which statement should you use?

 

A.

SET FORCEPLAN ON;

B.

SET SHOWPLAN_XML ON;

C.

SET STATISTICS IO ON;

D.

SET STATISTICS TIME ON;

 

Correct Answer: B

 

 

QUESTION 119

You are troubleshooting query performance on SQL Server 2008. You are tasked to create an estimated execution plan by using Transact-SQL. You should be able to view the plan graphically in SQL Server Management Studio. You need to ensure that the execution plan can be saved as a .sqlplan file. Which Transact-SQL setting should you use?

 

A.

SET SHOWPLAN_ALL ON;

B.

SET SHOWPLAN_XML ON;

C.

SET STATISTICS XML ON;

D.

SET STATISTICS PROFILE ON;

 

Correct Answer: B

Explanation:

SET SHOWPLAN_XML ON

This statement causes SQL Server not to execute Transact-SQL statements. Instead, Microsoft SQL Server returns execution plan information about how the statements are going to be executed in a well-formed XML document.

 

SET SHOWPLAN_TEXT ON

After this SET statement is executed, SQL Server returns the execution plan information for each query in text. The Transact-SQL statements or batches are not executed.

 

SET SHOWPLAN_ALL ON

This statement is similar to SET SHOWPLAN_TEXT, except that the output is in a format more verbose than that of SHOWPLAN_TEXT.

 

SET STATISTICS XML ON

Returns execution information for each statement after the statement executes in addition to the regular result set the statement returns. The output is a set of well-formed XML documents. SET STATISTICS XML ON produces an XML output document for each statement that executes. The difference between SET SHOWPLAN_XML ON and SET STATISTICS XML ON is that the second SET option executes the Transact-SQL statement or batch. SET STATISTICS XML ON output also includes information about the actual number of rows processed by various operators and the actual number of executes of the operators.

 

SET STATISTICS PROFILE ON

Returns the execution information for each statement after the statement executes in addition to the regular result set the statement returns. Both SET statement options provide output in text. The difference between SET SHOWPLAN_ALL ON and SET STATISTICS PROFILE ON is that the second SET option executes the Transact-SQL statement or batch. SET STATISTICS PROFILE ON output also includes information about the actual number of rows processed by various operators and the actual number of executes of the operators.

 

 

QUESTION 120

You are troubleshooting query performance on SQL Server 2008. You are tasked to capture a graphical execution plan. You need to save the plan to a file that can be used by SQL Server Management Studio to display the graphical execution plan.

Which file extension should you use?

 

A.

.gif

B.

.xml

C.

.psql

D.

.sqlplan

 

Correct Answer: D

 

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

Try Microsoft 70-433 Free Demo