You work as a SQL Server 2012 database developer at ABC.com. You are developing a query for
a database driven Web application that allows visitors to vote for the cricket player of the week.
The number of votes is stored in a table named WeeklyVotes that has columns named Week,
PlayerName, Votes.
You need to write a Transact-SQL query that returns the cricket player that received the most
votes for each week, as well as the number of votes they received.
Which of the following SELECT statement would accomplish this task?

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 stores the total number of a product that ABC.com has.
What Transact-SQL statements would accomplish this task?

ABC.com has a SQL Server 2012 database instance that hosts a database named ComDB. The
ComDB database has a table named Partners that was created using the following Transact-SQL
code:
CREATE TABLE [dbo].[Partners]
(
[CompanyID] [int] NOT NULL,
[CompanyName] [nvarchar] (50) NOT NULL,
[Location] [nvarchar] (50) NOT NULL,
[ContactName] [nvarchar] (50) NOT NULL,
[Email] [nvarchar] (50) NOT NULL,
[Phone] [nvarchar] (10) NOT NULL,
CONSTRAINT [PK_Partners] PRIMARY KEY CLUSTERED
(
[CompanyID] ASC
)
ON PRIMARY

)
You want to create a FOR UPDATE trigger that will track changes to the ContactName and Phone
columns.
Which of the following statements should you use in the trigger definition?

You are the database administrator of a SQL Server 2012 database infrastructure at ABC.com.
You need to optimize a very large database table that contains several million rows of data by
designing a view based on the table. The view must allow users to perform aggregations on
several columns.
How should you configure the view to ensure optimal performance?

You are the database developer at ABC.com. ABC.com has a SQL Server 2012 database
infrastructure that has a database named ComDB with a table named Partners.
The Partners table was 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
)
You develop a new table named Events using the following Transact-SQL code:
CREATE TABLE [dbo].[Events]
(
[EventID] [int] NOT NULL PRIMARY KEY,
[CompanyID] [int] NOT NULL,
[EventDescription] [varchar] (2500),
[EventCordinator] [varchar] (150) NOT NULL
)
How would you guarantee that values in the Events.CompanyID column already exist in the
Partners.CompanyID column?

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?