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