which clause should you insert this expression?
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?
Which total number of rows should you choose?
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?
Which Transact-SQL statement should you use?
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?
Which rows will be displayed?
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?
Which Transact-SQL batch 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 Transact-SQL batch should you use?
Which code segment should you use?
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?
Which results should the query produce?
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?
Which code segment 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?
Which Transact-SQL statement 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 0.1 for all entries in Sales.SalesOrderDetail that only correspond to SalesPersonID 290. Which Transact-SQL statement should you use?
Which Transact-SQL batch should you use?
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?