Querying Microsoft SQL Server 2012/2014 — Question 2

You develop a Microsoft SQL Server database that supports an application. The application contains a table that has the following definition:

CREATE TABLE Inventory -
(ItemID int NOT NULL PRIMARY KEY,
ItemsInStore int NOT NULL,
ItemsInWarehouse int NOT NULL)
You need to create a computed column that returns the sum total of the ItemsInStore and ItemsInWarehouse values for each row.
Which Transact-SQL statement should you use?

Answer options

• A. ALTER TABLE Inventory ADD TotalItems AS ItemsInStore + ItemsInWarehouse
• B. ALTER TABLE Inventory ADD ItemsInStore - ItemsInWarehouse = TotalItemss
• C. ALTER TABLEInventory ADD TotalItems = ItemsInStore + ItemsInWarehouse
• D. ALTER TABLE Inventory ADD TotalItems AS SUM(ItemsInStore, ItemsInWarehouse);

Correct answer: A

Explanation

The correct answer is A because it correctly uses the syntax for adding a computed column in SQL Server, which allows for the calculation of TotalItems as the sum of ItemsInStore and ItemsInWarehouse. Option B is incorrect due to its invalid syntax and incorrect assignment, while option C has a syntax error with the missing space in 'TABLEInventory'. Option D incorrectly uses the SUM function, which is not applicable for computed columns in this context.