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 TransactSQL
batch that will delete exactly 1000 rows.
Which TransactSQL
batch should you use?

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 TransactSQL
statement should you use?

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?

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?

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?

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 TransactSQL
batch should you use?

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?

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 TransactSQL
statement should you use?

You create and populate two tables by using the following TransactSQL
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?

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?