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