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

You create a view on the SalesDB database using the following Transact-SQL code:
CREATE VIEW SalesV
WITH SCHEMABINDINGS
AS

SELECT Products.ProductID, Invoices.InvoiceDate, SUM (Products.RetailPrice *
OrderDetail.Quantity * OrderDetail.SalesDiscount) AS Price
FROM OrderDetail INNER JOIN Products ON
OrderDetail.ProductID = Products.ProducID
INNER JOIN Invoices ON
OrderDetail.InvoiceID = Invoices.InvoiceID
GROUP BY Products.ProductID, Invoices.InvoiceDate
GO
You want the SalesV view to persist data to disk in order to improve performance.
How would you accomplish this task?

You work as a database developer at ABC.com. You need to store Microsoft Word documents in a
SQL Server 2012 database table.
The Word documents must not be accessed by Windows applications that do not use TransactSQL queries.
How would accomplish this task?

You work as a database developer at ABC.com. ABC.com has a SQL Server 2012 database
named SalesDB with a table named Invoices.
Application developers are developing several in-house applications that will access the Invoices
table. You need to develop a solution that will allow the applications to access the table indirectly
while still allowing them to update the Invoice table.
How would you accomplish this task?

Which of the following can be used to protect the code in a stored procedure?

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

You need to write a Transact-SQL query that displays all Customers, whether they have invoices
or not. The query must also display the InvoiceDate for a Customer that has an Invoice.
How would you accomplish this task?

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?