Latest 70-433 Real Exam Download 41-50

Ensurepass

QUESTION 41

You have a database that contains two tables named ProductCategory and ProductSubCategory.

You need to write a query that returns a list of product categories that contain more than ten

sub-categories. Which query should you use?

A. SELECT [Name]

FROM ProductSubCategory

WHERE ProductCategoryID IN (SELECT ProductCategoryID

FROM ProductCategory)

GROUP BY [Name] HAVING COUNT(*) > 10

B. SELECT [Name]

FROM Product Category c

WHERE NOT EXISTS (SELECT ProductCategoryID

FROM ProductSubCategory

WHERE ProductCategoryID = c.ProductCategoryID GROUP BY ProductCategoryID

HAVING COUNT(*) > 10)

C. SELECT [Name]

FROM ProductSubCategory

WHERE ProductCategoryID NOT IN (SELECT ProductCategoryID

FROM ProductCategory)

GROUP BY [Name] HAVING COUNT(*) > 10

D. SELECT [Name]

FROM Product Category c

WHERE EXISTS (SELECT ProductCategoryID

FROM ProductSubCategory

WHERE ProductCategoryID = c.ProductCategoryID GROUP BY ProductCategoryID

HAVING COUNT(*) > 10)

Correct Answer: D


QUESTION 42

You are a developer of a Microsoft SQL Server 2008 R2 database instance that supports a web-based order-entry application. You need to create a server-side trace that meets the following requirements:

·Captures performance information from 07:00 hours to 07:30 hours daily.

·Stores trace information in a portable format. Which four actions should you perform in sequence? (To answer, move the appropriate actions from the list of actions to the answer area and arrange them in the correct order.)

Build List and Reorder:

clip_image001

Correct Answer:

clip_image002


QUESTION 43

You have two views named Sales.SalesSummaryOverall and Sales.CustomerAndSalesSummary. They are defined as follows: CREATE VIEW Sales.SalesSummaryOverallAS

SELECT CustomerId, SUM(SalesTotal) AS OverallTotal FROM Sales.SalesOrder

GROUP BY CustomerIdGOCREATE VIEW Sales.CustomerAndSalesSummaryAS SELECT Customer.Name,

SalesSummaryOverall.OverallTotal,

(SELECT AVG(OverallTotal)

FROM Sales.SalesSummaryOverall

WHERE SalesSummaryOverall.CustomerId = Customer.CustomerId) AS avgOverallTotal, (SELECT MAX(OverallTotal)

FROM Sales.SalesSummaryOverall

WHERE SalesSummaryOverall.CustomerId = Customer.CustomerId) AS maxOverallTotal, FROM Sales.Customer

LEFT OUTER JOIN Sales. Sales.SalesSummaryOverall

ON SalesSummaryByYear.CustomerId = Customer.CustomerIdGO You have been tasked to modify the Sales.CustomerAndSalesSummary view to remove references to other views. You need to identify a feature to use in the modified version of the Sales.CustomerAndSalesSummary object

to achieve the task. Which feature should you use?

A. Table variables

B. Common table expressions

C. Temporary tables

D. User-defined table types

Correct Answer: B


QUESTION 44

Note: This
QUESTION is part of a series of questions that use the same set of answer choices. An answer choice may be correct for more than one
QUESTION in the series. You administer a Microsoft SQL Server 2008 database for an inventory management system.

The application contains a product table that has the following definition: CREATE TABLE [Production].[Product](

[ProductID] [int] IDENTITY(1,1) NOT NULL,

[Name] [nvarchar](50) NOT NULL, [ProductNumber] [nvarchar](25) NOT NULL, [Color] [nvarchar](15) NULL,

[Class] [nchar](2) NULL,

[Style] [nchar](2) NULL, [Active] [bit] NOT NULL,

[ModifiedDate] [datetime] NOT NULL,

CONSTRAINT [PK_Product_ProductID] PRIMARY KEY CLUSTERED (

[ProductID] ASC

) ON [PRIMARY]) ON [PRIMARY]GO You want to add a new field to the Product table to meet the following requirements:

·Allows user-specified information that will be added to records in the Product table.

·Supports the largest storage size needed for the field.

·Uses the smallest data type necessary to support the domain of values that will be entered by users. You need to add a field named User_Data_1 to support U.S. dollar currency values that

have two decimals and as large as 500,000 U.S. dollars. Which SQL statement should you use?

A. ALTER TABLE [Production].[Product] ADD [User_Data_1] VARCHAR(100)

B. ALTER TABLE [Production].[Product] ADD [User_Data_1] CHAR(100)

C. ALTER TABLE [Production].[Product] ADD [User_Data_1] NUMERIC(6,11)

D. ALTER TABLE [Production].[Product] ADD [User_Data_1] DATETIME

E. ALTER TABLE [Production].[Product] ADD [User_Data_1] BIGINT

F. ALTER TABLE [Production].[Product] ADD [User_Data_1] NUMERIC(11,6)

G. ALTER TABLE [Production].[Product] ADD [User_Data_1] SMALLMONEY

H. ALTER TABLE [Production].[Product] ADD [User_Data_1] BIT

I. ALTER TABLE [Production].[Product] ADD [User_Data_1] NCHAR(100)

J. ALTER TABLE [Production].[Product] ADD [User_Data_1] DATETIME2

K. ALTER TABLE [Production].[Product] ADD [User_Data_1] NUMERIC(5,6)

L. ALTER TABLE [Production].[Product] ADD [User_Data_1] SMALLDATETIME

M. ALTER TABLE [Production].[Product] ADD [User_Data_1] NVARCHAR(100)

N. ALTER TABLE [Production].[Product] ADD [User_Data_1] SMALLINT

O. ALTER TABLE [Production].[Product] ADD [User_Data_1] DATE

P. ALTER TABLE [Production].[Product] ADD [User_Data_1] TINYINT Q ALTER TABLE [Production].[Product] ADD [User_Data_1] MONEY

Q. ALTER TABLE [Production].[Product] ADD [User_Data_1] INT

Correct Answer: Q


QUESTION 45

You administer a Microsoft SQL Server 2008 database that contains a table named Sales.SalesOrderDetail and a view named Sales.ProductOrders.

The view has the following definition: CREATE VIEW Sales.ProductOrders AS SELECT ProductID,

COUNT(*) as NbrOfOrders, SUM(OrderQty) as TotalOrderQtyFROM Sales.SalesOrderDetailGROUP BY

ProductIdGO The Sales.SalesOrderDetail table contains 5 million rows. Report queries that join to this view consume excessive disk I/O. You need to create an index on the view. Which

Transact-SQL statement or statements should you use?

A. ALTER VIEW Sales.ProductOrders WITH SCHEMABINDING

AS

SELECT ProductID,COUNT(*) as NbrOfOrders,SUM(OrderQty) as TotalOrderQty FROM Sales.SalesOrderDetail

GROUP BY ProductId GO

CREATE UNIQUE CLUSTERED INDEX IX_V_ProductOrders ON Sales.ProductOrders (ProductID) GO

B. ALTER VIEW Sales.ProductOrders WITH SCHEMABINDING

AS

SELECT ProductID,COUNT_BIG(*) as NbrOfOrders,SUM(OrderQty) as TotalOrderQty FROM Sales.SalesOrderDetail WITH (NOLOCK)

GROUP BY ProductId GO

CREATE UNIQUE CLUSTERED INDEX IX_V_ProductOrders ON Sales.ProductOrders (ProductID) GO

C. ALTER VIEW Sales.ProductOrders WITH SCHEMABINDING

AS

SELECT ProductID,COUNT_BIG(*) as NbrOfOrders,SUM(OrderQty) as TotalOrderQty FROM Sales.SalesOrderDetail

GROUP BY ProductId GO

CREATE UNIQUE CLUSTERED INDEX IX_V_ProductOrders ON Sales.ProductOrders (ProductID) GO

D. ALTER VIEW Sales.ProductOrders AS

SELECT ProductID,COUNT_BIG(*) as NbrOfOrders,SUM(OrderQty) as TotalOrderQty FROM Sales.SalesOrderDetail

GROUP BY ProductId GO

CREATE UNIQUE CLUSTERED INDEX IX_V_ProductOrders ON Sales.ProductOrders (ProductID)GO

E. IF EXISTS (SELECT *

FROM sys.views

WHERE object_id = OBJECT_ID(N'[Sales].[ProductOrders]’)

)

DROP VIEW [Sales].[ProductOrders] GO

CREATE VIEW Sales.ProductOrders WITH SCHEMABINDING

AS

SELECT ProductID,COUNT_BIG(*) as NbrOfOrders,SUM(OrderQty) as TotalOrderQty FROM Sales.SalesOrderDetail

GROUP BY ProductId GO

CREATE UNIQUE CLUSTERED INDEX IX_V_ProductOrders ON Sales.ProductOrders (ProductID) GO

F. IF EXISTS (SELECT *

FROM sys.views

WHERE object_id = OBJECT_ID(N'[Sales].[ProductOrders]’)

)

DROP VIEW [Sales].[ProductOrders] GO

CREATE VIEW Sales.ProductOrders AS

SELECT ProductID,COUNT_BIG(*) as NbrOfOrders,SUM(OrderQty) as TotalOrderQty FROM Sales.SalesOrderDetail

GROUP BY ProductId GO

CREATE UNIQUE CLUSTERED INDEX IX_V_ProductOrders ON Sales.ProductOrders (ProductID) GO

G. IF EXISTS (SELECT *

FROM sys.views

WHERE object_id = OBJECT_ID(N'[Sales].[ProductOrders]’)

)

DROP VIEW [Sales].[ProductOrders] GO

CREATE VIEW Sales.ProductOrders WITH SCHEMABINDING

AS

SELECT ProductID,COUNT_BIG(*) as NbrOfOrders,SUM(OrderQty) as TotalOrderQty FROM Sales.SalesOrderDetail WITH (NOLOCK)

GROUP BY ProductId GO

CREATE UNIQUE CLUSTERED INDEX IX_V_ProductOrders ON Sales.ProductOrders (ProductID)

GO

H. IF EXISTS (SELECT *

FROM sys.views

WHERE object_id = OBJECT_ID(N'[Sales].[ProductOrders]’)

)

DROP VIEW [Sales].[ProductOrders] GO

CREATE VIEW Sales.ProductOrders WITH SCHEMABINDING

AS

SELECT ProductID,COUNT(*) as NbrOfOrders,SUM(OrderQty) as TotalOrderQty FROM Sales.SalesOrderDetail

GROUP BY ProductId GO

CREATE UNIQUE CLUSTERED INDEX IX_V_ProductOrders ON Sales.ProductOrders (ProductID) GO

Correct Answer: CE


QUESTION 46

Note: This
QUESTION is part of a series of questions that use the same set of answer choices. An answer choice may be correct for more than one
QUESTION in the series. You administer a Microsoft SQL Server 2008 database for an inventory management system.

The application contains a product table that has the following definition: CREATE TABLE [Production].[Product](

[ProductID] [int] IDENTITY(1,1) NOT NULL,

[Name] [nvarchar](50) NOT NULL, [ProductNumber] [nvarchar](25) NOT NULL, [Color] [nvarchar](15) NULL,

[Class] [nchar](2) NULL,

[Style] [nchar](2) NULL, [Active] [bit] NOT NULL,

[ModifiedDate] [datetime] NOT NULL,

CONSTRAINT [PK_Product_ProductID] PRIMARY KEY CLUSTERED (

[ProductID] ASC

) ON [PRIMARY]) ON [PRIMARY]GO

You want to add a new field to the Product table to meet the following requirements:

·Allows user-specified information that will be added to records in the Product table.

·Supports the largest storage size needed for the field.

·Uses the smallest data type necessary to support the domain of values that will be entered by users. You need to add a field named User_Data_1 to support date values that are no more precise than to the hour.

Which SQL statement should you use?

A. ALTER TABLE [Production].[Product] ADD [User_Data_1] NVARCHAR(100)

B. ALTER TABLE [Production].[Product] ADD [User_Data_1] VARCHAR(100)

C. ALTER TABLE [Production].[Product] ADD [User_Data_1] BIGINT

D. ALTER TABLE [Production].[Product] ADD [User_Data_1] BIT

E. ALTER TABLE [Production].[Product] ADD [User_Data_1] SMALLINT

F. ALTER TABLE [Production].[Product] ADD [User_Data_1] MONEY

G. ALTER TABLE [Production].[Product] ADD [User_Data_1] TINYINT

H. ALTER TABLE [Production].[Product] ADD [User_Data_1] NCHAR(100)

I. ALTER TABLE [Production].[Product] ADD [User_Data_1] NUMERIC(11,6)

J. ALTER TABLE [Production].[Product] ADD [User_Data_1] DATETIME2

K. ALTER TABLE [Production].[Product] ADD [User_Data_1] DATETIME

L. ALTER TABLE [Production].[Product] ADD [User_Data_1] SMALLMONEY

M. ALTER TABLE [Production].[Product] ADD [User_Data_1] NUMERIC(5,6)

N. ALTER TABLE [Production].[Product] ADD [User_Data_1] NUMERIC(6,11)

O. ALTER TABLE [Production].[Product] ADD [User_Data_1] SMALLDATETIME

P. ALTER TABLE [Production].[Product] ADD [User_Data_1] INT

Q ALTER TABLE [Production].[Product] ADD [User_Data_1] CHAR(100)

Q. ALTER TABLE [Production].[Product] ADD [User_Data_1] DATE

Correct Answer: O


QUESTION 47

You are developing a database using Microsoft SQL Server 2008. The database contains the tables shown in the following exhibit. (Click the Exhibit button.) You are required to prevent parts from being deleted if they belong to a kit. If a part belongs to a kit, the delete should not occur and the IsDeleted column for the row should be changed to ‘True’. Parts should be deleted if they do not belong to a kit. You have the following Transact-SQL statement to be used in a trigger:

UPDATE p

SET IsDeleted = 1 FROM KitPart kp

JOIN deleted d ON kp.PartID = d.PartID JOIN Part p ON kp.PartID = p.PartID; DELETE FROM p

FROM Part p

JOIN deleted d ON p.PartID = d.PartID

LEFT OUTER JOIN KitPart kp ON p.PartID = kp.PartID

WHERE kp.KitID IS NULL; You need to implement the Transact-SQL statement in a trigger. Which trigger syntax should you use?

Exhibit:

clip_image003

A. CREATE TRIGGER tr_Part_d ON Part INSTEAD OF DELETE AS BEGIN …END

B. CREATE TRIGGER tr_Part_d ON Part AFTER DELETE AS BEGIN …END

C. CREATE TRIGGER tr_KitPart_d ON KitPart AFTER DELETE AS BEGIN …END

D. CREATE TRIGGER tr_KitPart_d ON KitPart INSTEAD OF DELETE AS BEGIN …END

Correct Answer: A


QUESTION 48

You are using TRY…CATCH error handling. You need to raise an error that will pass control to the CATCH block. Which severity level should you use?

A. 10

B. 16

C. 9

D. 0

Correct Answer: B


QUESTION 49

You need to alter stored procedures to use the WITH RECOMPILE option.

Which types of stored procedures should you alter? (Each correct answer represents a complete solution. Choose two.)

A. Stored procedures implemented from CLR assemblies.

B. Stored procedures that require the FOR REPLICATION option.

C. Stored procedures that require the WITH ENCRYPTION option.

D. Stored procedures that contain queries that use the OPTION (RECOMPILE) hint.

Correct Answer: CD


QUESTION 50

Your company uses an application that passes XML to the database server by using stored procedures. The database server has a large number of XML handles that are currently active. You determine that the XML is not being flushed from SQL Server memory. You need to identify the system stored procedure to flush the XML from memory. Which Transact-SQL statement should you use?

A. sp_xml_preparedocument

B. sp_xml_removedocument

C. DBCC DROPCLEANBUFFERS

D. DBCC FREEPROCACHE

Correct Answer: B


 

Download Latest 70-433 Real Free Tests , help you to pass exam 100%.