Latest 70-433 Real Exam Download 131-140

Ensurepass

QUESTION 131

You are the administrator of a SQL Server database. Database table modifications and additions must occur only between 11:00 P.M. and midnight. You need to ensure that if database table modifications or additions are attempted at any other time, an error is raised and the attempt is not successful. Which Transact-SQL statement should you use?

A. CREATE TRIGGER TRG_TABLES_ON_LAST_HOUR ON ALL SERVER FOR ALTER_DATABASE

AS

IF DATEPART(hour,getdate())<>1 BEGIN

ROLLBACK RAISERROR (‘Must wait.’, 16, 1)

END

B. CREATE TRIGGER TRG_TABLES_ON_LAST_HOUR ON DATABASE FOR CREATE_TABLE

AS

IF DATEPART(hour,getdate())<>23 BEGIN

RAISERROR (‘Must wait.’, 16, 1)

END

C. CREATE TRIGGER TRG_TABLES_ON_LAST_HOUR ON DATABASE

FOR CREATE_TABLE,ALTER_TABLE AS

IF DATEPART(hour,getdate())<>23 BEGIN

RAISERROR (‘Must wait.’,16, 1) END

D. CREATE TRIGGER TRG_TABLES_ON_LAST_HOUR ON DATABASE FOR CREATE_TABLE,ALTER_TABLE AS

IF DATEPART(hour,getdate())<>1 BEGIN

ROLLBACK

RAISERROR (‘Must wait.’, 16, 1) END

Correct Answer: D


QUESTION 132

You need to generate the following XML document.

<ProductExport>

<Product Price="99">Product1</Product>

<Product Price="199">Product2</Product>

<Product Price="299">Product3</Product>

<Product Price="399">Product4</Product></ProductExport> Which query should you use?

A. SELECT Price, ProductName

FROM Products

FOR XML AUTO, ROOT(‘ProductExport’)

B. SELECT Price [@Price],ProductName AS [*] FROM Products

FOR XML PATH(‘Product’),ROOT(‘ProductExport’)

C. SELECT Price, ProductName FROM Products AS ProductExport FOR XML PATH(‘Product’)

D. SELECT Price [@Price], ProductName AS [*] FROM Products AS ProductExport

FOR XML AUTO, ELEMENTS

Correct Answer: B


QUESTION 133

You have a table named Customer that has an XML column named Locations. This column stores an XML fragment that contains details of one or more locations, as show in the following examples. <Location City="Sydney" Address="…" PhoneNumber="…" /><Location City="Chicago" Address="…" PhoneNumber="…" /><Location City="London" Address="…" PhoneNumber="…" /> You need to write a query that returns a row for each of the customer’s locations. Each resulting row must include the customer name, city, and an XML fragment that contains the location details. Which query should you use?

A. SELECT CustomerName,Loc.value(‘@City’,’varchar(100)’),Loc.query(‘.’) FROM Customer

CROSS APPLY Customer.Locations.nodes (‘/Location’) Locs(Loc)

B. SELECT CustomerName,Locations.query(‘for $i in /Location return data($i/@City)’),Locations.query (‘for $i in /Location return $i’)

FROM Customer

C. SELECT CustomerName,Locations.query(‘data(/Location/@City)’),Locations.query(‘/Location’) FROM Customer

D. SELECT CustomerName,Locations.query(‘for $i in /Location return element Location {$i/@City,$i}’) FROM Customer

Correct Answer: A


QUESTION 134

Your company manufactures and distributes bicycle parts. You have a full-text catalog on the Inventory table which contains the PartName and Description columns. You also use a full-text thesaurus to expand common bicycle terms.

You need to write a full-text query that will not only match the exact word in the search, but also the meaning. Which Transact-SQL statement should you use?

A. SELECT * FROM Inventory WHERE Description LIKE ‘%cycle%’

B. SELECT * FROM Inventory WHERE FREETEXT (*, ‘cycle’))

C. SELECT * FROM Inventory WHERE CONTAINS (*, ‘FormsOf(Inflectional, cycle)’)

D. SELECT * FROM Inventory WHERE CONTAINS (*, ‘cycle’)

Correct Answer: B


QUESTION 135

You are using the Database Engine Tuning Advisor (DTA) to analyze a workload. You need to save the recommendations generated by the DTA. Which command should you use?

A. Export Session Results

B. Export Session Definition

C. Import Session Definition

D. Preview Workload Table

Correct Answer: A


QUESTION 136

You have two tables. A table named Student.CurrentStudents contains the names of all students enrolled for the current year. Another table named Student.NewYearRoster contains the names of students who have enrolled for the upcoming year. You have been tasked to write a MERGE statement to:

·Insert into Student.CurrentStudents the names of students who are enrolled for the upcoming year but not for the current year.

·Update information in Student.CurrentStudents for students who are enrolled both in the current year and in the upcoming year.

·Delete from Student.CurrentStudents the names of students who are not enrolled for the upcoming year. You need to write the appropriate MERGE statement. Which Transact-SQL statement should you use?

A. MERGE Student.CurrentStudents AS T USING Student.NewYearRoster AS S ON S.LastName = T. LastName AND S.FirstName = T.FirstName

WHEN MATCHED THEN UPDATE SET Address = S.Address, Age = S.Age

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

B. MERGE Student.CurrentStudents AS T USING Student.NewYearRoster AS SON S.LastName = T. LastName AND S.FirstName = T.FirstName

WHEN MATCHED AND NOT T.Address = S.Address AND NOT T.Age = S.Age THEN UPDATE SET

T.Age = S.Age, T.Address = S.Address

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

C. MERGE Student.CurrentStudents AS T USING Student.NewYearRoster AS SON S.LastName = T. LastName AND S.FirstName = T.FirstName

WHEN MATCHED AND NOT T.Address = S.Address OR NOT T.Age = S.Age THEN UPDATE SET T.

Address = S.Address, T.Age = S.Age

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

WHEN MATCHED THEN DELETE;

D. D. MERGE Student.CurrentStudents AS T USING Student.NewYearRoster AS SON S.LastName = T. LastName AND S.FirstName = T.FirstName

WHEN MATCHED THEN DELETE

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

WHEN NOT MATCHED BY SOURCE THEN UPDATE SET Address = T.Address, Age = T.Age;

Correct Answer: A


QUESTION 137

You have the following rows in the Customer Table: CustomerId Status————————— 1

Active2 Active3 Inactive4 NULL5

Dormant6

Dormant You write the following query to return all customers that do not have NULL or

‘Dormant’ for their status: SELECT *FROM

CustomerWHERE Status NOT IN (NULL, ‘Dormant’)You need to identify the results of the query.Which result should you expect?

A. CustomerId Status

————- ———–

1 Active

2 Active

3 Inactive

4 NULL

B. CustomerId Status

————- ———–

1 Active

2 Active

3 Inactive

4 NULL

5 Dormant

6 Dormant

C. CustomerId Status

————- ———–

D. CustomerId Status

————- ———–

1 Active

2 Active

3 Inactive

Correct Answer: C


QUESTION 138

You are the database developer for a customer service application. The database has the following table and view. CREATE TABLE T ickets (

T icketId int,

Department varchar(200), Status varchar(200),

IsArchived bit); GOCREATE VIEW SupportT ickets ( T icketId,

Department, Status, IsArchived)ASSELECT

t.T icketId, t.Department, t.Status,

t.IsArchivedFROM T ickets tWHERE t.Department = ‘support’

AND t.IsArchived = 0WITH CHECK OPTION; GOINSERT INTO T ickets VALUES (1, ‘support’, ‘open’, 0), (2, ‘support’, ‘open’, 0), (3, ‘support’, ‘open’, 0);GO You are assigned to review another

developer’s code. You need to verify that the entire code is functional. Which Transact-SQL statements will fail?

A. INSERT INTO T ickets VALUES (4, ‘sales’, ‘open’, 0), (5, ‘support’, ‘open’, 0), (6, ‘support’, ‘open’, 1)

B. UPDATE T icketsSET IsArchived = 1WHERE T icketId IN (1, 2, 3)

C. UPDATE SupportT icketsSET IsArchived = 1WHERE T icketId IN (1, 2, 3)

D. UPDATE SupportT icketsSET Status = ‘closed’WHERE T icketId IN (1, 2, 3)

E. INSERT INTO SupportT ickets VALUES (4, ‘support’, ‘open’, 0), (5, ‘support’, ‘in progress’, 0), (6,

‘support’, ‘closed’, 0)

F. INSERT INTO T ickets VALUES (4, ‘support’, ‘open’, 0), (5, ‘support’, ‘in progress’, 0), (6, ‘support’, ‘closed’, 0)

G. INSERT INTO SupportT ickets VALUES (4, ‘support’, ‘open’, 1), (5, ‘support’, ‘in progress’, 1), (6,

‘support’, ‘closed’, 1)

H. DELETE FROM SupportT icketsWHERE T icketId IN (1, 2, 3)

Correct Answer: CG


QUESTION 139

You administer a database named Contoso running on a Microsoft SQL Server 2008 R2 instance. You plan to implement custom error handling in your application. You need to implement custom error handling that meets the following requirements:

·The custom message is a reusable user-defined error message.

·The message returned to the application is an informational message that returns status

information or error that is not severe.

·The custom message returned indicates that an error has occurred in the current database and current session. 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[4]

Correct Answer:

clip_image002[4]


QUESTION 140

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. You create tables named order, customer, and product as follows: CREATE TABLE [dbo].[order] ([OrderID] [int],

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

[OrderDate] [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 by the following entries:

·The Customer name and product name

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

A. SELECT p.ProductName,DATEPART(mm, o.OrderDate) OrderMonth,SUM(p.SalePrice) AS Sales FROM product p

INNER JOIN[order] o ON p.ProductID = o.ProductID GROUP BY ProductName, DATEPART(mm, o.OrderDate);

B. SELECT c.CustomerName,COUNT(o.OrderID) AS Orders FROM customer c

INNER JOIN [order] o ON c.CustomerID = o.CustomerID WHERE COUNT(o.OrderID) > 10

GROUP BY c.CustomerName;

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

INNER JOIN [order] o ON ProductID = o.ProductID INNER JOIN customer c ON o.CustomerID = CustomerID WHERE o.OrderDate > ’09/01/2011′

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

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

INNER JOIN [order] o ON p.ProductID = o.ProductID INNER JOIN customer c ON o.CustomerID = CustomerID

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

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

INNER JOIN [order] o ON p.ProductID = o.ProductID INNER JOIN customer c ON o.CustomerID = CustomerID

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

F. SELECT p.ProductName,DATEPART(mm, o.OrderDate) OrderMonth,SUM(p.SalePrice) AS Sales FROM product p

INNER JOIN [order] o ON p.ProductID = o.ProductID GROUP BY CUBE;

G. SELECT p.ProductName,DATEPART(mm, o.OrderDate) OrderMonth,SUM(p.SalePrice) AS Sales FROM product p

INNER JOIN [order] o ON p.ProductID = o.ProductID

GROUP BY CUBE(p.ProductName, DATEPART(mm, o.OrderDate));

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

INNER JOIN [order] o ON ProductID = o.ProductID INNER JOIN customer c ON o.CustomerID = CustomerID WHERE o.OrderDate > ’09/01/2011′

AND AVG(p.SalePrice) >= 500

I. SELECT p.ProductName,DATEPART(mm, o.OrderDate) OrderMonth,SUM(p.SalePrice) AS Sales FROM product p

INNER JOIN [order] o ON p.ProductID = o.ProductID GROUP BY ProductName, OrderMonth;

J. SELECT c.CustomerName,COUNT(o.OrderID) AS Orders FROM customer c

INNER JOIN [order] o ON c.CustomerID = o.CustomerID GROUP BY c.CustomerName

HAVING COUNT(o.OrderID) > 10;

Correct Answer: B


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