Latest 70-433 Real Exam Download 21-30

Ensurepass

QUESTION 21

You are a database developer responsible for maintaining an application.

The application has a table named Programs that has the following definition: CREATE TABLE [dbo].[Customers]([ID] int NOT NULL IDENTITY(1,1),

[Name] varchar(100) NOT NULL, [Address] varchar(255) NOT NULL, [City] varchar(100) NOT NULL, [State] char(2) NOT NULL,

[Postal] varchar(5) NOT NULL, [Priority] char(1) NOT NULL,

[Active] bit NOT NULL)GOALTER TABLE [dbo].[Customers] ADD CONSTRAINT PK_Customers PRIMARY KEY NONCLUSTERED (Name)GO You need to modify the Customers table to meet the following requirements:

·ID must be the Primary Key.

·The clustered index must be on the ID column.

·The Active column must have a default value of 1, and must allow values of only 0 or 1.

·The Priority column must have values of "a", "b", or "c".

·The Postal column must contain a correctly formatted five-digit numeric Postal Code. Which Transact-SQL statement or statements should you use?

A. ALTER TABLE [dbo].[Customers] ADD CONSTRAINT PK_Customers PRIMARY KEY CLUSTERED

(ID)

ALTER TABLE [dbo].[Customers] ADD CONSTRAINT CK_Priority CHECK ([Priority] IN (‘a’, ‘b’,’c’)) ALTER TABLE [dbo].[Customers] ADD CONSTRAINT CK_Postal CHECK ([Postal] LIKE ‘[0-9][0-9][0-9] [0-9][0-9]’)

ALTER TABLE [dbo].[Customers] ADD CONSTRAINT DF_Active DEFAULT (1) FOR [Active] ALTER TABLE [dbo].[Customers] ADD CONSTRAINT CK_Active CHECK ([Active] IN (0,1))

B. ALTER TABLE [dbo].[Customers] DROP CONSTRAINT PK_Customers

ALTER TABLE [dbo].[Customers] ADD CONSTRAINT PK_Customers PRIMARY KEY CLUSTERED (ID)

ALTER TABLE [dbo].[Customers] ADD CONSTRAINT CK_Priority CHECK ([Priority] IN (‘a’, ‘b’, ‘c’)) ALTER TABLE [dbo].[Customers] ADD CONSTRAINT CK_Postal CHECK ([Postal] LIKE ‘[0-9][0-9][0-9] [0-9][0-9]’)

ALTER TABLE [dbo].[Customers] ADD CONSTRAINT DF_Active DEFAULT (1) FOR [Active] ALTER TABLE [dbo].[Customers] ADD CONSTRAINT CK_Active CHECK ([Active] IN (0,1))

C. ALTER TABLE [dbo].[Customers] ADD CONSTRAINT PK_Customers PRIMARY KEY CLUSTERED (ID)

ALTER TABLE [dbo].[Customers] ADD CONSTRAINT CK_Priority VALIDATE ([Priority] IN (‘a’, ‘b’,’c’)) ALTER TABLE [dbo].[Customers] ADD CONSTRAINT CK_Postal VALIDATE ([Postal] LIKE ‘[0-9][0-9] [0-9][0-9][0-9]’)

ALTER TABLE [dbo].[Customers] ADD CONSTRAINT DF_Active DEFAULT (1) FOR [Active] ALTER TABLE [dbo].[Customers] ADD CONSTRAINT CK_Active VALIDATE ([Active] IN (0,1))

D. ALTER TABLE [dbo].[Customers] DROP CONSTRAINT PK_Customers

ALTER TABLE [dbo].[Customers] ADD CONSTRAINT PK_Customers PRIMARY KEY NONCLUSTERED (ID)

ALTER TABLE [dbo].[Customers] ADD CONSTRAINT CK_Priority CHECK ([Priority] IN (‘a’, ‘b’, ‘c’)) ALTER TABLE [dbo].[Customers] ADD CONSTRAINT CK_Postal CHECK ([Postal] LIKE ‘[0-9][0-9][0-9] [0-9][0-9]’)

ALTER TABLE [dbo].[Customers] ADD CONSTRAINT DF_Active DEFAULT (1) FOR [Active] ALTER TABLE [dbo].[Customers] ADD CONSTRAINT CK_Active CHECK ([Active] IN (0,1))

E. ALTER TABLE [dbo].[Customers] DROP CONSTRAINT PK_Customers

ALTER TABLE [dbo].[Customers] ADD CONSTRAINT PK_Customers PRIMARY KEY (ID)

ALTER TABLE [dbo].[Customers] ADD CONSTRAINT CK_DataChecks CHECK ([Priority] LIKE ‘[abc]’ AND ISNUMERIC([Postal]) = 1 AND LEN([Postal]) = 5 AND [Active] IN (0,1))

F. ALTER CONSTRAINT PK_Customers SET CLUSTERED (ID)

ALTER TABLE [dbo].[Customers] ADD CONSTRAINT PK_Customers PRIMARY KEY CLUSTERED (ID)

ALTER TABLE [dbo].[Customers] ADD CONSTRAINT CK_Priority CHECK ([Priority] IN (‘a’, ‘b’, ‘c’)) ALTER TABLE [dbo].[Customers] ADD CONSTRAINT CK_Postal CHECK ([Postal] LIKE ‘[0-9][0-9][0-9] [0-9][0-9]’)

ALTER TABLE [dbo].[Customers] ADD CONSTRAINT DF_Active DEFAULT (1) FOR [Active] ALTER TABLE [dbo].[Customers] ADD CONSTRAINT CK_Active CHECK ([Active] IN (0,1))

G. ALTER TABLE [dbo].[Customers] DROP CONSTRAINT PK_Customers

ALTER TABLE [dbo].[Customers] ADD CONSTRAINT PK_Customers PRIMARY KEY (ID) ALTER TABLE [dbo].[Customers] ADD CONSTRAINT CK_DataChecks CHECK ([Priority] = ‘[abc]’ AND LEN([Postal]) = 5 AND [Active] IN (0,1))

H. ALTER TABLE [dbo].[Customers] DROP CONSTRAINT PK_Customers

ALTER TABLE [dbo].[Customers] ADD CONSTRAINT PK_Customers PRIMARY KEY (ID)

ALTER TABLE [dbo].[Customers] ADD CONSTRAINT CK_DataChecks CHECK ([Priority] IN ‘[abc]’ AND ISNUMERIC([Postal]) = 1 AND [Active] IN (0,1))

Correct Answer: BE


QUESTION 22

You administer a Microsoft SQL Server 2008 database that contains a table named Sales.SalesOrderHeader. The Sales.SalesOrderHeader table has the following definition: CREATE TABLE [Sales].[SalesOrderHeader](

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

[OrderDate] [datetime] NOT NULL, [DueDate] [datetime] NOT NULL, [ShipDate] [datetime] NULL, [Status] [tinyint] NOT NULL, [CustomerID] [int] NOT NULL,

[SalesPersonID] [int] NULL, [SubTotal] [money] NOT NULL, [TaxAmt] [money] NOT NULL,

CONSTRAINT [PK_OrderHeader_SalesOrderID] PRIMARY KEY CLUSTERED

(

[SalesOrderID] ASC

) ) ON [PRIMARY]GO You want to generate an execution plan of XML output document for a query that displays all Sales.SalesOrderHeader records containing orders that have not been shipped. You need to ensure that the following requirements are met:

·An XML document is provided only for this query.

·An XML document that contains the estimated execution plan is returned. Which three

Transact-SQL statements should you use? (To answer, move the appropriate statements from the list of statements to the answer area and arrange them in the correct order.)

Build List and Reorder:

clip_image001

Correct Answer:

clip_image002


QUESTION 23

You have a table named Customers that has an XML column named CustomerData. There are currently no indexes on the table. You use the following WHERE clause in a query: WHERE CustomerData.exist (‘/CustomerDemographic/@Age[.>="21"]’) = 1 You need to create indexes for the query.

Which Transact-SQL statements should you use?

A. CREATE PRIMARY XML INDEX PXML_IDX_Customer ON Customers(CustomerData); CREATE XML INDEX SXML_IDX_Customer ON Customer(CustomerData)

USING XML INDEX PXML_IDX_Customer FOR PATH;

B. CREATE PRIMARY XML INDEX PXML_IDX_Customer ON Customers(CustomerData); CREATE XML INDEX SXML_IDX_Customer ON Customer(CustomerData)

USING XML INDEX PXML_IDX_Customer FOR VALUE;

C. CREATE CLUSTERED INDEX CL_IDX_Customer ON Customers(CustomerID); CREATE PRIMARY XML INDEX PXML_IDX_Customer ON Customers(CustomerData);

CREATE XML INDEX SXML_IDX_Customer_Property ON Customer(CustomerData) USING XML INDEX PXML_IDX_Customer FOR VALUE;

D. CREATE CLUSTERED INDEX CL_IDX_Customer ON Customers(CustomerID); CREATE PRIMARY XML INDEX PXML_IDX_Customer ON Customers(CustomerData); CREATE XML INDEX SXML_IDX_Customer ON Customer(CustomerData)

USING XML INDEX PXML_IDX_Customer FOR PATH;

Correct Answer: D


QUESTION 24

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 are a developer for a Microsoft SQL Server 2008 R2 database instance used to support a customer service application. You create tables named complaint, customer, and product as follows: CREATE TABLE [dbo].[complaint] ([ComplaintID] [int],

[ProductID] [int], [CustomerID] [int],

[ComplaintDate] [datetime]); CREATE TABLE [dbo].[customer] ([CustomerID] [int], [CustomerName] [varchar](100),

[Address] [varchar](200), [City] [varchar](100),

[State] [varchar](50),

[ZipCode] [varchar](5)); CREATE TABLE [dbo].[product] ([ProductID] [int], [ProductName] [varchar](100),

[SalePrice] [money],

[ManufacturerName] [varchar](100)); You need to write a query to identify all customers who have complained about products that have an average sales price of 500 or more from September 01, 2011. Which SQL query should you use?

A. SELECT c.CustomerName,p.ProductName,SUM(p.SalePrice) AS Sales FROM product p

INNER JOIN complaint com ON p.ProductID = com.ProductID INNER JOIN customer c ON com.CustomerID = c.CustomerID

GROUP BY GROUPING SETS ((c.CustomerName, p.ProductName), ());

B. SELECT c.CustomerName,p.ProductName,SUM(p.SalePrice) AS Sales FROM product p

INNER JOIN complaint com ON p.ProductID = com.ProductID INNER JOIN customer c ON com.CustomerID = c.CustomerID

GROUP BY GROUPING SETS ((c.CustomerName), (p.ProductName), ());

C. SELECT c.CustomerName,COUNT(com.ComplaintID) AS Complaints FROM customer c

INNER JOIN complaint com ON c.CustomerID = com.CustomerID WHERE COUNT(com.ComplaintID) > 10

GROUP BY c.CustomerName;

D. SELECT c.CustomerName,AVG(p.SalePrice) AS Sales FROM product p

INNER JOIN complaint com ON p.ProductID = com.ProductID INNER JOIN customer c ON com.CustomerID = c.CustomerID WHERE com.ComplaintDate > ’09/01/2011′

AND AVG(p.SalePrice) >= 500

E. SELECT p.ProductName,DATEPART(mm, com.ComplaintDate) ComplaintMonth,SUM(p.SalePrice) AS Sales

FROM product p

INNER JOIN complaint com ON p.ProductID = com.ProductID GROUP BY p.ProductName, ComplaintMonth;

F. SELECT c.CustomerName,COUNT(com.ComplaintID) AS complaints FROM customer c

INNER JOIN complaint com ON c.CustomerID = com.CustomerID GROUP BY c.CustomerName

HAVING COUNT(com.ComplaintID) > 10;

G. SELECT p.ProductName,DATEPART(mm, com.ComplaintDate) ComplaintMonth,SUM(p.SalePrice)

AS Sales

FROM product p

INNER JOIN complaint com ON p.ProductID = com.ProductID

GROUP BY CUBE(p.ProductName, DATEPART(mm, com.ComplaintDate)); , ComplaintMonth;

H. SELECT p.ProductName,DATEPART(mm, com.ComplaintDate) ComplaintMonth,SUM(p.SalePrice) AS Sales

FROM product p

INNER JOIN complaint com ON p.ProductID = com.ProductID

GROUP BY CUBE;.CustomerName,COUNT(com.ComplaintID) AS complaints FROM customer c INNER

JOIN complaint com ON c.CustomerID = com.CustomerID GROUP BY c.CustomerName

HAVING COUNT(com.ComplaintID) > 10;

I. SELECT c.CustomerName,AVG(p.SalePrice) AS Sales FROM product p

INNER JOIN complaint com ON p.ProductID = com.ProductID INNER JOIN customer c ON com.CustomerID = c.CustomerID WHERE com.ComplaintDate > ’09/01/2011′

GROUP BY c.CustomerName

HAVING AVG(p.SalePrice) >= 500.ProductName,DATEPART(mm, com.ComplaintDate) ComplaintMonth,SUM(p.SalePrice)

AS SalesFROMproduct p

INNER JOINcomplaint com ON p.ProductID = com.ProductID

GROUP BY CUBE(p.ProductName, DATEPART(mm, com.ComplaintDate));

J. . SELECT p.ProductName,DATEPART(mm, com.ComplaintDate) ComplaintMonth,SUM(p.SalePrice) AS Sales

FROM product p

INNER JOIN complaint com ON p.ProductID = com.ProductID

GROUP BY p.ProductName, DATEPART(mm, com.ComplaintDate);.ProductName,DATEPART(mm, com.ComplaintDate) ComplaintMonth,SUM(p.SalePrice)

AS Sales

FROM product p

INNER JOIN complaint com ON p.ProductID = com.ProductID GROUP BY CUBE;

Correct Answer: I


QUESTION 25

You have two tables named dbo.Products and dbo.PriceChange. Table dbo.Products contains ten products. Five products are priced at $20 per unit and have PriceIncrease set to 1. The other five products are priced at $10 per unit and have PriceIncrease set to 0. You have the following query: INSERT dbo.PriceChange (ProductID, Change, ChangeDate)SELECT ProductID, inPrice – delPrice, SYSDATETIME()FROM (

UPDATE dbo.Products SET Price *= 1.1

OUTPUT inserted.ProductID, inserted.Price, deleted.Price

WHERE PriceIncrease = 1) p (ProductID, inPrice, delPrice); You need to predict the results of the query. Which results should the query produce?

A. Five rows are updated in dbo.Products.No rows are inserted into dbo.PriceChange.

B. No rows are updated in dbo.Products.Five rows are inserted into dbo.PriceChange.

C. No rows are updated in dbo.Products.No rows are inserted into dbo.PriceChange.

D. Five rows are updated in dbo.Products.Five rows are inserted into dbo.PriceChange.

Correct Answer: D


QUESTION 26

You are tasked to create a table that has a column that must store the current time accurate to

ten microseconds. You need to use a system function in conjunction with the DEFAULT option in the column definition. Which system function should you use?

A. DATEADD

B. SYSDATETIME

C. CURRENT_TIMESTAMP

D. GETUTCDATE

Correct Answer: B


QUESTION 27

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 05:00 hours to 05:30 hours daily.

·Stores trace information in a format that can be used even while users are disconnected from the network. 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_image003

Correct Answer:

clip_image004


QUESTION 28

You administer a Microsoft SQL Server 2008 database that contains a table named

Sales.SalesOrderHeader. The Sales.SalesOrderHeader table has the following definition: CREATE TABLE [Sales].[SalesOrderHeader](

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

[OrderDate] [datetime] NOT NULL, [DueDate] [datetime] NOT NULL, [ShipDate] [datetime] NULL, [Status] [tinyint] NOT NULL, [CustomerID] [int] NOT NULL, [SalesPersonID] [int] NULL, [SubTotal] [money] NOT NULL,

[TaxAmt] [money] NOT NULL,

CONSTRAINT [PK_OrderHeader_SalesOrderID] PRIMARY KEY CLUSTERED

(

[SalesOrderID] ASC

) ) ON [PRIMARY]GO You plan to perform the following tasks:

·Display all Sales.SalesOrderHeader records that contain orders that have not been shipped.

·Generate an execution plan of XML output document for the query. You need to ensure that the following requirements are met:

·Data is returned for all fields in the Sales.SalesOrderHeader table where the shipping date is unknown.

·An XML document is provided only for this query.

·An XML document that contains the actual execution plan is returned. Which three Transact-SQL statements should you use? (To answer, move the appropriate statements from the list of statements to the answer area and arrange them in the correct order.)

Build List and Reorder:

clip_image005

Correct Answer:

clip_image006


QUESTION 29

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 are a developer for a

Microsoft SQL Server 2008 R2 database instance used to support a customer service application. You create tables named complaint, customer, and product as follows: CREATE TABLE [dbo].[complaint] ([ComplaintID] [int],

[ProductID] [int], [CustomerID] [int],

[ComplaintDate] [datetime]); CREATE TABLE [dbo].[customer] ([CustomerID] [int], [CustomerName] [varchar](100),

[Address] [varchar](200), [City] [varchar](100),

[State] [varchar](50),

[ZipCode] [varchar](5)); CREATE TABLE [dbo].[product] ([ProductID] [int], [ProductName] [varchar](100),

[SalePrice] [money],

[ManufacturerName] [varchar](100)); You need to write a query to sum the sales made to each customer who has made a complaint by the following entries:

·The customer name and product name

·The grand total of all sales Which SQL query should you use?

A. SELECT c.CustomerName,p.ProductName,SUM(p.SalePrice) AS Sales FROM product p

INNER JOIN complaint com ON p.ProductID = com.ProductID INNER JOINcustomer c ON com.CustomerID = c.CustomerID

GROUP BY GROUPING SETS ((c.CustomerName), (p.ProductName), ());

B. SELECT p.ProductName,DATEPART(mm, com.ComplaintDate) ComplaintMonth,SUM(p.SalePrice) AS Sales

FROM product p

INNER JOIN complaint com ON p.ProductID = com.ProductID

GROUP BY CUBE(p.ProductName, DATEPART(mm, com.ComplaintDate));

C. SELECTp.ProductName,DATEPART(mm, com.ComplaintDate) ComplaintMonth,SUM(p.SalePrice) AS Sales

FROM product p

INNER JOIN complaint com ON p.ProductID = com.ProductID GROUP BY p.ProductName, ComplaintMonth;

D. SELECT p.ProductName,DATEPART(mm, com.ComplaintDate) ComplaintMonth,SUM(p.SalePrice) AS Sales

FROM product p

INNER JOIN complaint com ON p.ProductID = com.ProductID GROUP BY CUBE;

E. SELECT c.CustomerName,AVG(p.SalePrice) AS Sales FROM product p

INNER JOIN complaint com ON p.ProductID = com.ProductID INNER JOIN customer c ON com.CustomerID = c.CustomerID WHERE com.ComplaintDate > ’09/01/2011′

AND AVG(p.SalePrice) >= 500

F. SELECT c.CustomerName,p.ProductName,SUM(p.SalePrice) AS Sales FROM product p

INNER JOIN complaint com ON p.ProductID = com.ProductID INNER JOIN customer c ON com.CustomerID = c.CustomerID

GROUP BY GROUPING SETS ((c.CustomerName, p.ProductName), ());

G. SELECT c.CustomerName,COUNT(com.ComplaintID) AS Complaints FROM customer c

INNER JOIN complaint com ON c.CustomerID = com.CustomerID WHERE COUNT(com.ComplaintID) > 10

GROUP BY c.CustomerName;

H. SELECT c.CustomerName,COUNT(com.ComplaintID) AS complaints FROM customer c

INNER JOIN complaint com ON c.CustomerID = com.CustomerID GROUP BY c.CustomerName

HAVING COUNT(com.ComplaintID) > 10;

I. SELECT c.CustomerName,AVG(p.SalePrice) AS Sales FROM product p

INNER JOIN complaint com ON p.ProductID = com.ProductID INNER JOINcustomer c ON com.CustomerID = c.CustomerID WHERE com.ComplaintDate > ’09/01/2011′

GROUP BY c.CustomerName HAVING AVG(p.SalePrice) >= 500

J. SELECT p.ProductName,DATEPART(mm, com.ComplaintDate) ComplaintMonth,SUM(p.SalePrice) AS Sales

FROM product p

INNER JOIN complaint com ON p.ProductID = com.ProductID GROUP BY p.ProductName, DATEPART(mm, com.ComplaintDate);

Correct Answer: F


QUESTION 30

You administer a Microsoft SQL Server 2008 R2 database instance named AdventureWorks. A user who has the db_datareader permissions on the AdventureWorks database wants to view detailed information about how the following query will be executed:

SELECT * FROM Sales.SalesOrderHeaderWHERE OnlineOrderFlag = 1 AND SubTotal > 500 You need to ensure that the user can view the following information in a data grid without executing

the query:

·Estimated number of rows of output

·Estimated I/O cost

·Estimated CPU cost Which two 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_image007

Correct Answer:

clip_image008


 

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