Latest 70-433 Real Exam Download 1-10

Ensurepass

QUESTION 1

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 Unicode string values that have a minimum length of two characters and a maximum length of 100 characters.

Which SQL statement should you use?

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Correct Answer: J

QUESTION 2

You are the database developer for a Microsoft SQL Server 2008 database that contains tables named order and product.

The tables have the following definitions: CREATE TABLE [order]

(OrderID INT, ProductID INT, CustomerID INT,

OrderDate DATETIME); CREATE TABLE product (ProductID INT,

ProductName VARCHAR(100), SalePrice money,

ManufacturerName VARCHAR(100)); You need to write a query that will extract a valid XML result set of all ordered products. You also need to ensure that the query conforms to the following schema: <?xml version="1.0" encoding="utf-16"?><xsd:schema attributeFormDefault="unqualified" elementFormDefault="qualified" version="1.0" xmlns:xsd="http://www.w3.org/2001/XMLSchema">

<xsd:element name="OrderedProducts">

<xsd:complexType>

<xsd:sequence>

<xsd:element name="ProductID" type="xsd:int" />

<xsd:element name="ProductName" type="xsd:string" />

<xsd:element name="SalePrice" type="xsd:decimal" />

<xsd:element name="ManufacturerName" type="xsd:string" />

<xsd:element name="OrderDate" type="xsd:dateT ime" />

</xsd:sequence>

</xsd:complexType>

</xsd:element></xsd:schema>

Which SQL query should you use? (Choose Two – complete answers)

A. SELECTp.ProductID,p.ProductName,p.SalePrice,p.ManufacturerName,o.OrderDate FROM product p

INNER JOIN[order] o ON p.ProductID = o.ProductID FOR XML AUTO (‘OrderedProducts’);

B. SELECTp.ProductID,p.ProductName,p.SalePrice,p.ManufacturerName,o.OrderDate FROM product p

INNER JOIN[order] o ON p.ProductID = o.ProductID FOR XML AUTO;

C. SELECTp.ProductID,p.ProductName,p.SalePrice,p.ManufacturerName,o.OrderDate FROM product p

INNER JOIN[order] o ON p.ProductID = o.ProductID FOR XML PATH (‘OrderedProducts’);

D. SELECT'<OrderedProducts>’,p.ProductID,p.ProductName,p.SalePrice,p.ManufacturerName, o.OrderDate,'</OrderedProducts>’

FROM product p

INNER JOIN[order] o ON p.ProductID = o.ProductID FOR XML PATH;

E. SELECT1 as Tag,0 as Parent,p.ProductID as [OrderedProducts!1!ProductID!ELEMENT],p.ProductName as [OrderedProducts!1!ProductName!ELEMENT],p.SalePrice as [OrderedProducts!1!SalePrice!ELEMENT],p.ManufacturerName as [OrderedProducts!1!ManufacturerName!ELEMENT],o.OrderDate as [OrderedProducts!1!OrderDate!ELEMENT]

FROM product p

INNER JOIN[order] o ON p.ProductID = o.ProductID FOR XML EXPLICIT;

F. SELECT1 as Tag,0 as Parent,p.ProductID as [OrderedProducts!1!ProductID!ELEMENT],p.ProductName as [OrderedProducts!1!ProductName!ELEMENT],p.SalePrice as [OrderedProducts!1!SalePrice!ELEMENT],p.ManufacturerName as [OrderedProducts!1!ManufacturerName!ELEMENT],o.OrderDate as [OrderedProducts!1!OrderDate!ELEMENT]

FROM product p

INNER JOIN[order] o ON p.ProductID = o.ProductID FOR XML EXPLICIT (‘OrderedProducts’);

G. SELECT p.ProductID,p.ProductName,p.SalePrice,p.ManufacturerName,o.OrderDate FROM product p

INNER JOIN [order] o ON p.ProductID = o.ProductID FOR XML RAW;

H. SELECTp.ProductID,p.ProductName,p.SalePrice,p.ManufacturerName,o.OrderDate

FROM product p

INNER JOIN[order] o ON p.ProductID = o.ProductID FOR XML RAW (‘OrderedProducts’);

Correct Answer: CE

QUESTION 3

You are a database developer writing reports for a sales management application. A customer table has the following definition: CREATE TABLE customer

(CustomerID INT, FirstName VARCHAR(30), LastName VARCHAR(50), StreetAddress VARCHAR(100), City VARCHAR(100),

[State] VARCHAR(25),

PostalCode VARCHAR(5)); An order table has the following definition: CREATE TABLE [order] (OrderID INT,

ProductID INT, CustomerID INT,

OrderDate DATETIME); You need to write a report that contains the following columns:

Column name Description CustomerID

The CustomerID FullName

Concatenated first and last names Postalcode

Customer’s Postal Code OrderCount

Number of orders for this customer EarliestOrderDate

The OrderDate from the earliest (by OrderDate) order for this customer

You also need to ensure that the report meets the following requirements:

·Contains only customers who have placed orders

·Contains only customers who have Postal Codes beginning with 89

·Returns only one record for each customer

·The report is ordered by the CustomerID in ascending order

Which Transact-SQL query should you use? (Choose 2 – Complete)

A. SELECT c.CustomerID,c.FirstName + ‘ ‘ + c.LastName AS FullName,c.PostalCode,COUNT(*) AS OrderCount,MIN(o.OrderDate) AS EarliestOrderDate

FROM Customer c

INNER JOIN [order] o ON CustomerId = o.CustomerId WHEREc.PostalCode LIKE ‘89%’

GROUP BY c.CustomerId, c.FirstName + ‘ ‘ + c.LastName, c.PostalCode, o.CustomerId ORDER BY c.CustomerID

B. SELECT c.CustomerID,c.FirstName + ‘ ‘ + c.LastName AS FullName,c.PostalCode,COUNT(*) AS OrderCount,MIN(o.OrderDate) AS EarliestOrderDate

FROM Customer c

INNER JOIN [order] o ON CustomerId = o.CustomerId WHERE c.PostalCode = ‘89%’

GROUP BY c.CustomerId, c.FirstName + ‘ ‘ + c.LastName, c.PostalCode, o.CustomerId ORDER BY FullName

C. WITH FullNames AS

(SELECT CustomerId,FirstName + ‘ ‘ + LastName AS FullName FROM customer)

SELECT c.CustomerID,fn.FullName,c.PostalCode,COUNT(*) AS OrderCount,MIN(o.OrderDate) AS EarliestOrderDate

FROM Customer c

INNER JOIN FullNames fn ON fn.CustomerID = c.CustomerID INNER JOIN [order] o ON c.CustomerId = o.CustomerId WHERE CustomerID PostalCode LIKE ‘89%’

GROUP BY c.CustomerId, fn.FullName, c.PostalCode, o.CustomerId ORDER BY c.

D. SELECT c.CustomerID,c.FirstName + ‘ ‘ + c.LastName AS FullName,c.PostalCode,COUNT(*) AS OrderCount,MIN(o.OrderDate) AS EarliestOrderDate

FROM Customer c

LEFT OUTER JOIN [order] o ON c.CustomerId = o.CustomerId WHERE c.PostalCode LIKE ‘89%’

GROUP BY c.CustomerId, FirstName + ‘ ‘ + c.LastName, c.PostalCode, o.CustomerId ORDER BY c.CustomerID

USING FullNames AS

( SELECTCustomerId,FirstName + ‘ ‘ + LastName AS FullName FROM customer)

SELECT c.CustomerID,fn.FullName,c.PostalCode,COUNT(*) AS OrderCount,MIN(o.OrderDate) AS EarliestOrderDate

FROM Customer c

INNER JOIN FullNames fn ON fn.CustomerID = c.CustomerID INNER JOIN [order] o ON c.CustomerId = o.CustomerId WHERE PostalCode LIKE ‘89%’

GROUP BY c.CustomerId, fn.FullName, c.PostalCode, o.CustomerId ORDER BY c.CustomerID

E. SELECT c.CustomerID,c.FirstName & ‘ ‘ & c.LastName AS FullName,c.PostalCode,COUNT(*) AS OrderCount,MIN(o.OrderDate) AS EarliestOrderDate

FROM Customer c

INNER JOIN [order] o ON CustomerId = o.CustomerId WHERE c.PostalCode LIKE ‘89%’

GROUP BY c.CustomerId, FirstName & ‘ ‘ & c.LastName, c.PostalCode, o.CustomerId ORDER BY c.CustomerID

F. SELECT c.CustomerID,c.FirstName + ‘ ‘ + c.LastName AS FullName,c.PostalCode,COUNT(*) AS OrderCount,MIN(o.OrderDate) AS EarliestOrderDate

FROM Customer c

INNER JOIN [order] o ON CustomerID CustomerId = o.CustomerId WHEREc.PostalCode LIKE ‘89%’

ORDER BY c.

G. WITH FullNames AS

( SELECTCustomerId,FirstName + ‘ ‘ + LastName AS FullNam FROM customer) SELECTc.CustomerID,fn.FullName,c.PostalCode,COUNT(*) AS OrderCount,MIN(o.OrderDate) AS EarliestOrderDate

FROM Customer c

INNER JOIN FullNames fn ON fn.CustomerID = c.CustomerID INNER JOIN [order] o ON c.CustomerId = o.CustomerId

GROUP BY c.CustomerId, fn.FullName, c.PostalCode, o.CustomerId ORDER BY c.CustomerId

Correct Answer: AC

QUESTION 4

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 named AdventureWorks that contains a table named Production.Product. The table contains a primary key named PK_Product_ProductID and a

non-clustered index named AK_Product_ProductNumber. Both indexes have been created on a single primary partition.

The table 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, [ModifiedDate] [datetime] NOT NULL,

CONSTRAINT [PK_Product_ProductID] PRIMARY KEY CLUSTERED (

[ProductID] ASC

) ON [PRIMARY]) ON [PRIMARY]GO The index has the following definition: CREATE UNIQUE NONCLUSTERED INDEX [AK_Product_ProductNumber] ON [Production].[Product] ( [ProductNumber] ASC) ON [PRIMARY]GO The Production.Product table contains 1 million rows. You want to ensure that data retrieval takes the minimum amount of time when the queries executed against the Production.Product table are ordered by product number or filtered by class.

You need to find out the degree of fragmentation for the indexes on the Production.Product table.

Which Transact-SQL statement should you use?

A. ALTER STATISTICS Production.Product

B. SELECT * FROM sys.indexes where name=N’Production.Product’

C. CREATE STATISTICS ProductClass_Stats ON Production.Product (Name, ProductNumber, Class)WHERE Class is not null

D. SELECT * FROM sys.dm_db_index_physical_stats (DB_ID(), OBJECT_ID(N’Production.Product’),NULL, NULL, NULL)

E. SELECT * FROM STATS WHERE name=’AK_Product_ProductNumber’

F. DBCC SHOW_STATISTICS (‘Production.Product’, AK_Product_ProductNumber)

G. UPDATE INDEX AK_Product_ProductNumber ON Production.Product SET (STATISTICS_NORECOMPUTE = ON)

H. ALTER INDEX AK_Product_ProductNumber ON Production.Product REBUILD

I. SELECT * FROM sys.dm_db_index_operational_stats (DB_ID(), OBJECT_ID(N’Production.Product’),NULL, NULL)

J. SELECT * FROM SYS.STATS WHERE name=’AK_Product_ProductNumber’

K. EXEC sys.sp_configure ‘index create memory’, 1

L. CREATE STATS ProductClass_StatsON Production.Product (Name, ProductNumber, Class)WHERE Class is not nullWITH SAMPLE 100 PERCENT

M. ALTER DATABASE [AdventureWorks] SET AUTO_UPDATE_STATISTICS ON

N. ALTER INDEX AK_Product_ProductNumber ON Production.Product REBUILD Partition = 1

O. UPDATE STATISTICS Production.Product

P. ALTER INDEX AK_Product_ProductNumber ON Production.Product REORGANIZE

Q CREATE STATISTICS ProductClass_StatsON Production.Product (Name, ProductNumber, Class)WHERE Class <> null

Correct Answer: D

QUESTION 5

A table named Contacts includes a column named SmtpAddress. You must develop a report that returns e-mail addresses from the Contacts table that have the following format: at least one character, the at sign (@), at least one character, and then ".org". You need to return data that meets the requirements. Which Transact-SQL statement should you use?

A. select * from Contacts where SmtpAddress like ‘%@%.org’

B. select * from Contacts where SmtpAddress like ‘_%@_%.org’

C. select * from Contacts where SmtpAddress like ‘_%@_.org’

D. select * from Contacts where SmtpAddress like ‘%@%[.]org’

Correct Answer: B

QUESTION 6

You have a column named TelephoneNumber that stores numbers as varchar(20). You need to write a query that returns the first three characters of a telephone number. Which expression should you use?

A. CHARINDEX(‘[0-9][0-9][0-9]’, TelephoneNumber, 3)

B. SUBSTRING(TelephoneNumber, 3, 3)

C. SUBSTRING(TelephoneNumber, 3, 1)

D. LEFT(TelephoneNumber, 3)

Correct Answer: D

QUESTION 7

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 named AdventureWorks that contains a table named Production.Product. The table contains a primary key named PK_Product_ProductID and a

non-clustered index named AK_Product_ProductNumber. Both indexes have been created on a single primary partition. The table 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, [ModifiedDate] [datetime] NOT NULL,

CONSTRAINT [PK_Product_ProductID] PRIMARY KEY CLUSTERED (

[ProductID] ASC

) ON [PRIMARY]) ON [PRIMARY]GO The index has the following definition: CREATE UNIQUE NONCLUSTERED INDEX [AK_Product_ProductNumber] ON [Production].[Product] ( [ProductNumber] ASC) ON [PRIMARY]GO The Production.Product table contains 1 million rows. You want to ensure that data retrieval takes the minimum amount of time when the queries executed against the Production.Product table are ordered by product number or filtered by class. You need to build new query optimization statistics on the Production.Product table to support queries that filter data rows where the class field has a null value. Which Transact-SQL statement should you use?

A. DBCC SHOW_STATISTICS (‘Production.Product’, AK_Product_ProductNumber)

B. EXEC sys.sp_configure ‘index create memory’, 1

C. ALTER INDEX AK_Product_ProductNumber ON Production.Product REORGANIZE

D. ALTER INDEX AK_Product_ProductNumber ON Production.Product REBUILD Partition = 1

E. CREATE STATS ProductClass_StatsON Production.Product (Name, ProductNumber, Class)WHERE Class is not nullWITH SAMPLE 100 PERCENT

F. SELECT * FROM sys.dm_db_index_physical_stats (DB_ID(), OBJECT_ID(N’Production.Product’),NULL, NULL, NULL)

G. UPDATE INDEX AK_Product_ProductNumber ON Production.Product SET (STATISTICS_NORECOMPUTE = ON)

H. UPDATE STATISTICS Production.Product

I. CREATE STATISTICS ProductClass_StatsON Production.Product (Name, ProductNumber, Class)WHERE Class <> null

J. SELECT * FROM sys.dm_db_index_operational_stats (DB_ID(), OBJECT_ID(N’Production.Product’),NULL, NULL)

K. SELECT * FROM SYS.STATS WHERE name=’AK_Product_ProductNumber’

L. ALTER INDEX AK_Product_ProductNumber ON Production.Product REBUILD

M. ALTER STATISTICS Production.Product

N. CREATE STATISTICS ProductClass_StatsON Production.Product (Name, ProductNumber, Class)WHERE Class is not null

O. SELECT * FROM sys.indexes where name=N’Production.Product’

P. ALTER DATABASE [AdventureWorks] SET AUTO_UPDATE_STATISTICS ON

Q SELECT * FROM STATS WHERE name=’AK_Product_ProductNumber’

Correct Answer: N

QUESTION 8

You are using SQL Server Profiler to gather deadlock information. You need to capture an XML description of a deadlock. Which event should you use?

A. Showplan XML

B. Lock:Deadlock

C. Deadlock Graph

D. Lock:Deadlock Chain

Correct Answer: C

QUESTION 9

You develop a new stored procedure for an existing database. You create two tables named Customer and Orders.

The tables have the following definitions: CREATE TABLE Customer (CustomerID int NOT NULL PRIMARY KEY CLUSTERED,

CustomerName nvarchar(255) NOT NULL,

CustomerAddress nvarchar (1024) NOT NULL) CREATE TABLE Orders (OrderID int NOT NULL PRIMARY KEY CLUSTERED,

CustomerID int NOT NULL FOREIGN KEY REFERENCES Customer(CustomerID),

OrderDetails nvarchar(MAX)) Users are restricted from accessing table objects directly. You need to ensure that users are able to retrieve customer data. You need to create a stored procedure that meets the following requirements:

·Returns a row that contains the name, address, and number of orders made by a customer by specifying the CustomerID value.

·Returns a row even if the customer has made no orders.

·Does not return a row if the CustomerID does not exist. Which Transact-SQL statement or statements should you use?

A. INSERT INTO Tickets VALUES (4, ‘sales’, ‘open’, 0),

(5, ‘support’, ‘open’, 0),

(6, ‘support’, ‘open’,1)

B. UPDATE Tickets SET IsArchived = 1 WHERE TicketId IN (1, 2, 3)

C. CREATE PROCEDURE p_GetTotalOrdersByCustomer (@customerid int) AS

SELECT c.CustomerName, c.CustomerAddress, TotalOrders = COUNT(o.OrderID) FROM Customer c

INNER JOIN Orders oON c.CustomerID = o.CustomerID GROUP BY c.CustomerName, c.CustomerAddress WHERE c.CustomerID = @customerid GO

D. CREATE PROCEDURE p_GetTotalOrdersByCustomer (@customerid int) AS

SELECT c.CustomerName, c.CustomerAddress,TotalOrders = SUM(o.OrderID) FROM Customer c

INNER JOIN Orders o ON c.CustomerID = o.CustomerID GROUP BY c.CustomerName, c.CustomerAddress

GO

E. INSERT INTO SupportTickets VALUES (4, ‘support’, ‘open’, 0),

(5, ‘support’, ‘in progress’, 0),

(6, ‘support’, ‘closed’, 0)

F. INSERT INTO Tickets

VALUES (4, ‘support’, ‘open’, 0),

(5, ‘support’, ‘in progress’, 0),

(6, ‘support’, ‘closed’, 0)

G. CREATE PROCEDURE p_GetTotalOrdersByCustomer (@customerid int) AS

SELECT c.CustomerName, c.CustomerAddress, TotalOrders = ISNULL(o.TotalOrders, 0) FROM Customer c

INNER JOIN (SELECT CustomerID, TotalOrders = COUNT(OrderID) FROM Orders

WHERE CustomerID = @customerid

GROUP BY CustomerID) o ON c.CustomerID = o.CustomerID

WHERE c.CustomerID = @customerid GO , c.CustomerAddress,TotalOrders = SUM(o.OrderID) FROM Customer c

INNER JOIN Orders o ON c.CustomerID = o.CustomerID GROUP BY c.CustomerName, c.CustomerAddress

GO

H. CREATE PROCEDURE p_GetTotalOrdersByCustomer (@customerid int)

AS SELECT c.CustomerName, c.CustomerAddress, TotalOrders = ISNULL(o.TotalOrders, 0) FROM Customer

Correct Answer: BF

QUESTION 10

You administer a Microsoft SQL Server 2008 instance that has two databases. The first database named AdventureWorks contains a table named Sales.SalesOrders.

The Sales.SalesOrders table has the following definition: CREATE TABLE [Sales].[SalesOrderDetail]( [SalesOrderDetailID] [int] IDENTITY(1,1) NOT NULL,

[ProductID] [int] NOT NULL, [OrderQty] [smallint] NOT NULL, [OrderDate] [datetime] NOT NULL,

CONSTRAINT [PK_SalesOrderDetail] PRIMARY KEY CLUSTERED (

[SalesOrderDetailID] ASC

)) ON [PRIMARY] The second database named AdventureWorksDW contains a table named dbo.SalesOrderSummary.

The dbo.SalesOrderSummary table has the following definition: CREATE TABLE [dbo].[SalesOrderSummary] (

ProductID [int] NOT NULL, OrderQty [int] NOT NULL, OrderYear [int] NOT NULL,

CONSTRAINT [PK_SalesOrderSummary] PRIMARY KEY CLUSTERED (

OrderYear ASC, ProductID ASC

)) ON [PRIMARY] You plan to migrate sales data for the year 2011 from the SalesOrderDetail table into the SalesOrderSummary table. You need to ensure that the following requirements are met:

·All data is removed from the SalesOrderSummary table before migrating data.

·A subset of data is migrated from the SalesOrderDetail table in the AdventureWorks database to the SalesOrderSummary table in the AdventureWorksDW database.

·Migrated data summarizes order quantity in one row per product for the year 2011. 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

 

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