You develop a database application for a university. You need to create a view that will be indexed that meets
the following requirements:
Displays the details of only students from Canada.
Allows insertion of details of only students from Canada.
Which four Transact-SQL statements should you use? (To answer, move the appropriate SQL statements from
the list of statements to the answer area and arrange them in the correct order.)

You create the following stored procedure. (Line numbers are included for reference only.)

You need to ensure that the stored procedure performs the following tasks:
If a record exists, update the record.
If no record exists, insert a new record.
Which four Transact-SQL statements should you insert at line 07? (To answer, move the appropriate
statements from the list of statements to the answer area and arrange them in the correct order.)

You use Microsoft SQL Server 2012 to develop a database application. You create two tables by using the
following table definitions.

Which six Transact-SQL statements should you use? (To answer, move the appropriate SQL statements from
the list of statements to the answer area and arrange them in the correct order.)

You use Microsoft SQL Server 2012 to develop a database application. You create a table by using the
following definition:
CREATE TABLE Prices (
PriceId int IDENTITY(1,1) PRIMARY KEY,
ActualPrice NUMERIC(16,9),
PredictedPrice NUMERIC(16,9)
)
You need to create a computed column based on a user-defined function named udf_price_index. You also
need to ensure that the column supports an index. Which three Transact-SQL statements should you use? (To
answer, move the appropriate SQL statements from the list of statements to the answer area and arrangethem
in the correct order.)

You use Microsoft SQL Server 2012 to develop a database that has two tables named Div1Cust and Div2Cust.
Each table has columns named DivisionID and CustomerId . None of the rows in Div1Cust exist in Div2Cust.
You need to write a query that meets the following requirements:
The rows in Div1Cust must be combined with the rowsin Div2Cust.
The result set must have columns named Division andCustomer.
Duplicates must be retained.
Which three Transact-SQL statements should you use?(To answer, move the appropriate statements from the
list of statements to the answer area and arrange them in the correct order.)

You administer a Microsoft SQL Server 2012 databasethat contains a table named OrderDetail. You discover
that the NCI_OrderDetail_CustomerID non-clustered index is fragmented. You need to reduce fragmentation.
You need to achieve this goal without taking the index offline. Which Transact-SQL batch should you use?

You develop a Microsoft SQL Server 2012 database. The database is used by two web applications that access
a table named Products. You want to create an object that will prevent the applications from accessingthe table
directly while still providing access to the required data. You need to ensure that the following requirements are
met:
Future modifications to the table definition will not affect the applications’ ability to access data.
The new object can accommodate data retrieval and data modification.
You need to achieve this goal by using the minimum amount of changes to the existing applications.
What should you create for each application?

You develop a Microsoft SQL Server 2012 database. You need to create a batch process that meets the
following requirements:
Returns a result set based on supplied parameters.
Enables the returned result set to perform a join with a table.
Which object should you use?

You develop a Microsoft SQL Server 2012 database.
You need to create and call a stored procedure thatmeets the following requirements:
Accepts a single input parameter for CustomerID.
Returns a single integer to the calling application.
Which Transact-SQL statement or statements should you use? (Each correct answer presents part of the
solution. Choose all that apply.)