Ensurepass

QUESTION 41

You have been tasked to delete 1000 rows from a table named NewWidgets. There are 2000 rows in which the column ToBeDeleted set to 1. You need to write a Transact-SQL batch that will delete exactly 1000 rows. Which Transact-SQL batch should you use?

 

A.

DELETE TOP (1000) dbo.NewWidgets

WHERE ToBeDeleted = 1;

B.

DECLARE @BatchSize INT = 10;

WHILE (@BatchSize = 10)

DELETE TOP (@BatchSize) dbo.NewWidgets

WHERE ToBeDeleted = 1;

C.

DELETE TOP ((SELECT COUNT(*) FROM dbo.NewWidgets WHERE ToBeDeleted = 1)) w

FROM dbo.NewWidgets w

WHERE w.ToBeDeleted = 1;

D.

DECLARE @TotalRowCount INT = 0;

WHILE (@TotalRowCount <= 1000)

BEGIN

DELETE TOP (10) dbo.NewWidgets

WHERE ToBeDeleted = 1;

SET @TotalRowCount += @@ROWCOUNT;

END

 

Correct Answer: A

 

 

QUESTION 42

You have tables named Sales.SalesOrderDetails and Sales.SalesOrderHeader. You have been tasked to update the discount amounts for the sales of a particular salesperson. You need to set UnitPriceDiscount to .1 for all entries in Sales.SalesOrderDetail that only correspond to SalesPersonID 290. Which Transact-SQL statement should you use?

 

A.

UPDATE d SET UnitPriceDiscount = .1

FROM

Sales.SalesOrderDetail d

INNER JOIN

Sales.SalesOrderHeader h

ON h.SalesOrderID = d.SalesOrderID

WHERE h.SalesPersonID = 290;

B.

UPDATE Sales.SalesOrderDetail SET UnitPriceDiscount = .1 FROM Sales.SalesOrderHeader h

WHERE h.SalesPersonID = 290;

C.

UPDATE Sales.SalesOrderDetail SET UnitPriceDiscount = .1 WHERE EXISTS (

SELECT * FROM Sales.SalesOrderHeader h

WHERE h.SalesPersonID = 290);

D.

UPDATE Sales.SalesOrderDetail SET UnitPriceDiscount = .1 FROM Sales.SalesOrderDetail d

WHERE EXISTS (

SELECT * FROM Sales.SalesOrderHeader h

WHERE h.SalesPersonID = 290);

 

Correct Answer: A

 

QUESTION 43

You have a table named Product. You need to increase product prices for only the vendor named Coho Winery by 10 percent and then return a list of the products and updated prices. Which code segment should you use?

 

A.

UPDATE Product

SET Price = Price * 1.10,

ProductName = ProductName

WHERE Product.VendorName = ‘Coho Winery’

B.

UPDATE Product

SET Price = Price * 1.10

OUTPUT inserted.ProductName, deleted.Price

WHERE Product.VendorName = ‘Coho Winery’

C.

UPDATE Product

SET Price = Price * 1.10

OUTPUT inserted.ProductName, inserted.Price

WHERE Product.VendorName = ‘Coho Winery’

D.

UPDATE Product

SET Price = Price * 1.10,

VendorName = ‘Coho Winery’

OUTPUT inserted.ProductName, inserted.Price

 

Correct Answer: C

 

 

QUESTION 44

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.

Five rows are inserted into dbo.PriceChange.

B.

Five rows are updated in dbo.Products.

No rows are inserted into dbo.PriceChange.

C.

No rows are updated in dbo.Products.

Five rows are inserted into dbo.PriceChange.

D.

No rows are updated in dbo.Products.

No rows are inserted into dbo.PriceChange.

 

Correct Answer: A

 

 

 

 

QUESTION 45

You have two tables named MainTable and ArchiveTable. You need to move data older than 30 days from MainTable into ArchiveTable. Which code segment should you use?

 

A.

DELETE FROM MainTable

OUTPUT deleted.*

WHERE RecordDate < DATEADD(D,-30,GETDATE())

B.

DELETE FROM MainTable

OUTPUT DELETED.* INTO ArchiveTable

WHERE RecordDate < DATEADD(D,-30,GETDATE())

C.

INSERT INTO ArchiveTable

SELECT *

FROM MainTable

WHERE RecordDate < DATEADD(D,-30,GETDATE())

D.

INSERT INTO ArchiveTable

SELECT *

FROM MainTable

WHERE RecordDate < DATEADD(D,-30,GETDATE())

DELETE FROM MainTable

 

Correct Answer: B

 

 

QUESTION 46

You have been tasked with creating a table named dbo.Widgets. You need to insert five rows into the dbo.Widgets table and return WidgetID for each of the five rows that have been inserted. Which Transact-SQL batch should you use?

 

A.

CREATE TABLE dbo.Widgets (

WidgetID INT IDENTITY PRIMARY KEY,

WidgetName VARCHAR(25));

GO

INSERT dbo.Widgets (WidgetName)

OUTPUT inserted.WidgetID, inserted.WidgetName

VALUES

(‘WidgetOne’),(‘WidgetTwo’),(‘WidgetThree’),(‘WidgetFour’),(‘WidgetFive’);

B.

CREATE TABLE dbo.Widgets (

WidgetID INT IDENTITY PRIMARY KEY,

WidgetName VARCHAR(25) );

GO

INSERT dbo.Widgets (WidgetName)

VALUES

(‘WidgetOne’),(‘WidgetTwo’),(‘WidgetThree’),(‘WidgetFour’),(‘WidgetFive’); SELECT SCOPE_IDENTITY();

C.

CREATE TABLE dbo.Widgets (

WidgetID UNIQUEIDENTIFIER PRIMARY KEY,

WidgetName VARCHAR(25) );

GO

INSERT dbo.Widgets (WidgetName)

VALUES

(‘WidgetOne’),(‘WidgetTwo’),(‘WidgetThree’),(‘WidgetFour’),(‘WidgetFive’); SELECT SCOPE_IDENTITY();

D.

CREATE TABLE dbo.Widgets (

WidgetID UNIQUEIDENTIFIER PRIMARY KEY,

WidgetName VARCHAR(25));

GO

INSERT dbo.Widgets (WidgetName)

OUTPUT inserted.WidgetID, inserted.WidgetName

VALUES

(‘WidgetOne’),(‘WidgetTwo’),(‘WidgetThree’),(‘WidgetFour’),(‘WidgetFive’);

 

Correct Answer: A

 

 

QUESTION 47

You have the following two tables.

 

image007

 

You execute the following statement.

 

MERGE Products

 

USING ProductChanges

 

ON (Products.ProductID = ProductChanges.ProductID)

 

WHEN MATCHED AND Products.VendorID = 0

 

THEN DELETE

 

WHEN MATCHED

 

THEN UPDATE SET Products.ProductName = ProductChanges.ProductName

 

Products.VendorID = ProductChanges.VendorID;

 

You need to identify the rows that will be displayed in the Products table. Which rows will be displayed?

 

image008

 

A.

Option A

B.

Option B

C.

Option C

D.

Option D

 

Correct Answer: B

 

QUESTION 48

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 S

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

C.

MERGE Student.CurrentStudents AS T

USING Student.NewYearRoster AS S

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

MERGE Student.CurrentStudents AS T

USING Student.NewYearRoster AS S

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

 

Correct Answer: A

 

 

QUESTION 49

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 S

 

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

 

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.

0

B.

3

C.

6

D.

9

 

Correct Answer: B

 

 

QUESTION 50

You are writing a query that returns a list of products that have grossed more than $10,000.00 during the year 2007.

 

You need to insert the following filter expression into the query.

 

SUM([Order Details].UnitPrice * [Order Details].Quantity) > 10000

 

Into which clause should you insert this expression?

 

A.

ON

B.

WHERE

C.

HAVING

D.

GROUP BY

 

Correct Answer: C

 

Instant Access to Download Latest Complete Collection of Microsoft 70-433 Real Exam

Try Microsoft 70-433 Free Demo