Latest 70-433 Real Exam Download 111-120

Ensurepass

QUESTION 111

You have a table named Stores that has an XML column named OpenHours. This column contains the opening and closing times.

<hours dayofWeek="Monday" open="8:00" closed="18:00" />

<hours dayofWeek="Tuesday" open="8:00" closed="18:00" />

<hours dayofWeek="Saturday" open="8:00" closed="18:00" /> You need to write a query that returns a list of stores and their opening time for a specified day. Which code segment should you use?

A. DECLARE @Day VARCHAR(10) = ‘Tuesday’

SELECT StoreName,OpenHours.value(‘/hours[1][@dayofWeek=sql:variable("@Day")]/@open’,’time’) FROM Stores

B. DECLARE @Day VARCHAR(10) = ‘Tuesday’

SELECT StoreName,OpenHours.value(‘/hours[1]/@open’,’time’)

FROM Stores

WHERE OpenHours.exist(‘/hours[@dayofWeek=sql:variable("@Day")]’) = 1

C. DECLARE @Day VARCHAR(10) = ‘Tuesday’

SELECT Storename,OpenHours.query(‘data(/hours[@dayofWeek=sql:variable("@Day")]/@open)’) FROM Stores

D. DECLARE @Day VARCHAR(10) = ‘Tuesday’

SELECT StoreName,OpenHours.value(‘/hours[1]/@open’,’time’) FROM Stores

WHERE OpenHours.value(‘/hours[1]/@dayofWeek’,’varchar(20)’) = @Day

Correct Answer: C


QUESTION 112

You administer a Microsoft SQL Server 2008 database that contains a table named dbo.SalesOrders. The table has the following definition: CREATE TABLE [dbo].[SalesOrder]( [SalesOrderNumber] NVARCHAR(20) NOT NULL,

[FullDateAlternateKey] DATETIME NOT NULL, [CustomerName] NVARCHAR(100) NOT NULL, [AddressLine] NVARCHAR(120) NOT NULL, [City] NVARCHAR(30) NOT NULL,

[StateProvinceName] NVARCHAR(50) NOT NULL, [CountryName] NVARCHAR(50) NOT NULL,

[SalesAmount] MONEY NOT NULL,

CONSTRAINT [PK_SalesOrderNumber] PRIMARY KEY CLUSTERED (

[SalesOrderNumber] ASC) ON [PRIMARY]) ON [PRIMARY]GO The SalesOrder table contains one million rows. You want to create a report that meets the following requirements:

·Only the states of the Unites States are ranked against each other based on the total number of orders received from each state.

·When two states have the same rank, the rank of the subsequent state is one plus the number of ranks that come before that row, as shown in the exhibit. (Click the Exhibit button.) You need to

execute a Transact-SQL query to generate the report. Which Transact-SQL query should you use?

Exhibit:

clip_image001

A. SELECT RANK() OVER (ORDER BY StateProvinceName DESC) AS

Ranking,StateProvinceName,TotalOrders FROM

(SELECT StateProvinceName,count(*) AS TotalOrders FROM dbo.SalesOrder

WHERE CountryName=’United States’ GROUP BY StateProvinceName) AS

B. SELECT DENSE_RANK() OVER (ORDER BY StateProvinceName DESC) AS

Ranking,StateProvinceName,TotalOrders FROM

(SELECT StateProvinceName,count(*) AS TotalOrders

FROM dbo.SalesOrder

WHERE CountryName=’United States’ GROUP BY StateProvinceName) AS

C. SELECT DENSE_RANK() OVER (ORDER BY TotalOrders DESC) AS Ranking,StateProvinceName, TotalOrders

FROM

(SELECT StateProvinceName,count(*) AS TotalOrders FROM dbo.SalesOrder

WHERE CountryName=’United States’ GROUP BY StateProvinceName) AS

D. SELECT RANK() OVER (ORDER BY TotalOrders DESC) AS Ranking,StateProvinceName, TotalOrders

FROM

(SELECT StateProvinceName,count(*) AS TotalOrders FROM dbo.SalesOrder

WHERE CountryName=’United States’ GROUP BY StateProvinceName) AS

E. SELECT RANK() OVER (PARTITION BY CountryName ORDER BY TotalOrders DESC) AS Ranking,

StateProvinceName,TotalOrders FROM

(SELECT CountryName,StateProvinceName,count(*) AS TotalOrders FROM dbo.SalesOrder

GROUP BY CountryName,StateProvinceName) AS

F. SELECT DENSE_RANK() OVER (PARTITION BY CountryName ORDER BY TotalOrders DESC) AS

Ranking,StateProvinceName,TotalOrders FROM

(SELECT CountryName,StateProvinceName,count(*) AS TotalOrders FROM dbo.SalesOrder

GROUP BY CountryName,StateProvinceName) AS

G. SELECT RANK() OVER (PARTITION BY CountryName ORDER BY TotalOrders DESC) AS Ranking, StateProvinceName,TotalOrders

FROM

(SELECT CountryName,StateProvinceName,count(*) AS TotalOrders FROM dbo.SalesOrder

WHERE CountryName=’United States’

GROUP BY CountryName,StateProvinceName) AS

H. SELECTDENSE_RANK() OVER (PARTITION BY CountryName ORDER BY TotalOrders DESC) AS

Ranking,StateProvinceName,TotalOrdersFROM(SELECTCountryName,StateProvinceName,count(*) AS TotalOrdersFROMdbo.SalesOrderWHERECountryName=’United States’GROUP BYCountryName,StateProvinceName) AS

Correct Answer: DG


QUESTION 113

You have a table named Orders. You have been tasked to modify your company’s main database to remove all inactive order rows. You are developing a stored procedure that will enable you to delete these rows. You have written the following code segment to accomplish this task.

01 BEGIN TRY 02

DECLARE @RowCount INT = 1000 03

WHILE @RowCount = 1000 04

BEGIN 05

DELETE TOP (1000) FROM Orders WHERE Status = ‘Inactive’;06 SET @RowCount = @@ROWCOUNT 07

…08

END 09 END TRY 10 BEGIN CATCH 11 PRINT ERROR_MESSAGE()12 END CATCH

You need to insert a Transact-SQL statement that will notify you immediately after each batch of rows is deleted.

Which Transact-SQL statement should you insert at line 07?

A. RAISERROR (‘Deleted %i rows’, 11, 1, @RowCount) WITH NOWAIT

B. RAISERROR (‘Deleted %i rows’, 16, 1, @RowCount)

C. RAISERROR (‘Deleted %i rows’, 6, 1, @RowCount)

D. RAISERROR (‘Deleted %i rows’, 10, 1, @RowCount) WITH NOWAIT

Correct Answer: D


QUESTION 114

You administer a Microsoft SQL Server 2008 database that contains two tables named Products and Suppliers. You want to implement referential integrity between the Products and Suppliers tables. You want to create a new Foreign Key constraint on the Products table. The new Foreign Key constraint must meet the following requirements:

·It must be created on the SupplierID column on the Products table to refer to the SupplierID column on the Suppliers table.

·It must prevent the deletion of rows from the Suppliers table whenever an attempt is made to delete a Supplier by using a SupplierID referenced by Foreign Keys in existing rows in the Products table. You need to be able to create a new Foreign Key constraint by using Microsoft SQL Server Management Studio. What should you do? (To answer, configure the appropriate option or

options in the dialog box in the answer area.)

Work Area

Exhibit:

clip_image002

A. This is the answer

Correct Answer: A


QUESTION 115

You have the following xml: <Site URL="http://www.contoso.com/index.htm">

<Site URL="http://www.contoso.com/finance/index.htm">

<Site URL="http://www.contoso.com/finance/reports/index.htm" />

<Site URL="http://www.contoso.com/finance/main/index.htm" />

</Site>

<Site URL="http://www.contoso.com/marketing/index.htm">

<Site URL="http://www.contoso.com/marketing/reports/index.htm" />

<Site URL="http://www.contoso.com/marketing/main/index.htm" />

</Site>

<Site URL="http://www.contoso.com/sales/index.htm" /></Site> You are tasked to query the

sites listed in the xml by using OPENXML. The results will have two columns, ParentSiteURL and SiteURL. The ParentSiteURL column should contain the URL attribute of the parent site. The SiteURL column should contain the URL attribute of the site itself. The output should look like as shown in the following the exhibit. (Click the Exhibit button.) You need to write the OPENXML query. Which Transact-SQL statement should you use?

Exhibit:

clip_image003

A. SELECT ParentSiteURL, SiteURL

FROM OPENXML (@XMLDocHandle, ‘//@URL’, 1

)WITH (ParentSiteURL nVarChar(512) ‘../URL’,SiteURL nVarChar(512) ‘URL’)

B. SELECT ParentSiteURL, SiteURL

FROM OPENXML (@XMLDocHandle, ‘//URL’, 1)

WITH (ParentSiteURL nVarChar(512) ‘../@URL’,SiteURL nVarChar(512) ‘@URL’)

C. SELECT ParentSiteURL, SiteURL

FROM OPENXML (@XMLDocHandle, ‘//@Site’, 1)

WITH (ParentSiteURL nVarChar(512) ‘../URL’,SiteURL nVarChar(512) ‘URL’)

D. SELECT ParentSiteURL, SiteURL

FROM OPENXML (@XMLDocHandle, ‘//Site’, 1)

WITH (ParentSiteURL nVarChar(512) ‘../@URL’,SiteURL nVarChar(512) ‘@URL’)

Correct Answer: D


QUESTION 116

You create and populate two tables by using the following Transact-SQL statements: CREATE TABLE CurrentStudents (LastName VARCHAR(50),

FirstName VARCHAR(50), Address VARCHAR(100),

Age INT);INSERT INTO CurrentStudents VALUES (‘Fritz’, ‘David’, ‘181 Kline Street’, 14)

,(‘Reese’, ‘Paul’ , ‘4429 South Union’, 14)

,(‘Brown’, ‘Jake’ , ‘5401 Washington Ave’,14)

,(‘Smith’, ‘Tom’ , ‘124 Water St’, 14)

,(‘Holtz’, ‘Mary’ , ‘984 Mass Ct’, 14)

,(‘Robbins’, ‘Jan’ , ‘4449 Union Ave’, 14)

,(‘Larsen’, ‘Frank’ , ‘5812 Meadow St’, 14)

,(‘Bishop’, ‘Cathy’ , ‘14429 Skyhigh Ave’, 14)

,(‘Francis’, ‘Thomas’ , ‘15401 120th St’, 14)

CREATE TABLE NewYearRoster(LastName VARCHAR(50), FirstName VARCHAR(50),

Address VARCHAR(100),

Age INT);INSERT INTO NewYearRoster VALUES (‘Fritz’, ‘David’, ‘181 Kline Street’, 15)

,(‘Reese’, ‘Paul’, ‘1950 Grandview Place’, 15)

,(‘Adams’, ‘Wilbur’, ‘4231 W. 93rd’, 15)

,(‘Adams’, ‘Norris’, ‘100 1st Ave’, 15)

,(‘Thomas’, ‘Paul’, ‘18176 Soundview Dr’, 15)

,(‘Linderson’, ‘Danielle’, ‘941 W. 37 Ave’, 15)

,(‘Moore’, ‘Joshua’, ‘2311 10st Ave’, 15)

,(‘Dark’, ‘Shelby’, ‘1987 Fifth Ave’, 15)

,(‘Scharp’, ‘Mary’, ‘1902 W. 303rd’, 15)

,(‘Morris’, ‘Walt’, ‘100 12st St’, 15); You run the following MERGE statement to update, insert and delete rows in the CurrentStudents table MERGE TOP (3) CurrentStudents AS T USING NewYearRoster AS SON S.LastName = T.LastName AND S.FirstName = T.FirstName WHEN MATCHED AND NOT (T.Age = S.Age OR T.Address = S.Address) THEN

UPDATE SET Address = S.Address,

Age = S.AgeWHEN NOT MATCHED BY TARGET THEN

INSERT (LastName, FirstName, Address, Age)

VALUES (S.LastName, S.FirstName, S.Address, S.Age) WHEN NOT MATCHED BY SOURCE THEN

DELETE; You need to identify the total number of rows that are updated, inserted, and deleted in the CurrentStudent table. Which total number of rows should you choose?

A. 9

B. 0

C. 3

D. 6

Correct Answer: C


QUESTION 117

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 the estimated execution plan XML output document for the following query:

SELECT * FROM Sales.SalesOrderHeaderWHERE OnlineOrderFlag = 1 AND SubTotal > 500 You need to ensure that the user can view the document. 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_image004

Correct Answer:

clip_image005


QUESTION 118

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_image006

Correct Answer:

clip_image007


QUESTION 119

You have a table named Subcategories that contains subcategories for socks, vests and helmets. You have another table named Products that contains products only from the subcategories socks and vests.

You have the following query: SELECT s.Name, p.Name AS ProductNameFROM Subcategories s OUTER APPLY

(SELECT *

FROM Products pr

WHERE pr.SubcategoryID = s.SubcategoryID) p

WHERE s.Name IS NOT NULL; You need to predict the results of the query. What results should the query produce?

A. Name ProductName

———- ——————–

Socks Mountain Bike Socks, Socks Mountain Bike Socks, Socks Racing Socks, MSocks Racing Socks, LVests Classic Vest,

SVests Classic Vest, MVests Classic Vest, LHelmets NULL NULL NULL

B. Name ProductName

———- ——————–

Socks Mountain Bike Socks, Socks Mountain Bike Socks, Socks Racing Socks, MSocks Racing Socks, LVests Classic Vest,

SVests Classic Vest, MVests Classic Vest, LHelmets NULL

C. Name ProductName

———- ——————–

Socks Mountain Bike Socks, Socks Mountain Bike Socks, Socks Racing Socks, MSocks Racing Socks, LVests Classic Vest,

SVests Classic Vest, MVests Classic Vest,

LNULL Mountain Bike Socks, NULL Mountain Bike Socks, NULL Racing Socks,

MNULL Racing Socks, LNULL Classic Vest, SNULL Classic Vest, MNULL Classic Vest, LHelmets NULL

NULL NULL

D. Name ProductName

———- ——————–

Socks Mountain Bike Socks, Socks Mountain Bike Socks, Socks Racing Socks, MSocks Racing Socks, LVests Classic Vest,

SVests Classic Vest, MVests Classic Vest, L

Correct Answer: B


QUESTION 120

You are working with a SQL Server 2008 instance that is configured to use the Icelandic_CS_AS collation. You create a database by using code that includes the following statements. CREATE DATABASE InternationalDB COLLATE Japanese_CS_AS;GOUSE InternationalDB;GOCREATE TABLE AppPermTable (PrimaryKey int PRIMARY KEY, AppID nchar ); You implement a temporary table named #AppTempTable that uses the following code. USE InternationalDB;GOCREATE TABLE

#AppTempTable (PrimaryKey int PRIMARY KEY, AppID nchar );INSERT INTO #AppTempTable SELECT * FROM AppPermTable; You need to identify the collation that will be assigned to

#AppTempTable. Which collation will be assigned?

A. Icelandic_CS_AS

B. No collation

C. The collation selected by the Windows system locale of the server

D. Japanese_CS_AS

Correct Answer: A


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