You work as a SQL Server 2012 database developer at ABC.com. You are developing a batch
process that accepts an input parameter and allows the result set to be joined with a table.
How would you accomplish this?

You work as a database administrator at manufacturing company named ABC.com. ABC.com has
a SQL Server 2012 database named ProductionDB. The ProductionDB database has a table
named Sites that was created using the following Transact-SQL code:
CREATE TABLE Sites (
SiteID int NOT NULL PRIMARY KEY,
Location int NOT NULL,
Manager nvarchar(200) NOT NULL,
Week smallint NOT NULL,
ItemsProduced int NOT NULL )
You want to write the Transact-SQL query that returns the number of items produced at each

location for each week. In addition, you want the result set to include a column named
PrevItemsProduced that holds the number of items produced at each location for the week before.
What Transact SQL statement would accomplish this task?

You work as a SQL Server 2012 database developer at ABC.com. You are developing a stored
procedure for ABC.com’s e-Commerce application.
Your stored procedure must display unique values from one column in multiple columns in the
result set.
How would you accomplish this?

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 to
ascertain whether it would be advantageous to open a store in other cities based on feedback
from its customers.
You are required to provide the company’s CEO with a list of Customers who live in a city that
does not have a ABC.com store, along with the customer’s Phone Number and the customer’s
City, and arranged 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. You are developing a SQL server 2012 database
for ABC.com’s e-Commerce application. The application allows ABC.com employees from
different regions to execute a store procedure based on their location.
The location of the ABC.com employees are determined by an input parameter named @location.
You want to develop a process that will execute the correct stored procedure for every ABC.com
employee based on their location.
How would you accomplish this?

You work as a SQL Server 2012 database developer at ABC.com. ABC.com has a large database

named SalesDB.
New rows are inserted into the tables in the SalesDB database and updates to existing rows occur
on a high frequency. The inserts and updates often blocked by queries retrieving and reading
data.
How would you prevent queries that retrieve and read data from blocking queries that insert and
update data?

You work as a SQL Server 2012 database developer at ABC.com. You are developing a stored
procedure that updates rows in several tables.
You want the entire transaction to be rolled back should the stored procedure cause a run-time
error.
How would you accomplish this?

You work as a database developer at ABC.com. ABC has an in-house application named
ABCApp3 that runs a Transact-SQL query against a SQL Server 2012 database named SalesDB.
ABC.com users report that ABCApp3 is functioning sluggishly. You discover that concurrent
updates are causing blockages on the SalesDB database.
How would you ensure that the query to use the original data when updates occur?

You work as a database developer at ABC.com. ABC has an in-house application named
ABCApp3 that runs a Transact-SQL query against a SQL Server 2012 database.
You want to run an execution plan against the query that will provide detailed information on
missing indexes.
How would you accomplish this task?

You work as a database administrator at ABC.com. ABC.com has a SQL Server 2012 database
infrastructure that contains a database named SalesDB.
The SalesDB database is used by an in-house application named ABCApp3. ABC.com users
report that ABCApp3 is functioning sluggishly.
You discover that application consumes considerable memory when it runs single-use dynamic
queries against the SalesDB database. You suspect that these queries are making excessive use
of procedure cache.
How would you reduce procedure cache if you cannot create new indexes on the SalesDB
database?