QUESTION 111
You have a table named Orders. OrderID is defined as an IDENTITY(1,1). OrderDate has a default value of 1.
You need to write a query to insert a new order into the Orders table for CustomerID 45 with today’s date and a cost of 89.00.
Which statement should you use?
A. INSERT INTO Orders (CustomerId, OrderDate, Cost) VALUES (45, DEFAULT, 89.00);
B. INSERT INTO Orders (OrderID, CustomerId, OrderDate, Cost) VALUES (1, 45, DEFAULT, 89.00);
C. INSERT INTO Orders (CustomerId, OrderDate, Cost) VALUES (45, CURRENT_TIMESTAMP, 89.00);
D. INSERT INTO Orders (OrderID, CustomerId, OrderDate, Cost) VALUES (1, 45, CURRENT_TIMESTAMP, 89.00);
Answer: C
QUESTION 112
You have the following two tables.
The foreign key relationship between these tables has CASCADE DELETE enabled.
You need to remove all records from the Orders table.
Which Transact-SQL statement should you use?
A. DROP TABLE Orders
B. DELETE FROM Orders
C. TRUNCATE TABLE Orders
D. DELETE FROM OrderDetails
Answer: B
QUESTION 113
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
Answer: A
QUESTION 114
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 0.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);
Answer: A
QUESTION 115
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
Answer: C
QUESTION 116
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.
Answer: A
QUESTION 117
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
Answer: B
QUESTION 118
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’);
Answer: A
QUESTION 119
You have the following two tables.
Products
ProductID ProductName VendorID
1 Product1 0
2 Product2 1
3 Product3 1
4 Product4 0
ProductChanges
ProductID ProductName VendorID
1 Product1 1
2 Product2 1
3 NewProduct3 2
5 Product5 1
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?
A. ProductID ProductName VendorID
2 Product2 1
3 NewProduct3 2
B. ProductID ProductName VendorID
2 Product2 1
3 NewProduct3 2
4 Product4 0
C. ProductID ProductName VendorID
1 Product1 1
2 Product2 1
3 NewProduct3 2
5 Product5 1
D. ProductID ProductName VendorID
1 Product1 1
2 Product2 1
3 NewProduct3 2
4 Product4 0
5 Product5 1
Answer: B
QUESTION 120
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;
Answer: A
If you want to pass Microsoft 70-433 successfully, donot missing to read latest lead2pass Microsoft 70-433 practice tests.
If you can master all lead2pass questions you will able to pass 100% guaranteed.