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) 答案：A
43 你有一个名为 Product 的表。你需要为名为 Coho Winery 的卖主的产品价格增加 10%，同时返回产品名称和修改后价格。
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
答案：C
44 你有名为 dbo.Products 和 dbo.PriceChange 的两个表。dbo.Products 表包含 10 个产品。单价$20 的 5 条记录价格增加 10%。其他单价$10 的 5 条记录价格增加为 0。你有下列查询：
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);
你需要预计查询的结果。这个查询将产生哪个结果？ 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.
第 16 页
答案：A
45 你 有 名 为 MainTable 和 ArchiveTable 的 两 个 表 。 你 需 要 将 MainTable 表 中 超 过 30 的 数 据 移 到ArchiveTable 表。你应使用下面哪个代码段？
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
答案：B
46 你的任务是创建一个名为 dbo.Widgets 的新表。你需要插入 5 行记录到 dbo.Widgets 表并返回被插入 5条记录的每个 WidgetID。
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');
答案：A 47 你有下列两个表。
Products
ProductID ProductName VendorID 1 Product1 0 2 Product2 1 3 Product3 1
第 17
页
4 Product4 0
ProductChanges ProductID ProductName VendorID 1 Product1 1 2 Product2 1 3 NewProduct3 2 5 Product5 1 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;
你执行下列语句。
你需要确认 Products 表中显示的行。将显示哪几行？
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
D. ProductID ProductName VendorID 1 Product1 1 2 Product2 1 3 NewProduct3 2 4 Product4 0 5 Product5 1
答案：B
48 你 有 两 个 表 。 名 为 Student.CurrentStudents 的 表 包 含 本 年 度 注 册 的 所 有 学 生 的 姓 名 。 另一 个 名 为Student.NewYearRoster 的表包含下一个年度注册的所有学生的姓名。你的任务是写一个 MERGE 语句：下一年度注册但不在本年度注册的学生姓名插入到 Student.CurrentStudents 表。既在下一年度注册又在本年度 注册的学生更新 Student.CurrentStudents 表中信息。不在下一年度注册的学生姓名从 Student.CurrentStudents表中删除。你应使用哪个 T-SQL 语句？
A. MERGE Student.CurrentStudents AS T USING Student.NewYearRoster AS S
ON S.LastName = T.LastName AND S.FirstName = T.FirstName WHEN MATCHED THEN
第 18 页
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 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; DELETE
答案：A
49 你 用 下 列 T-SQL 语 句 创 建 和 填 充 两 个 表
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)
第 19 页
,('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','1987 Fifth Ave', ,('Dark', 'Shelby','1987 Fifth Ave',15) ,('Scharp', 'Mary','1902 W. 303rd',15) ,('Morris', 'Walt','100 12st St',15)
你 使 用 下 列 MERGE 语 句 更 新 、 插 入 和 删 除CurrentStudents 表中的行。
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;
你需要确认更新、插入和删除 CurrentStudents 表的整个行数。你应选择总 的行数是哪个？
A. 0 B. 3 C. 6 D. 9 答案：B
50 你将写一个查询来返回 2007 年总收入超过$10,000.00 的产品列表。你需要在查询中插入下列筛选表达式。
SUM([Order Details].UnitPrice * [Order Details].Quantity) > 10000 你应在哪个子句中插入这个表达式？
A. ON B. WHERE C. HAVING D. GROUP BY 答案：C
51 你有一个名为 Sales 的表。你的任务是列出卖给少于 10 个客户的产品。你需要写一个查询完成这个任务。你应使用哪个 T-SQL 语句？
A. SELECT ProductID,COUNT(*) AS CustomerCount FROM Sales GROUP BY ProductID, CustomerID HAVING COUNT(*) < 10;
B. SELECT ProductID,COUNT(DISTINCT CustomerID) AS CustomerCount FROM Sales GROUP BY ProductID
HAVING COUNT(DISTINCT CustomerID) < 10;
C. SELECT ProductID, CustomerID,
COUNT(DISTINCT CustomerID) AS CustomerCount FROM Sales GROUP BY ProductID, CustomerID HAVING COUNT(DISTINCT CustomerID) < 10;
D. SELECT * FROM
(SELECT ProductID, RANK() OVER (ORDER BY CustomerID DESC) AS Rnk FROM Sales) s WHERE s.Rnk <= 10; 答案：B
第 20 页