A Microsoft SQL Server 2012 database containing the following definition has been created by you in order to support an application.

CREATE TABLE Inventory

(ItemID int NOT NULL PRIMARY KEY, ItemsInStore int NOT NULL, ItemsInWarehouse int NOT NULL)

Your job is to form a computed column which should return the sum total of ItemsInWarehouse and ItemsInStore values for all the rows. Choose the Transact-SQL statement you will use for the purpose. ALTER TABLE Inventory

ADD TotalItems = ItemsInStore + ItemsInWarehouse

A travel application is developed for which you are required to design database objects and tables. You begin by forming a table for Airlines Schedule. Now you need to store dates for arrivals and departures in addition to the flight timings containing the zone information as well. How will you do it?

A travel application is developed for which you are required to design database objects and tables. Your job is to provide a stored procedure. This procedure should also include various event names as well as their parameters and dates. How would you do it?

You are forming a stored procedure so multiple tables can be updated within a transaction. Before starting the stored procedure, which Transact-SQL statement would you choose to ensure that in the case where a run-time error gets raised, it results in the termination and rolling back of the entire transaction?

A software vendor has hired you as a database developer. Your job in this role is to form stored procedures that include propriety code. You have to make sure that this code never gets viewed by any of your customers. Which option will let you do that?

Two tables by the name of ‘DomesticSalesOrders’ and ‘InternationalSalesOrders’ are present inside a database. Each table contains over a 100 million rows and each row contains a Primary Key Column called ‘SalesOrderId’. There is no similarity between the data present in both the tables. You have been asked by the business users to make a report that contains collective information regarding the total sales amount and global sales figures. To execute your query in the least amount of time, which of the following queries would you use?

You oversee a Microsoft SQL Server 2012 database comprising of a table called ‘OrderDetail’ in it. You find out that the non-clustered index, NCI_OrderDetail_CustomerID, is fragmented. To reduce fragmentation while keeping the index online, which Transact-SQL batch would you prefer using?

A Microsoft SQL Server 2012 database has been developed by you. Two web applications are using it to access the ‘Products’ table. You are required to create an object that allows the applications to access the data they need, but without having direct access to the table.

Following are the requirements that need to be met:

Any table definition modifications in the future are not supposed to affect the data accessing ability of the application. Data modification and data retrieval can be accommodated by the new object.

The existing applications should undergo the least amount of changes in order to acquire this goal. For each application, which of the following would you create?

A Microsoft SQL Server 2012 database has been developed by you. To meet the following requirements, you are required to form a batch process:

The result set that is returned is based on the parameters supplied.

The result set that is returned is enabled to join a table.

A Microsoft SQL Server 2012 database has been developed by you. It holds a heap called ‘OrdersHistorical’. The following Transact-SQL query is written by you.

INSERT INTO OrdersHistorical

SELECT * FROM CompletedOrders

Choose a table hint that will allow you to optimize transaction locking and logging for the statement:

A tabled called, ‘Purchases’ is present in your database. It includes a DATETIME column called ‘PurchaseTime’. It stores the time and date of each purchase. This column has a non-clustered index on it. Your business team has asked you to prepare a report that shows the number of purchases made today. Which of the following Transact-SQL queries will efficiently return the most accurate results?

A travel application needs a database developed. For that, you have to design not only tables, but other database objects as well. You need to store each media file, typically less than 1 MB in size, in the tables. The files will need to have quick access and frequent retrieval. What do you plan to do?

A travel application needs a database developed. For that, you have to design not only tables, but other database objects as well. You need to store each media file, typically less than 1 MB in size, in the tables. The files will need to have quick access and frequent retrieval. What do you plan to do?

Within a territory, the total profit of each year is stored in a table named ‘Profits’. The columns of the table are named Year, Territory and Profit. The report that you have to create needs to display the profit of each territory for the previous year, as well as the each year.

What is the Transact-SQL query that you choose to use?