Ensurepass

QUESTION 151

You are a developer for a Microsoft SQL Server 2008 R2 database instance used to support a customer service application.

 

You create tables named complaint, customer, and product as follows:

 

image061

 

You need to write a query to sum the monthly sales of each product.

 

Which SQL query should you use?

 

image062

image063

 

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: I

 

 

QUESTION 152

You are a developer for a Microsoft SQL Server 2008 R2 database instance.

 

You create tables named order, customer, and product as follows:

 

image064

 

You need to write a query to sum the sales made to each customer by the following entries:

 

Which SQL query should you use?

 

image065

image066

 

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: A

 

 

QUESTION 153

DRAG DROP

You administer a Microsoft SQL Server 2008 database that contains a table named Sales.SalesOrderHeader. The Sales.SalesOrderHeader table has the following definition:

 

image067

 

You plan to perform the following tasks:

 

You need to ensure that the following requirements are met:

 

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.)

 

image068

 

Correct Answer:

image069

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

QUESTION 154

DRAG DROP

You are a developer for a Microsoft SQL Server 2008 R2 database instance.

 

You want to add functionality to an existing application that uses the Microsoft SQL Server Profiler tool to capture trace information.

 

You need to ensure that the following requirements are met:

 

Which three actions should you perform in sequence? (To answer, move the appropriate actions from the list of actions to the answer area and arrange them in the correct order.)

 

image071

 

Correct Answer:

image073

 

 

 

 

 

 

 

 

 

QUESTION 155

DRAG DROP

You administer a Microsoft SQL Server 2008 R2 database instance named AdventureWorks.

 

A user who has the db_datareader permissions on the AdventureWorks database wants to view the estimated execution plan XML output document for the following query:

 

SELECT * FROM Sales.SalesOrderHeader

 

WHERE OnlineOrderFlag = 1 AND SubTotal > 500

 

You need to ensure that the user can view the document.

 

Which two actions should you perform in sequence? (To answer, move the appropriate

actions from the list of actions to the answer area and arrange them in the correct order.)

 

image075

 

Correct Answer:

image077

QUESTION 156

You administer a Microsoft SQL Server 2008 database that contains a stored procedure named dbo.SalesOrderDetails.

 

The stored procedure has following definition:

 

image078

 

Parameter values passed to the stored procedure largely vary.

 

You discover that the stored procedure executes quickly for some parameters but slowly for other parameters.

 

You need to ensure that the query plan generated is optimized to provide the most consistent execution times for any set of parameters passed to the stored procedure.

 

A.

OPTION (NOLCCK)

B.

OPTION (KEEP PLAN)

C.

OPTION (ROBUST PLAN)

D.

OPTION (RECOMPILE)

 

Correct Answer: C

 

 

 

 

 

 

 

 

 

QUESTION 157

You are working with a SQL Server 2008 instance that is configured to use the Icelandic_CS_AS collation. You create a database by using code that includes the following statements.

 

image080

 

You need to identify the collation that will be assigned to #AppTempTable.

 

Which collation will be assigned?

 

A.

Japanese_CS_AS

B.

Icelandic_CS_AS

C.

The collation selected by the Windows system locale of the server

D.

No collation

 

Correct Answer: B

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

QUESTION 158

A table named Locations contains 5000 locations and the number of sales made at each location.

You need to display the top 5 percent of locations by sales made. Which Transact-SQL code segment should you use?

 

image081

 

A.

Option A

B.

Option B

C.

Option C

D.

Option D

 

Correct Answer: A

 

 

 

 

 

 

 

 

 

 

 

 

 

 

QUESTION 159

DRAG DROP

You administer a Microsoft SQL Server 2008R2 database that hosts a customer relationship management (CRM) application.

 

The application supports the following two types of customers as shown in the exhibit.

(Click the Exhibit button.)

 

image082

 

You need to generate a list of residential customers who live outside North America.

 

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.)

image084

Correct Answer:

image086

 

QUESTION 160

You are the database developer for a customer service application.

 

The database has the following table and view.

 

image087

 

You need to verify that the entire code is functional.

 

Which Transact-SQL statements will fail?

 

image088

 

A.

Option A

B.

Option B

C.

Option C

D.

Option D

 

Correct Answer: C

 

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

Try Microsoft 70-433 Free Demo

Comments are closed.