Which of the following datatypes has a fixed precision and a scale of six digits?

You work as a database developer at ABC.com. ABC.com has a SQL Server 2012 database
named SalesDB as illustrated in the following database diagram:

ABC.com has retail stores in a few major cities across the country. The company wants a list of
Customers who live in a city that does not have a ABC.com store, along with the customer’s City.
The result set must be sorted alphabetically by City name.
Which of the following Transact-SQL statements would return the required information?

You work as a database developer at ABC.com. ABC.com has a SQL Server 2012 database
infrastructure with several databases. You have permissions on two of these databases, namely,
CommDB and SalesDB.
You need to develop a stored procedure named dbo.ABC_addEntry in a database named
AssetsDB. The dbo.ABC_addEntry stored procedure will run stored procedures in CommDB and
SalesDB.
How should you design the stored procedure so that callers that do not paver permissions on
CommDB and SalesDB can run the dbo.ABC_addEntry stored procedure successfully?

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?