Latest Cisco IBM HP Oracle certifications batch pdf

Free 70-442GB2312 Demo Download

Examsoon offers free demo for Microsoft Licensing 70-442GB2312 Practice exam (PRO:Design & Optimize Data Access by Using MS SQL Serv 2005). You can check out the interface, question quality and usability of our practice exam before you decide to get it. We are the only one site can offer demo for almost all products.

Free Download 70-442GB2312 PDF Practice exam

　
　
Exam : Microsoft 70-442GB2312
Title : PRO:Design & Optimize Data Access by Using MS SQL Serv 2005

1. 您是公司的数据库开发人员。有一个将订单项保存到 OrderItems 表的过程。如果该项不存在，则应执行插入。如果该项存在，则应执行更新。OrderItems 表设计为如下所示。
OrderID (PK, FK)
ProductID (PK, FK)
Quantity
int
int
int
您需要开发一个使用最少量资源的例程。
您应该使用哪个例程？
A. BEGIN TRY
UPDATE OrderItems SET Quantity = @Quantity
WHERE OrderID = @OrderID
AND ProductID = @ProductID;
END TRY
BEGIN 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 = @Quantity
WHERE OrderID = @OrderID AND ProductID = @ProductID;
IF(@@ROWCOUNT = 0)
INSERT OrderItems (OrderID, ProductID, Quantity)
VALUES (@OrderID, @ProductID, @Quantity);
Answer: D

2. 您是公司的数据库开发人员。公司正在从 SQL Server 2000 升级到 SQL Server 2005。公司新的编码标准要求符合 ANSI 标准。
您的经理要求您将以下查询转换为使用 ANSI 标准措词。
SELECT E.EmployeeID, E.Hiredate, J.Resume
FROM HumanResources.Employee AS E,
HumanResources.JobCandidate AS J
WHERE E.EmployeeID *= J.EmployeeID
新查询必须始终生成与现有查询相同的结果集。
您需要编写查询以满足这些要求。
您应该使用哪个 Transact-SQL 查询？
A. SELECT E.EmployeeID, E.Hiredate, J.Resume
FROM HumanResources.Employee AS E RIGHT OUTER JOIN
HumanResources.JobCandidate AS J
ON E.EmployeeID = J.EmployeeID
B. SELECT E.EmployeeID, E.Hiredate, J.Resume
FROM HumanResources.Employee AS E LEFT OUTER JOIN
HumanResources.JobCandidate AS J
ON E.EmployeeID = J.EmployeeID
C. SELECT E.EmployeeID, E.Hiredate, J.Resume
FROM HumanResources.Employee AS E INNER JOIN
HumanResources.JobCandidate AS J
ON E.EmployeeID = J.EmployeeID
D. SELECT E.EmployeeID, E.Hiredate, J.Resume
FROM HumanResources.Employee AS E FULL OUTER JOIN
HumanResources.JobCandidate AS J
ON E.EmployeeID = J.EmployeeID
Answer: B

3. 您是公司的数据库开发人员。每天将以 XML 文档的形式发送 Parts 数据库的信息，并将该信息放在 ProductXML 表的 ProductInfo 列中的单一行中。该 XML 文档中的信息的示例如下所示。
<Products>
<Product ID="…" Name="…" Number="…"/>
<Product ID="…" Name="…" Number="…"/>
…
</Products>
每天结束时将运行一个批处理作业。这个批处理作业将 XML 文档从 ProductXML 表导入 Product 表。将为每个 Product 元素从 XML 文档中提取产品的 ID、名称和编号，并将这些信息插入 Product 表的 ProductID、Name、和 Number 列中。在批处理结束时会截断 ProductXML 表。
您需要设计从 ProductXML 表中检索行以插入 Product 表的查询。
您应该使用哪个 Transact-SQL 查询从 ProductXML 表中查询数据？
A. 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
B. SELECT
col.value(’@ID’, ‘integer’) AS ProductID,
col.value(’@Name’, ‘varchar(50)’) AS Name,
col.value(’@Number’, ‘varchar(25)’) AS ProductNumber
FROM ProductXML
CROSS APPLY ProductInfo.nodes(’/Products/Product’) AS x (col)
C. SELECT
col.query(’data(@ID), data(@Name), data(@Number)’)
FROM ProductXML
CROSS APPLY ProductInfo.nodes(’/Products/Product’) AS x (col)
D. SELECT
ProductInfo.query(’
for $p in /Products/Product
return fn:concat($p/@ID, $p/@Name, $p/@Number)
‘) AS Result
FROM ProductXML
Answer: B

4. 您是公司的数据库开发人员。一个名为 Articles 的表包含报纸文章，其设计如下所示。
ArticleID (PK)
Title
ArticleXml
CategoryID (FK)
int
nvarchar(50)
xml
int
ArticleXml 列中的 XML 文档如下所示。
<article>
<paragraph>…</paragraph>
<image>…</image>
…
<image>…</image>
…
</article>您需要设计一个查询，该查询将按照 CategoryID 值显示图像的总数。
您应该使用哪个查询？
A. SELECT
CategoryID
,COUNT(ArticleXml.value(’count(/article/image)’,
‘INT’)) AS NumberOfImages
FROM Articles
GROUP BY CategoryID;
B. SELECT
CategoryID
,COUNT(ArticleXml.exist(’/article/image’)
AS NumberOfImages
FROM Articles
GROUP BY CategoryID;
C. SELECT
CategoryID
,SUM(ArticleXml.value(’count(/article/image)’, ‘INT’))
AS NumberOfImages
FROM Articles
GROUP BY CategoryID;
D. SELECT
CategoryID
,SUM(ArticleXml.exist(’/article/image’)
AS NumberOfImages
FROM Articles
GROUP BY CategoryID;
Answer: C

5. 您是公司的数据库开发人员。用户需要有关在 TotalDue 列中具有最高值的订单的信息。用户希望能够在每次请求信息时返回不同的记录百分比。
您需要在 SQL Server 2005 上生成代码，该代码将允许前端应用程序传递最终用户将为要返回的记录百分比提供的变量。
您应该如何编写代码？
A. CREATE PROCEDURE TopPercentTotalDue
@P int
AS
SELECT TOP(@P) PERCENT
TotalDue, OrderDate
FROM Purchasing.PurchaseOrderHeader
ORDER BY TotalDue DESC
B. CREATE PROCEDURE TopPercentTotalDue
@P int
AS
SELECT TOP(@P) PERCENT
TotalDue, OrderDate
FROM Purchasing.PurchaseOrderHeader
ORDER BY TotalDue ASC
C. CREATE PROCEDURE TopPercentTotalDue
@R int
AS
SET rowcount @R
SELECT TotalDue, OrderDate
FROM Purchasing.PurchaseOrderHeader
ORDER BY TotalDue DESC
D. CREATE PROCEDURE TopPercentTotalDue
@R int
AS
SET rowcount @R
SELECT TotalDue, OrderDate
FROM Purchasing.PurchaseOrderHeader
ORDER BY TotalDue ASC
Answer: A

6. 您是公司的数据库开发人员。现必须优化 Stockholm 市订单的处理例程，以获得更好的性能。
当前例程如以下代码段所示。
…
DECLARE OrderCursor CURSOR FOR
SELECT OrderID, City, CustomerID FROM Orders;
OPEN OrderCursor;
FETCH NEXT FROM OrderCursor INTO @OrderID, @CustomerID, @City;
WHILE(@@FETCH_STATUS = 0)
BEGIN
IF(@City <> ‘Stockholm’)
GOTO Next;
<Code for processing order>
Next:
FETCH NEXT FROM OrderCursor INTO
@OrderID, @CustomerID, @City;
END
…
您需要优化该例程以获得最佳性能。
您应该怎么办？
A. 将游标更改为以 DYNAMIC 形式声明。
B. 将游标更改为以 KEYSET 形式声明。
C. 将游标更改为以 STATIC 形式声明。
D. 更改游标中的 SELECT 语句以包括 WHERE City = ‘Stockholm’。
E. 将 IF 语句更改为检查 @City = ‘Stockholm’，从而避免使用 GOTO 语句。
Answer: D

7. 您是公司的数据库开发人员。您正在创建一个参数化存储过程，该存储过程将查询数据并返回一些行，其中包含作为该存储过程输入提供的动词的任何动词时态。查询还应返回同时包含名词的单数和复数形式的行。数据包括在若干 varchar(max) 和 varbinary(max) 列中。
您需要创建存储过程来满足这些要求。
您应该执行哪两项操作？（每个正确答案都仅给出了部分解决方案。请选择两个答案。）
A. 使用 LIKE 运算符。
B. 使用 CONTAINS 谓词。
C. 针对适当的一个或多个列创建全文索引。
D. 针对适当的一个或多个列创建非聚集索引。
Answer: BC

8. 您是公司的数据库开发人员。公司最近升级到了 SQL Server 2005。
您正在创建一个查询，以便返回显示指定员工组所下达采购订单总数的信息，按请求订单的供应商分组。
结果集应在其单独一列中显示每位员工的采购订单总数。每个供应商在结果集中都应有自己的行。
您需要创建查询来满足这些要求。
您应该使用哪个 Transact-SQL 命令？
A. CROSS APPLY
B. UNION
C. PIVOT
D. INTERSECT
Answer: C

9. 您是公司的数据库开发人员。您负责在公司新的 SQL Server 2005 计算机上将名为 DB1 和 DB2 的两个现有 SQL Server 2000 数据库合并为一个名为 NewDB 的数据库。
以下查询在用于访问 DB1 数据库的旧应用程序中可以正常工作。
SELECT * FROM Customer WHERE FaxNumber = NULL
但是，该查询在新的 SQL Server 2005 计算机上运行时将不会返回任何行，即使 FaxNumber 列中存在 NULL 值。
您需要解决该问题，以使该查询在 SQL Server 2005 计算机上能正常工作。您需要确保您的解决方案不会影响 DB2 中表和查询的行为。
您应该怎么办？
A. 使用 ISNULL 函数，而不是 = NULL。
B. 将 = NULL 更改为 IS NULL。
C. 将 = NULL 更改为 = ‘NULL’。
D. 在查询前面添加以下命令：SET ANSI_NULLS ON
E. 使用 ALTER DATABASE 命令将 ANSI_NULLS 数据库选项更改为 ON。
Answer: B

10. 您是公司的数据库开发人员。在您的数据库中，Employees 表包含大约 1,000 行，Orders 表包含数百万行。
Employees 表设计为如下所示。
EmployeeID (PK)
Firstname
Lastname
…
int
nvarchar(30)
nvarchar(30)
…
Orders 表设计为如下所示。
OrderID (PK)
SoldByEmployeeID (FK)
OrderDate
…
int
int
datetime
…
您需要编写一个查询，该查询返回每位员工所下的订单数。
您应该使用哪个查询？
A. SELECT e.Firstname, e.Lastname, COUNT(*)
FROM Employees AS e
LEFT OUTER JOIN Orders AS o
ON o.SoldByEmployeeID = e.EmployeeID
GROUP BY e.Firstname, e.Lastname
B. SELECT e.Firstname, e.Lastname, COUNT(*)
FROM Employees AS e
LEFT OUTER JOIN Orders AS o
ON o.SoldByEmployeeID = e.EmployeeID
GROUP BY e.EmployeeID, e.Firstname, e.Lastname
C. SELECT e.Firstname, e.Lastname, COUNT(o.OrderID)
FROM Employees AS e
LEFT OUTER JOIN Orders AS o
ON o.SoldByEmployeeID = e.EmployeeID
GROUP BY e.EmployeeID, e.Firstname, e.Lastname
D. SELECT e.Firstname, e.Lastname, COUNT(o.OrderID)
FROM Employees AS e
LEFT OUTER JOIN Orders AS o
ON o.SoldByEmployeeID = e.EmployeeID
GROUP BY e.Firstname, e.Lastname
Answer: C

70-442GB2312 Practice exam Description

Credit Toward CertificationWhen you pass Practice exam 70-442GB2312: PRO:Design & Optimize Data Access by Using MS SQL Serv 2005, you complete the requirements for the following certification(s):

Microsoft Certified Technical Specialist: PRO:Design & Optimize Data Access by Using MS SQL Serv 2005 Note This preparation guide is subject to change at any time without prior notice and at the sole discretion of Microsoft.Microsoft exam might include adaptive testing technology and simulation items.Microsoft does not identify the format in which exam are presented. Please use this preparation guide to prepare for the exam, regardless of its format.

We have examsoon 70-442GB2312 practice exam,examsoon 70-442GB2312 exam,examsoon 70-442GB2312 study guide,examsoon 70-442GB2312 dumps.and Pass4sure 70-442GB2312 exam,Pass4sure 70-442GB2312 practice exam,pass4sure 70-442GB2312 study guide. and we have Testking 70-442GB2312 exam,Testking 70-442GB2312 study guide,examsoon 70-442GB2312 exam.etc.

Examsoon professional IT Q&As vendors, offer well after-sale service for 70-442GB2312 exam. To all the customers buy the real 70-442GB2312 exam questions and answers, examsoon provide track service for 70-442GB2312 study guides. when you buy the Q&As with in 3 months. you can enjoy the upgrade Q&As service for free with 70-442GB2312 practice exam. If in this period, the certified test center change the Microsoft Licensing 70-442GB2312 Exam Q&As, we will update the Microsoft Licensing 70-442GB2312 exam Q&As in the first time, and provide you the download update for free