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?

You work as a database administrator at ABC.com. You are developing a database that will be
used by a web site analysis application name ABCWeb1.
The ABCWeb1 application must display the date and time each visitor visits a page on a website
as well as the date and time they leave that web page. This data needs to be displayed in different
date and time formats.

How would you accomplish this?

You work as a database developer at ABC.com. ABC.com has a SQL Server 2012 database
named SalesDB that has a table named Inventory.
The Inventory table has three columns named ProductID, InStore and InWarehouse. The
ProductID column is the primary key and is linked to the Products table. The InStore column
stores the quantity of a product that is held at ABC.com’s retail shop, while the InWarehouse
column stores the quantity of a product that is held at ABC.com’s warehouse.
You need to add a computed column that is the sum of values in the InStore and InWarehoue
columns for each product.
What Transact SQL statement would accomplish this task?

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?