Latest 70-433 Real Exam Download 71-80

Ensurepass

QUESTION 71

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 decimal values between 0 and 10,000 that have up to six decimal points. Which SQL statement should you use?

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

P. ALTER TABLE [Production].[Product] ADD [User_Data_1] NUMERIC(5,6) Q ALTER TABLE [Production].[Product] ADD [User_Data_1] MONEY

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

Correct Answer: C


QUESTION 72

You have a database that uses stored procedures to perform INSERT, UPDATE, DELETE, and SELECT statements.

You are tasked with providing a recommendation of indexes to be created and dropped from the database. You need to select the appropriate method to accomplish the task. Which method should you use?

A. SQL Server Profiler

B. Database Engine Tuning Advisor

C. Index Usage DMVs

D. Missing Index DMVs

Correct Answer: B


QUESTION 73

You have an application that is used by international clients. All clients connect by using Windows Authentication. You need to ensure that system and user-defined error messages are displayed in the localized language for the clients.

What should you do? (Each correct answer represents part of the solution. Choose two.)

A. Use @@LANGUAGE function

B. Use default language for each login

C. Use @lang parameter of sp_addmessage

D. Use the "set language" option of sp_configure

Correct Answer: BC


QUESTION 74

You are a database developer working on an application hosted on Microsoft SQL Server 2008 R2. The application regularly imports employee data from XML files. These files are bulk-loaded into the CompanyDoc column of the CompanyXML table. The table the following definition: CREATE

TABLE CompanyXML(CompanyXMLID int IDENTITY PRIMARY KEY NOT NULL, CompanyDoc xml NOT NULL) One of the XML files loaded into this table has the following code fragment.

<Company><Employee Name = "Michaeline" Department = "Finance"/><Employee Name = "Chad" Department = "Facilities"/>

…<Employee Name = "Shelly" Department = "Human Resources"/></Company> You need to be able to return the data as shown in the following table:

Name Department Michael Finance John Facilities David

Human Resources

Which Transact-SQL statements should you use?

Exhibit:

clip_image001

A. SELECT Name = Company.Employee.query(‘@Name’,’varchar(20)’), Department = Company.

Employee.query(‘@Department’,’varchar(20)’) FROM CompanyXML c

CROSS APPLY CompanyDoc.nodes(‘/Company/Employee’) Company(Employee)

B. DECLARE @xmlid int

DECLARE cur_xml CURSOR FAST_FORWARD FOR

SELECT CompanyXMLID

FROM CompanyXMLOPEN cur_xml

FETCH NEXT FROM cur_xml INTO @xmlid

WHILE @@FETCH_STATUS = 0 BEGIN

SELECT Name = Company.Employee.value(‘@Name’,’varchar(20)’),Department = Company. Employee.value(‘@Department’,’varchar(20)’)

FROM CompanyXML

WHERE CompanyXMLID = @xmlid

FETCH NEXT FROM cur_xml INTO

@xmlid END

CLOSE cur_xml DEALLOCATE cur_xml

C. SELECT Name = Company.Employee.value(‘@Name’,’varchar(20)’), Department = Company.

Employee.value(‘@Department’,’varchar(20)’) FROM CompanyXML c

CROSS APPLY CompanyDoc.nodes(‘/Company/Employee’) Company(Employee)

D. DECLARE @xml xml

DECLARE @results table (Name varchar(20), Department varchar(20))

DECLARE cur_xml CURSOR FAST_FORWARD FOR

SELECT CompanyDoc FROM CompanyXML

OPEN cur_xml

FETCH NEXT FROM cur_xml INTO @xml

WHILE @@FETCH_STATUS = 0 BEGIN

INSERT @results

SELECT Name = Company.Employee.query(‘@Name’,’varchar(20)’), Department = Company. Employee.query(‘@Department’,’varchar(20)’)

FROM @xml.nodes(‘/Company/Employee’) Company(Employee)

FETCH NEXT FROM cur_xml INTO @xml

END

CLOSE cur_xml DEALLOCATE cur_xml

SELECT * FROM @results

E. DECLARE @xmlid int

DECLARE cur_xml CURSOR FAST_FORWARD FOR

SELECT CompanyXMLID FROM CompanyXML

OPEN cur_xml

FETCH NEXT FROM cur_xml INTO @xmlid

WHILE @@FETCH_STATUS = 0 BEGIN

SELECT Name = Company.Employee.query(‘@Name’,’varchar(20)’), Department = Company. Employee.query(‘@Department’,’varchar(20)’)

FROM CompanyXML

WHERE CompanyXMLID = @xmlid

FETCH NEXT FROM cur_xml INTO @xmlid

END

CLOSE cur_xml DEALLOCATE cur_xml

F. DECLARE @xml xml DECLARE @results table (Name varchar(20),

Department varchar(20))

DECLARE cur_xml CURSOR FAST_FORWARD FOR

SELECT CompanyDoc FROM CompanyXML

OPEN cur_xml

FETCH NEXT FROM cur_xml INTO @xml

WHILE @@FETCH_STATUS = 0 BEGIN

INSERT @results

SELECT Name = @xml.value(‘/Company[1]/Employee[1]/@Name’, ‘varchar(20)’), Department =

@xml.value(‘/Company[1]/Employee[1]/@Department’, ‘varchar(20)’)

FETCH NEXT FROM cur_xml INTO @xml

END

CLOSE cur_xml DEALLOCATE cur_xml

SELECT * FROM @results

G. DECLARE @xml xml

DECLARE @results table (Name varchar(20), Department varchar(20))

DECLARE cur_xml CURSOR FAST_FORWARD FOR

SELECT CompanyDoc FROM CompanyXML

OPEN cur_xml

FETCH NEXT FROM cur_xml INTO @xml

WHILE @@FETCH_STATUS = 0 BEGIN

INSERT @results

SELECT Name = @xml.value(‘/Company/Employee/@Name’, ‘varchar(20)’), Department = @xml. value(‘/Company/Employee/@Department’, ‘varchar(20)’)

FETCH NEXT FROM cur_xml INTO @xml

END

CLOSE cur_xml DEALLOCATE cur_xml

SELECT * FROM @results

H. DECLARE @xml xml DECLARE @results table (Name varchar(20), Department varchar(20))

DECLARE cur_xml CURSOR FAST_FORWARD FOR

SELECT CompanyDoc FROM CompanyXML

OPEN cur_xml

FETCH NEXT FROM cur_xml INTO @xml

WHILE @@FETCH_STATUS = 0 BEGIN

INSERT @results

SELECT Name = Company.Employee.value(‘@Name’,’varchar(20)’), Department = Company. Employee.value(‘@Department’,’varchar(20)’)

FROM @xml.nodes(‘/Company/Employee’) Company(Employee)

FETCH NEXT FROM cur_xml INTO @xml

END

CLOSE cur_xml DEALLOCATE cur_xml

SELECT * FROM @results

Correct Answer: CH


QUESTION 75

You have implemented change tracking on a table named Sales.SalesOrder. You need to

determine all columns that have changed since the minimum valid version. Which function should you use?

A. CHANGETABLE with the VERSION argument

B. CHANGETABLE with the CHANGES argument

C. CHANGE_TRACKING_CURRENT_VERSION

D. CHANGE_TRACKING_IS_COLUMN_IN_MASK

Correct Answer: B


QUESTION 76

You administer a Microsoft SQL Server 2008 database that contains tables named Sales.Customer and Sales.SalesOrder.

A diagram of the tables is shown in the exhibit. (Click the Exhibit button.) You need to execute a query to update the value of the CustomerValue field to HV when a customer has more than 5 orders for a total sales amount of more than 5,000 U.S. dollars. Which Transact-SQL statement should you use?

Exhibit:

clip_image002

A. UPDATE Sales.Customer SET CustomerValue = ‘HV’ FROMSales.Customer WHERE SalesAmount > 5000

AND CustomerID IN (SELECT c.CustomerID

FROM Sales.Customer c

INNER JOIN Sales.SalesOrder o ON o.CustomerID=c.CustomerID GROUP BY c.CustomerID

HAVING COUNT(*) > 5)

B. UPDATE Sales.Customer SET CustomerValue = ‘HV’ FROM Sales.Customer

WHERE CustomerID IN (SELECT c.CustomerID

FROM Sales.Customer c

INNER JOIN Sales.SalesOrder o ON o.CustomerID=c.CustomerID GROUP BY c.CustomerID

HAVING COUNT(*) > 5 AND SUM(SalesAmount) > 5000)

C. UPDATE c

SET CustomerValue = ‘HV’ FROM Sales.SalesOrder o

INNER JOIN Sales.Customer c ON c.CustomerID=o.CustomerID GROUP BY c.CustomerID

HAVING COUNT(*) > 5 AND SUM(SalesAmount) > 5000

D. UPDATE c

SET CustomerValue = ‘HV’ FROM Sales.SalesOrder o

INNER JOIN Sales.Customer c ON c.CustomerID=o.CustomerID WHERE SalesAmount > 5000

GROUP BY c.CustomerID HAVING COUNT(*) > 5

E. UPDATE u

SET CustomerValue = ‘HV’ FROM Sales.Customer u

WHERE EXISTS(SELECT c.CustomerID

FROMSales.Customer c

INNER JOIN Sales.SalesOrder o ON o.CustomerID=c.CustomerID WHERE c.CustomerID=u.CustomerID

GROUP BY c.CustomerID

HAVING COUNT(*) > 5 AND SUM(SalesAmount) > 5000)

F. UPDATE u

SET CustomerValue = ‘HV’ FROM Sales.Customer u WHERE SalesAmount > 5000

AND EXISTS(SELECT c.CustomerID

FROM Sales.Customer c

INNER JOIN Sales.SalesOrder o ON o.CustomerID=c.CustomerID WHERE c.CustomerID=u.CustomerID

GROUP BY c.CustomerID HAVING COUNT(*) > 5)

G. UPDATE Sales.Customer SET CustomerValue = ‘HV’ FROM Sales.SalesOrder o

INNER JOIN Sales.Customer c ON c.CustomerID=o.CustomerID GROUP BY c.CustomerID

HAVING COUNT(*) > 5 AND SUM(SalesAmount) > 5000

H. UPDATE Sales.Customer SET CustomerValue = ‘HV’ FROM Sales.SalesOrder o

INNER JOIN Sales.Customer c ON c.CustomerID=o.CustomerID WHERE SalesAmount > 5000

GROUP BY c.CustomerID HAVING COUNT(*) > 5

Correct Answer: BE


QUESTION 77

© 2012 Microsoft Corporation. All rights reserved. WelcomeWelcome to this Microsoft Certification exam. The following information is provided to help you use your exam time most efficiently.

Exam Format and
QUESTION FormatsThis exam session includes multiple sections. You will see introductory sections, the exam content, and post-exam sections. This exam might contain several
QUESTION formats. The Instructions button located at the bottom of each
QUESTION screen provides information on the
QUESTION format shown.

T imingThe amount of time that you have to complete this exam was provided on the Welcome Screen.

The amount of time you have remaining in each section is provided in the upper-right corner of each screen. Any delays, such as the loading of the next screen, will not be counted against your exam time.

Number of Questions The total number of questions on this exam is provided on the “Get Ready to Take the Exam!” screen. The number of questions remaining in each section is provided in the upper-left corner of each question.

Reviewing/CommentingThe Mark for review or comment check box located in the upper-left corner of each
QUESTION screen can be used to flag a
QUESTION that you want to review. At the end of this exam, you will see a screen that shows you which questions you have marked for review. The Mark for review or comment check box can also be used to flag a
QUESTION for which you want to provide a comment. Note that you cannot make comments during the exam. The comment period is timed separately; any time you spend making comments does not affect the time limit

for the exam itself.

Important Changes to Your Exam Experience ScrollingYou will no longer be notified that you have not viewed the entire
QUESTION when you move to another question. In other words, you will not

be prompted to scroll to the end of a
QUESTION before you move to the next one. Keyboard ShortcutsFunctionality, such as keyboard shortcuts, can be used during the exam. Additional information about the keyboard controls that are available within the exam can be found by

clicking the Help button associated with each question. Case Studies This exam might contain case studies. We no longer time each case study separately. In other words, the amount of time you are given to complete this exam can be used at your discretion. You can spend as much time as you like on any given case or question, but be sure to manage your time accordingly.

A. This is the correct answer

Correct Answer: A


QUESTION 78

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:

·Each customer name

·Each 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 JOIN customer 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. 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

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

F. 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);

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

H. 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;

I. 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;

J. 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), ());

Correct Answer: J


QUESTION 79

You have a table named Employee. You document your company’s organizational hierarchy by inserting the EmployeeID of each employee’s manager in the ReportsTo column. You need to write a recursive query that produces a list of employees and their manager. The query must also include the employee’s level in the hierarchy. You write the following code segment. (Line numbers are included for reference only.) 01 WITH EmployeeList (EmployeeID, FullName, ManagerName, Level)02 AS (03 04 )05 SELECT EmployeeID, FullName, ManagerName, Level06 FROM EmployeeList;

Which code segment should you insert at line 3?

A. SELECT EmployeeID,FullName,” AS [Reports To],1 AS [Level] FROM Employee

UNION ALL

SELECT emp.EmployeeID,emp.FullName,mgr.FullName,1 + 1 AS [Level] FROM Employee emp

LEFT JOIN Employee mgrON emp.ReportsTo = mgr.EmployeeID

B. SELECT EmployeeID,FullName,” AS [ReportsTo],1 AS [Level] FROM Employee

WHERE ReportsTo IS NULL UNION ALL

SELECT emp.EmployeeID,emp.FullName,mgr.FullName,mgr.Level + 1 FROM EmployeeList mgr

JOIN Employee emp ON emp.ReportsTo = mgr.EmployeeId

C. SELECT EmployeeID,FullName,” AS [ReportsTo],1 AS [Level] FROM Employee

UNION ALL

SELECT emp.EmployeeID,emp.FullName,mgr.FullName,mgr.Level + 1 FROM EmployeeList mgr

JOIN Employee emp ON emp.ReportsTo = mgr.EmployeeID

D. SELECT EmployeeID,FullName,” AS [ReportsTo],1 AS [Level] FROM Employee

WHERE ReportsTo IS NULL UNION ALL

SELECT emp.EmployeeID,emp.FullNName,mgr.FullName,1 + 1 AS [Level]

FROM Employee emp

JOIN Employee mgrON emp.ReportsTo = mgr.EmployeeID

Correct Answer: B


QUESTION 80

You are a developer for a Microsoft SQL Server 2008 R2 database instance. You plan to add functionality to an existing application that uses the Microsoft SQL Server Profiler tool to capture trace information. You need to ensure that the following requirements are met:

·Users are able to use the new application functionality.

·Users are able to capture trace information and read saved trace files.

·Users are granted the minimum permissions to achieve these goals. Which three 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


 

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