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?

You work as a database administrator at ABC.com. ABC.com has a SQL Server 2012 database
infrastructure that contains a database named ABCDB.
The ABCDB database is used by an in-house application named ABCApp3 that queries a readonly table with a clustered index. ABC.com users report that ABCApp3 is functioning sluggishly.
You suspect query the application uses is causing the problem. You analyze the query and
discover that column referenced in the WHERE clause is not part of the clustered index. You also
notice that the query returns five columns, as well as a COUNT (*) clause grouped on the five
columns.
How would you improve the efficiency of this query?

You work as a database administrator at ABC.com. ABC.com has a SQL Server 2012 database
named ProductsDB. The ProductsDB database is shown in the following database diagram:

You need to write a Transact-SQL query that displays rows of data in the following XML format:
<Suppliers>
<CompanyName>Company Name</CompanyName>
<ContactName>Contact Name</ContactName>
<Phone>453 3545 5224</Phone>
<Products>
<ProductID>10</ProductID>
<ProductName>Product Name</ProductName>
<UnitPrice>559.00</UnitPrice>
<UnitsInStock>12</UnitsInStock>
</Products>
<Products>
<ProductID>132</ProductID>
<ProductName>Product Name</ProductName>

<UnitPrice>59.00</UnitPrice>
<UnitsInStock>102</UnitsInStock>
</Products>
<Products>
<ProductID>259</ProductID>
<ProductName>Product Name</ProductName>
<UnitPrice>599.00</UnitPrice>
<UnitsInStock>6</UnitsInStock>
</Products>
</Suppliers>
Which of the following SELECT statement would you write?

You work as a SQL Server 2012 database developer at ABC.com. ABC.com has a database
named SalesDB with tables named Customer and Orders. The Customer and Orders tables were
created using the following Transact-SQL code:
CREATE TABLE SalesDB.Customers
(
CustomerID int NOT NULL PRIMARY KEY,
CustomerName nvarchar (250) NOT NULL,
Address1 nvarchar (100) NOT NULL,
Address2 nvarchar (100) NULL,
City nvarchar (50) NOT NULL,
State nvarchar (50) NOT NULL,
Zip varchar (5) NOT NULL,
Phone varchar (10) NOT NULL
)
GO
CREATE TABLE SalesDB.Orders
(
OrderID int NOT NULL PRIMARY KEY,
CustomerID int NOT NULL,
OrderDate datetime NOT NULL,
ShipDate datetime NOT NULL,
CustomerID int NOT NULL,
SalesRepID int NOT NULL
)
GO
You are developing a stored procedure named OrdersByDate that returns the OrderID,

CustomerID, CustomerName and OrderDate. The stored procedure will take a parameter named
@date that uses the int datatype. The @date parameter will be used to filter the result set based
on the OrderDate column in the Orders table.
How would you create the stored procedure?