REAL QUESTIONS SUBMIT MATERIAL ADVERTISE

Microsoft

Cisco

Oracle

Citrix

CIW

CompTia

CWNA

Apple

Adobe

Sun

HP

Legato

Exin

Filemaker

Brocade

Ericsson

TIA

Veritas

ISEB

SCP

IISFA

ISM

OMG

Apc

Mile2

Foundry

Huawei

McData

Symantec

TeraData

RedHat

 

 
 
Click on name of dumper to view the dump
 
Franklin
 
 

Braindumps of 70-442

Designing and Optimizing Data Access by Using

Microsoft SQL Server 2005

 

Exam Questions, Answers, Braindumps (70-442)

Thanx to www.exams.ws  for providing helpful material 

 

Question: 1

You are a database developer for Company.com In your database, the Categories table is designed as shown in the following display.

CategoryID (PK) Name (UQ) ParentCategoryID (FK)

int nvarchar(50) int

You need to write a query that retrieves all product categories that exist either directly or indirectly under category 7. Which query should you use?

A. SELECT * FROM Categories AS c2 WHERE c2.CategoryID IN ( SELECT CategoryID FROM
Categories WHERE CategoryID = 7 EXCEPT SELECT cs.CategoryID FROM Categories AS cs WHERE ParentCategoryID = 7)

B. SELECT * FROM Categories AS c2 WHERE c2.CategoryID IN ( SELECT CategoryID FROM Categories WHERE ParentCategoryID = 7 UNION ALL SELECT cs.CategoryID FROM Categories AS cs INNER JOIN Categories AS c ON c.ParentCategoryID = cs.CategoryID)

C. WITH c AS ( SELECT CategoryID FROM Categories WHERE ParentCategoryID = 7 UNION ALL SELECT cs.CategoryID FROM Categories AS cs INNER JOIN c ON c.ParentCategoryID = cs.CategoryID)SELECT * FROM Categories AS c2 WHERE c2.CategoryID IN (SELECT

c.CategoryID FROM c) D. WITH c AS ( SELECT CategoryID FROM Categories WHERE CategoryID = 7 UNION ALL SELECT cs.CategoryID FROM Categories AS cs INNER JOIN c ON c.ParentCategoryID = cs.CategoryID)SELECT * FROM Categories AS c2 WHERE c2.CategoryID IN (SELECT c.CategoryID FROM c)

Answer: C

Question: 2

You are a database developer for Company.com You are creating a parameterized stored procedure that will query the data and return rows that include any verb tense of the verbs supplied as input to the stored procedure. The query should also return rows that contain both plural and singular forms of nouns. The data is included in several varchar(max) and varbinary(max)columns. You need to create the stored procedure to fulfill these requirements. Which two actions should you perform? (Each correct answer presents part of the solution. Choose two.)

A. Create a nonclustered index on the appropriate column or columns.

B. Create a full-text index on the appropriate column or columns.

C. Use the LIKE operator.

D. Use the CONTAINS predicate.

Answer: A, D

Question: 3

You are a database developer for Company.com An XML document is stored in the XML variable @Article. The XML document in the variable can contain hundreds of article elements. The XML document is shown in the following segment.

<articles>

<article>

<title>...</title>

<paragraph>...</paragraph>

<image filename="...">

<description>...</description>

</image>

<paragraph>...</paragraph>

<image filename="...">

<description>...</description>

</image>

<paragraph>...</paragraph>

<paragraph>...</paragraph>

</article>

...

</articles>

You need to design a query that will return the filename and description for all images from the XML document in the following tabular format.

Filename Description

… …

Which query should you use?

A. SELECT@Article.value('(/articles/article/image)[1]/@filename', 'NVARCHAR(50)') AS
Filename,@Article.value('(/articles/article/image/description)[1]', 'NVARCHAR(max)') AS
Description

B. SELECT col.query('<filename>{@filename}</filename>') AS Filename
,col.query('description[1]') AS DescriptionFROM @Article.nodes('/articles/article/image') AS x
(col) ;

C. SELECT col.value('@filename', 'NVARCHAR(50)') AS Filename ,col.value('description[1]',
'NVARCHAR(max)') AS DescriptionFROM @Article.nodes('/articles/article/image') AS x (col) ;

D. SELECT col.value('(articles/article/image)[1]/@filename', 'NVARCHAR(50)') AS Filename
,col.value('articles/article/image/description[1]', 'NVARCHAR(max)') AS DescriptionFROM
@Article.nodes('/') AS x (col);

Answer: C

Question: 4

You are a database developer for Company.com You are updating a stored procedure that declares and uses a dynamic cursor. The stored procedure originally needed to return the result set and allow for updates to the result set. The requirements have changed, and now the data will be read-only. Therefore, data changes should not be allowed to be made through the cursor. The only fetch command used in the stored procedure is FETCH NEXT. You need to rewrite the cursor definition to use resources as efficiently as possible and to provide the best performance, while fulfilling the new requirements. What should you do?

A. Change the cursor DECLARE statement to use only the LOCAL, FORWARD_ONLY, and OPTIMISTIC options.

B. Change the cursor DECLARE statement to use only the LOCAL and FAST_FORWARD options.

C. Change the cursor DECLARE statement to use only the LOCAL and FORWARD_ONLY options.

D. Add the SCROLL_LOCKS option to the current DECLARE statement with the LOCAL and DYNAMIC options.

Answer: B

Question: 5

You are a database developer for Company.com You are creating a stored procedure that will use a Transact-SQL cursor to share the result set with other statements in the stored procedure. This cursor must have a fixed membership where the order of the rows does not change. The data in the rows that are retrieved through the cursor should be updatable. These updates should be viewable after they are made. While implementing this cursor, you need to minimize memory usage regardless of the resulting effect on the speed of the cursor. You need to create the stored procedure to fulfill these requirements. Which option should you use in the DECLARE CURSOR statement?

A. DYNAMIC

B. KEYSET

C. FAST_FORWARD

D. STATIC

Answer: B

Question: 6

You are a database developer for Company.com A procedure exists that saves an order item to the OrderItems table. If the item does not exist, an insert should be performed. If the item exists, an update should be performed. The OrderItems table is designed as shown in the following display.

CategoryID (PK, EK) ProductID (PK,FK) Quantity

Int nvarchar(50) int

You need to develop a routine that uses a minimum amount of resources. Which routine should you use?

A. BEGIN TRY UPDATE OrderItems SET Quantity = @Quantity WHERE OrderID = @OrderID AND ProductID = @ProductID;END TRYBEGIN CATCH INSERT OrderItems (OrderID, ProductID, Quantity) VALUES (@OrderID, @ProductID, @Quantity);END CATCH

B. IF EXISTS (SELECT * FROM OrderItems WHERE OrderID = @OrderID AND ProductID = @ProductID) UPDATE OrderItems SET Quantity = @Quantity WHERE OrderID = @OrderID AND ProductID = @ProductID; ELSE INSERT OrderItems (OrderID, ProductID, Quantity) VALUES (@OrderID, @ProductID, @Quantity);

C. IF NOT EXISTS (SELECT * FROM OrderItems WHERE OrderID = @OrderID AND ProductID = @ProductID) INSERT OrderItems (OrderID, ProductID, Quantity) VALUES (@OrderID, @ProductID, @Quantity);ELSE UPDATE OrderItems SET Quantity = @Quantity WHERE OrderID = @OrderID AND ProductID = @ProductID;

D. UPDATE OrderItems SET Quantity = @QuantityWHERE OrderID = @OrderID AND ProductID = @ProductID;IF(@@ROWCOUNT = 0) INSERT OrderItems (OrderID, ProductID, Quantity) VALUES (@OrderID, @ProductID, @Quantity);

Answer: D

Question: 7

You are a database developer for Company.com You are writing a query that will search a resume text column for specified skills. The results must include both synonyms and exact matches. You need to write the query to fulfill the requirements. Which Transact-SQL function should you use?

A. CHARINDEX

B. SOUNDEX

C. PATINDEX

D. CONTAINS

Answer: D

Question: 8

You are a database developer for Company.com You use the following Transact-SQL code. SELECT e.Resume,j.EmployeeID FROM JobCandidate j, Employee e WHERE j.EmployeeID =* e.EmployeeID

You need to rewrite this code to be ANSI compliant. How should you rewrite the code?

A. SELECT e.Resume, j.EmployeeID FROM JobCandidate AS j CROSS JOIN Employee AS e

B. SELECT e.Resume, j.EmployeeID FROM JobCandidate AS j LEFT OUTER JOIN Employee AS eON j.EmployeeID = e.EmployeeID

C. SELECT e.Resume, j.EmployeeID FROM JobCandidate AS j FULL OUTER JOIN Employee AS eON j.EmployeeID = e.EmployeeID

D. SELECT e.Resume, j.EmployeeID FROM JobCandidate AS j RIGHT OUTER JOIN Employee AS eON j.EmployeeID = e.EmployeeID

Answer: D

Question: 9

You are a database developer for Company.com Information for the Parts database is sent daily as an XML document and is placed in a single row in the ProductInfo column of the ProductXML table. A sample of the information in the XML document is shown in the following display.

<Products>

<Product ID="" Name="" Number=""/>

<Product ID="" Name="" Number=""/>

...