ABC.com has a SQL Server 2012 database infrastructure that has a database named ComDB.
You have created a view using the following Transact-SQL code:
CREATE VIEW ABCCommunications
(Type, CompanyID, CompanyName, Location, ContactName, Email, Phone)
AS
SELECT ‘Clients’ AS Type, CompanyID, CompanyName, Location, ContactName, Email, Phone
FROM CommList
WHERE Relation = ‘Client’
SELECT ‘Partners’ AS Type, CompanyID, CompanyName, Location, ContactName, Email, Phone
FROM CommList
WHERE Relation = ‘Partner’
SELECT ‘Guests’ AS Type, CompanyID, CompanyName, Location, ContactName, Email, Phone
FROM CommList
WHERE Relation = ‘Guests’
GO
You want the view to be used to edit all columns except the CompanyID, CompanyName and
Location columns.
What should you implement on the view?

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 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
How should you alter this view to allow users to update data through the SalesV?

CORRECT TEXT
You are employed as a SQL Server 2012 database developer at ABC.com. ABC.com has a
SalesDB database with a view named SalesV. The SalesV view was created using the following
Transact-SQL code:
CREATE VIEW SalesDB.ProductsSalesV
AS
SELECT OrderID, ProductID, ShipDate, OrderDate, Amount
FROM SalesDB.Orders;
You want to create an inline table-valued function named fn_ABC that accepts a @ProductID
parameter of the integer data type. The inline table-valued function should also allow for sales
orders for each product to be listed by the latest sale.
How would you create this inline table-valued function?
To answer, type the correct code in the answer area.

You work as a SQL Server 2012 database developer at ABC.com. ABC.com has a database
named SalesDB.
You are developing a stored procedure that takes a parameter named @date that uses the
varchar datatype. The @date parameter must be compared to the value in a datetime column

named OrderDate.
Which of the following WHERE clauses would be the most efficient WHERE clause to use?

You work as a database developer at ABC.com. You want to create a Transact-SQL query will call
a table-valued function for every row the query returns.
How would you accomplish this task?

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?