CORRECT TEXT

You are the database developer at ABC.com. ABC.com has a SQL Server 2012 database
infrastructure that has a database named ComDB with tables named Partners and Events. These
tables were created using the following Transact-SQL code:
CREATE TABLE [dbo].[Partners]
(
[CompanyID] [int] NOT NULL PRIMARY KEY,
[CompanyName] [varchar] (150) NOT NULL,
[Location] [varchar] (150) NOT NULL,
[ContactName] [varchar] (150) NOT NULL,
[Email] [varchar] (150) NOT NULL,
[Phone] [varchar] (10) NOT NULL
)
CREATE TABLE [dbo].[Events]
(
[EventID] [int] NOT NULL PRIMARY KEY,
[CompanyID] [int] NOT NULL,
[EventDescription] [nvarchar] (MAX),
[EventDate] [nvarchar] (50) NOT NULL,
[EventCordinator] [nvarchar] (150) NOT NULL
)
You add a foreign key relationship between the two tables on the CompanyID column.
You need to develop a stored procedure named sp_coEvents that retrieves CompanyName for all
partners and the EventDate for all events that they have coordinated.
To answer, type the correct code in the answer area.

You work as a database developer at ABC.com. ABC.com has a SQL Server 2012 database
infrastructure with a very large database named SalesDB. You create a new table named
SalesHistory that will hold historical data from the SalesDB database.
You need to perform a batch update from the SalesDB database to the SalesHistory table. You
want the status information from the batch process to be logged to a SQL Server table that must
be created by the batch process.
How would you accomplish this task?

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?