REAL QUESTIONS SUBMIT MATERIAL ADVERTISE
Braindumps

Microsoft

Cisco

Citrix

CIW

CompTia

CWNA

Apple

Adobe

HP

Legato

Exin

Filemaker

Brocade

Ericsson

TIA

Veritas

ISEB

SCP

IISFA

ISM

OMG

Apc

Mile2

Foundry

Huawei

McData

Symantec

TeraData

RedHat

Solar Winds

Blue Coat

Riverbed

 

 
 
Click on name of dumper to view the dump
 

Franklin

Mason

 
 

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=""/>

...

</Products>

At the end of each day, a batch job is run. This batch imports the XML document into the Product table from the ProductXML table. The products ID, name, and number will be extracted from the XML document for each Product element and will be inserted into the Product tables ProductID, Name, and Number columns. The ProductXML table is truncated at the end of the batch. You need to design the query that retrieves the rows from the ProductXML table for insertion into the Product table. Which Transact-SQL query should you use to query the data from the ProductXML table?

A. SELECT col.query('data(@ID), data(@Name), data(@Number)') FROM ProductXMLCROSS APPLY ProductInfo.nodes('/Products/Product') AS x (col)

B. SELECT col.value('@ID', 'integer') AS ProductID, col.value('@Name', 'varchar(50)') AS Name, col.value('@Number', 'varchar(25)') AS ProductNumber FROM ProductXMLCROSS APPLY ProductInfo.nodes('/Products/Product') AS x (col)

C. SELECT ProductInfo.value('(/Products/Product)[1]/@ID', 'integer') AS ProductID, ProductInfo.value('(/Products/Product)[1]/@Name', 'varchar(50)') AS Name, ProductInfo.value('(/Products/Product)[1]/@Number', 'varchar(25)') AS ProductNumber FROM ProductXML

D. SELECT ProductInfo.query(' for $p in /Products/Product return fn:concat($p/@ID, $p/@Name, $p/@Number) ') AS ResultFROM ProductXML

Answer: B

Question: 10

You are a database developer for Company.com You are responsible for combining two existing SQL Server 2000 databases named DB1 and DB2 into a single database named NewDB on the companys new SQL Server 2005 computer. The following query functions properly from the old application that was used to access the DB1 database.

SELECT * FROM Customer WHERE FaxNumber = NULL

However, no rows are returned when the query is run on the new SQL Server 2005 computer, even though NULL values exist in the FaxNumber column. You need to resolve the problem so that the query will function properly on the SQL Server 2005 computer. You need to ensure that your solution does not affect the behavior of the tables and queries from DB2. What should you do?

A. Change = NULL to IS NULL.

B. Instead of = NULL use the ISNULL function.

C. Use the ALTER DATABASE command to change the ANSI_NULLS database option to ON.

D. Add the following command before the query: SET ANSI_NULLS ON

E. Change = NULL to = 'NULL'.

Answer: A

Question: 11

You are a database developer for a local school district. You maintain information about teachers and staff in a column of the xml data type. You need to produce a tabular report that contains the values of the EmployeeID, Title, and YearsOfEmployment columns for each employee. This report should be ordered by Title in ascending order and YearsOfEmployment in descending order. What should you use to retrieve this data?

A. FOR XML PATH

B. the query method of the xml data column

C. FOR XML AUTO

D. the value and nodes methods of the xml data column

Answer: D

 

 

Braindumps Real exam questions and verified answers - 100% passing guarantee - cheap prices.

 

Free brain dumps Braindumps, notes, books for free

 

Braindumps and Exams - Instant download real exam questions - Passing guarantee.

Follow us on FaceBook
Braindumps on Facebook
 
 
 
 
 

CheckPoint

Linux

Novell

DB/2

Network Appliance

EC-Council

Nortel

McAfee

Juniper

ISACA

PMI

Sybase

EMC

HDI

SNIA

ISC

Sair

IBM

Lotus

Exam Express

3COM

BICSI

DeLL

Enterasys

Extreme Networks

Guidance Software

Computer Associates

Network General

SAS Institute

Alcatel Lucent

SeeBeyond

TruSecure

Polycom

Hyperion

Hitachi

Nokia

Fortinet

Vmware

Fujitsu

Tibco

Intel

PostgreSQLCE

BusinessObjects

RESSoftware

BlackBerry

AccessData

ICDL

Isilon

SAP

The Open Group

ACSM

Altiris

Avaya

Cognos

F5

Genesys

SDI

ACI

ASQ

Google

H3C

HIPAA

HRCI

SOA

IIBA

Zend