為大家收集整理了《2013微軟認證:sql認證考試練習(xí)試題4》供大家參考，希望對大家有所幫助?。?！
第16題 你有下面兩個表。這兩個表之間的外鍵關(guān)系啟用了CASCADE DELETE。你需要從Orders表刪除所有記錄。應(yīng)使用哪 一條Transact-SQL語句？ A. DROP TABLE Orders B. DELETE FROM Orders C. TRUNCATE TABLE Orders D. DELETE FROM OrderDetails 答案：B
第17題 你的任務(wù)是從名為NewWidgets的表刪除1000行。有2000行的ToBeDeleted列設(shè)置為1。你需要編寫一個Transact-SQL 批處理來準確刪除1000行。應(yīng)使用哪個Transact-SQL批處理？ A. DELETE (1000) dbo.NewWidgets WHERE ToBeDeleted = 1; B. DECLARE @BatchSize INT = 10; WHILE (@BatchSize = 10) DELETE (@BatchSize) dbo.NewWidgets WHERE ToBeDeleted = 1; C. DELETE ((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 (10) dbo.NewWidgets WHERE ToBeDeleted = 1; SET @TotalRowCount += @@ROWCOUNT; END 答案：A
第18題 你有兩個表dbo.Products和dbo.PriceChange。dbo.Products表包含10種產(chǎn)品。五種產(chǎn)品的單價為$20，PriceIncrease（價 格增量）設(shè)置為1。其他五種產(chǎn)品的單價為$10，PriceIncrease（價格增量）設(shè)置為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.PriceWHERE PriceIncrease = 1 ) p (ProductID, inPrice, delPrice); 你需要預(yù)測查詢結(jié)果。該查詢應(yīng)生成什么結(jié)果？ A. dbo.Products中五行更新。五行插入dbo.PriceChange。 B. dbo.Products中五行更新。沒有行插入dbo.PriceChange。 C. dbo.Products中無行更新。五行插入dbo.PriceChange。 D. dbo.Products中無行更新。沒有行插入dbo.PriceChange。 答案：A
第19題 Products ProductID ProductName 1 Product1 2 Product2 3 Product3 4 Product4 ProductChanges ProductID 1 2 3 5 ProductName Product1 Product2 NewProduct3 Product5 VendorID 0 1 1 0 VendorID 1 1 2 1 你執(zhí)行了下面的語句。 MERGE Products USING Produc tChanges 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 B. ProductID 2 3 4 C. ProductID 1 2 3 5 D. ProductID 1 答案：B
第20題 你有兩個表。 名為Student.CurrentStudents的表包含為今年注冊的所有學(xué)生的姓名。 另一個名為Student.NewYearRoster 的表包含為明年注冊的學(xué)生的姓名。你的任務(wù)是編寫MERGE語句來實現(xiàn)以下目的： 在Student.CurrentStudents中插入為明年注冊， 但不是為今年注冊的學(xué)生的姓名。 更新Student.CurrentStudents中為今年 和明年都注冊的學(xué)生的信息。從Student.CurrentStudents表中刪除沒有為明年注冊的學(xué)生的姓名。你需要編寫相應(yīng)的 MERGE語句。應(yīng)使用哪一條Transact-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 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; 答案：A
第16題 你有下面兩個表。這兩個表之間的外鍵關(guān)系啟用了CASCADE DELETE。你需要從Orders表刪除所有記錄。應(yīng)使用哪 一條Transact-SQL語句？ A. DROP TABLE Orders B. DELETE FROM Orders C. TRUNCATE TABLE Orders D. DELETE FROM OrderDetails 答案：B
第17題 你的任務(wù)是從名為NewWidgets的表刪除1000行。有2000行的ToBeDeleted列設(shè)置為1。你需要編寫一個Transact-SQL 批處理來準確刪除1000行。應(yīng)使用哪個Transact-SQL批處理？ A. DELETE (1000) dbo.NewWidgets WHERE ToBeDeleted = 1; B. DECLARE @BatchSize INT = 10; WHILE (@BatchSize = 10) DELETE (@BatchSize) dbo.NewWidgets WHERE ToBeDeleted = 1; C. DELETE ((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 (10) dbo.NewWidgets WHERE ToBeDeleted = 1; SET @TotalRowCount += @@ROWCOUNT; END 答案：A
第18題 你有兩個表dbo.Products和dbo.PriceChange。dbo.Products表包含10種產(chǎn)品。五種產(chǎn)品的單價為$20，PriceIncrease（價 格增量）設(shè)置為1。其他五種產(chǎn)品的單價為$10，PriceIncrease（價格增量）設(shè)置為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.PriceWHERE PriceIncrease = 1 ) p (ProductID, inPrice, delPrice); 你需要預(yù)測查詢結(jié)果。該查詢應(yīng)生成什么結(jié)果？ A. dbo.Products中五行更新。五行插入dbo.PriceChange。 B. dbo.Products中五行更新。沒有行插入dbo.PriceChange。 C. dbo.Products中無行更新。五行插入dbo.PriceChange。 D. dbo.Products中無行更新。沒有行插入dbo.PriceChange。 答案：A
第19題 Products ProductID ProductName 1 Product1 2 Product2 3 Product3 4 Product4 ProductChanges ProductID 1 2 3 5 ProductName Product1 Product2 NewProduct3 Product5 VendorID 0 1 1 0 VendorID 1 1 2 1 你執(zhí)行了下面的語句。 MERGE Products USING Produc tChanges 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 B. ProductID 2 3 4 C. ProductID 1 2 3 5 D. ProductID 1 答案：B
第20題 你有兩個表。 名為Student.CurrentStudents的表包含為今年注冊的所有學(xué)生的姓名。 另一個名為Student.NewYearRoster 的表包含為明年注冊的學(xué)生的姓名。你的任務(wù)是編寫MERGE語句來實現(xiàn)以下目的： 在Student.CurrentStudents中插入為明年注冊， 但不是為今年注冊的學(xué)生的姓名。 更新Student.CurrentStudents中為今年 和明年都注冊的學(xué)生的信息。從Student.CurrentStudents表中刪除沒有為明年注冊的學(xué)生的姓名。你需要編寫相應(yīng)的 MERGE語句。應(yīng)使用哪一條Transact-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 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; 答案：A