You are developing a SQL Server 2012 database for ABC.com. You need to create a computed
column that returns the data by referencing another table using an INNER JOIN.
How would you accomplish this?

Which of the following statements regarding SQL Server 2012 objects is TRUE?

You work as a SQL Server 2012 database developer at ABC.com. TesABCig.com has a database
named DataDB.
You are developing a complex stored procedure named sp_updater that will use a single
transaction to update several tables in the DataDB database.
You are concerned about data integrity and incomplete updates should the sp_updater stored
procedure cause a run-time error.
To mitigate this potential problem you want the transaction to terminate and the transaction to be
rolled back if the sp_updater stored procedure raises a run-time error.
How would you accomplish this task?

You work as a SQL Server 2012 database developer at ABC.com.
ABC.com has a database SalesDB with a large Orders table. You create a heap named OldData
that will store historical data from the Orders table.
You need to write a Transact-SQL query that will insert rows of data from the Orders table that are
marked as closed and are more than six months old.
Which of the following table hints should you use in your query if you want to optimize transaction
logging and locking for the query?

You are employed as a SQL Server 2012 database developer at ABC.com. You have a stored
procedure that is executed quite often. The stored procedure joins data from two tables.
ABC.com users report that the stored procedure takes a long time to execute. You analyze the

query plan and find that the stored procedure often makes use of table scans rather than indexes
when the estimated rows do not match the actual rows on one of the tables.
How would you optimize the performance of the stored procedure?

You work as a SQL Server 2012 database developer at ABC.com. You are developing a database
driven Web application. The application executes a store procedure based on the location of the
web user.
The location of the Web user is determined by IP Geolocation. You want to develop a process that
will execute the correct stored procedure for every Web user based on the user’s location.
How would you accomplish this?

You work as a SQL Server 2012 database administrator at ABC.com. You have developing a
database named WebAnDB that will be used by a web site analysis application. The WebAnDB
database has a table named Visitors that stores date and time data in a column named Accessed.

You must now develop a stored procedure that will insert data into the Accessed column. You
want the stored procedure to store time zone data as well.
How would you accomplish this?

You work as a SQL Server 2012 database developer at ABC.com.
You are developing a database that will be used by a Web application. The database will store
small multimedia files in several tables. The largest multimedia file is 975 kB. These multimedia
files will be retrieved quite often.
How would you store these files?

You work as a database administrator at ABC.com. You are developing a database that will be
used by a web site analysis application name ABCWeb1.
The ABCWeb1 application must display the date and time each visitor visits a page on a website
as well as the date and time they leave that web page. This data needs to be displayed in different
date and time formats.

How would you accomplish this?

You work as a database developer at ABC.com. ABC.com has a SQL Server 2012 database
named SalesDB that has a table named Inventory.
The Inventory table has three columns named ProductID, InStore and InWarehouse. The
ProductID column is the primary key and is linked to the Products table. The InStore column
stores the quantity of a product that is held at ABC.com’s retail shop, while the InWarehouse
column stores the quantity of a product that is held at ABC.com’s warehouse.
You need to add a computed column that is the sum of values in the InStore and InWarehoue
columns for each product.
What Transact SQL statement would accomplish this task?