Ensurepass

QUESTION 141

You administer a Microsoft SQL Server 2008 database named AdventureWorks that contains a table named Production.Product. The table contains a primary key named PK_Product_ProductID and a non-clustered index named AK_Product_ProductNumber. Both indexes have been created on a single primary partition.

 

The table has the following definition:

 

image029

 

The index has the following definition:

 

image031

 

image033

 

A.

Option A

B.

Option B

C.

Option C

D.

Option D

E.

Option E

F.

Option F

G.

Option G

H.

Option H

I.

Option I

J.

Option J

K.

Option K

L.

Option L

M.

Option M

N.

Option N

O.

Option O

P.

Option P

Q.

Option Q

 

Correct Answer: Q

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

QUESTION 142

You are the database administrator for an order management system.

 

The database has the following two schemas:

 

The application contains a product table that has the following definition:

 

image034

 

You want to load a subset of data from the product and order tables in the dbo schema to a new ProductOrderHistory table in the Reporting schema.

 

You need to ensure that the table meets the following requirements:

 

The Reporting.ProductOrderHistory columns have the following types:

 

image035

 

Which Transact-SQL query should you use?

image037

 

A.

Option A

B.

Option B

C.

Option C

D.

Option D

 

Correct Answer: A

 

 

QUESTION 143

You administer a Microsoft SQL Server 2008 database that contains tables named Sales.Customer and Sales.SalesOrder.

 

A diagram of the tables is shown in the exhibit. (Click the Exhibit button.)

 

image038

 

You need to execute a query to update the value of the Customer-Value field to HV when a customer has more than 5 orders for a total sales amount of more than 5,000 U.S. dollars.

 

Which Transact-SQL statement should you use?

 

image040

 

A.

Option A

B.

Option B

C.

Option C

D.

Option D

 

Correct Answer: B

 

 

QUESTION 144

You are a database developer for your organization.

 

You create an application that uses a Transact-SQL variable to store user input data. The database collation is case-insensitive.

 

The variable is constructed as shown in the following statement:

 

image041

 

You need to implement a keyword search that meets the following requirements:

 

Which Transact-SQL statement should you use?

 

image043

 

A.

Option A

B.

Option B

C.

Option C

D.

Option D

 

Correct Answer: B

QUESTION 145

You have a table named Subscribers. You receive an updated list of subscribers. You need to remove subscribers that are no longer on the list. Which clause should you use to remove the subscribers from the table?

 

A.

WHEN NOT MATCHED

B.

WHEN MATCHED

C.

WHEN NOT MATCHED BY SOURCE

D.

WHEN NOT MATCHED BY TARGET

 

Correct Answer: C

 

 

QUESTION 146

You are the database developer for a Microsoft SQL Server 2008 database that contains tables named order and product.

 

The tables have the following definitions:

 

image044

 

You need to write a query that will extract a valid XML result set of all ordered products. You also need to ensure that the query conforms to the following schema:

 

image046

 

Which SQL query should you use?

 

image048

 

A.

Option A

B.

Option B

C.

Option C

D.

Option D

 

Correct Answer: C

 

 

 

 

QUESTION 147

A table named Shirts includes an XML column named SizeAndColors that contains the

sizes and colors of each shirt, as shown in the following code segment.

 

image049

 

You need to return a list of shirt colors available in a specific size.

 

Which code segment should you use?

 

image051

 

A.

Option A

B.

Option B

C.

Option C

D.

Option D

 

Correct Answer: D

 

 

 

QUESTION 148

Your company uses an application that passes XML to the database server by using stored procedures. The database server has a large number of XML handles that are currently active. You determine that the XML is not being flushed from SQL Server memory. You need to identify the system stored procedure to flush the XML from memory. Which Transact-SQL statement should you use?

 

A.

DBCC DROPCLEANBUFFERS

B.

DBCC FREEPROCACHE

C.

sp_xml_preparedocument

D.

sp_xml__removedocument

 

Correct Answer: D

 

 

QUESTION 149

DRAG DROP

You are a database developer for your organization. You have an application hosted on Microsoft SQL Server 2008 R2.

 

One of the tables in the application database has the following definition:

 

image053

 

Users report that the response time of the stored procedure has decreased.

 

You need to ensure that the response time of the stored procedure is improved.

 

Which three Transact-SQL statements should you use? (To answer, move the appropriate statements from the list of statements to the answer area and arrange them in the correct order.)

 

image055

 

Correct Answer:

image057

 

 

 

QUESTION 150

You are a developer for a Microsoft SQL Server 2008 R2 database instance. You create tables named order, customer, and product as follows:

 

image058

 

You need to write a query to return all customer names and total number of orders for customers who have placed more than 10 orders. Which SQL query should you use?

 

image059

image060

 

A.

Option A

B.

Option B

C.

Option C

D.

Option D

E.

Option E

F.

Option F

G.

Option G

H.

Option H

I.

Option I

J.

Option J

 

Correct Answer: D

 

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

Try Microsoft 70-433 Free Demo

Comments are closed.