Microsoft 70-461 Exam Questions Instant Access

Question 1

You develop a Microsoft SQL Server 2012 database. You need to create a batch process that meets the following requirements:

* Status information must be logged to a status table.

* If the status table does not exist at the beginning of the batch, it must be created.

Which object should you use?

AScalar user-defined function

BInline user-defined function

CTable-valued user-defined function

DStored procedure

Answer : D

http://msdn.microsoft.com/en-us/library/ms186755.aspx

Question 2

You administer a Microsoft SQL Server 2012 database that has Trustworthy set to On. You create a stored procedure that returns database-level information from Dynamic Management Views. You grant User1 access to execute the stored procedure. You need to ensure that the stored procedure returns the required information when User1 executes the stored procedure. You need to achieve this goal by granting the minimum permissions required. What should you do? (Each correct answer presents a complete solution. Choose all that apply.)

ACreate a SQL Server login that has VIEW SERVER STATE permissions. Create an application role and a secured password for the role.

BModify the stored procedure to include the EXECUTE AS OWNER statement. Grant VIEW SERVER STATE permissions to the owner of the stored procedure.

CCreate a SQL Server login that has VIEW SERVER STATE permissions. Modify the stored procedure to include the EXECUTE AS {newlogin} statement.

DGrant the db_owner role on the database to User1.

EGrant the sysadmin role on the database to User1.

Answer : D, E

Question 3

You have a database that contains the tables shown in the exhibit. (Click the Exhibit button.)

You need to create a query that calculates the total sales of each OrderId from the Sales.Details table. The solution must meet the following requirements:

* Use one-part names to reference columns.

* Sort the order of the results from OrderId.

* NOT depend on the default schema of a user.

* Use an alias of TotalSales for the calculated ExtendedAmount.

* Display only the OrderId column and the calculated TotalSales column.

Which code segment should you use?

To answer, type the correct code in the answer area.

ASELECT OrderID, SUM(ExtendedAmount) AS TotalSales
FROM Sales.Details
ORDER BY OrderID

BSELECT OrderID, SUM(ExtendedAmount) AS TotalSales
FROM Sales.Details
GROUP BY OrderID
ORDER BY OrderID

Answer : B

Question 4

You have an XML schema collection named Sales.InvoiceSchema.

You need to declare a variable of the XML type named XML1. The solution must ensure that XML1 is validated by using Sales.InvoiceSchema.

Which code segment should you use?

To answer, type the correct code in the answer area.

See Below Explanation:

http://msdn.microsoft.com/en-us/library/ms176009.aspx

Answer : A

http://msdn.microsoft.com/en-us/library/ms176009.aspx

Question 5

Note: This question is part of series of questions that use the same or similar answer choices. An answer choice may be correct for more than one question in the series. Each question is independent of the other questions in the series. Information and details provided in a question apply only to that question.

You administer a Microsoft SQL Server database.

The database contains a table named Employee. Part of the Employee table is shown in the exhibit. (Click the Exhibit button.)

Unless stated above, no columns in the Employee table reference other tables.

Confidential information about the employees is stored in a separate table named EmployeeData. One record exists within EmployeeData for each record in the Employee table.

You need to assign the appropriate constraints and table properties to ensure data integrity and visibility.

Which column in the Employee table should be referenced in a one-to-one relationship by the EmployeeData table?

ADateHired

BDepartmentID

CEmployeeID

DEmployeeNum

EFirstName

FJobTitle

GLastName

HMiddleName

IReportsToID

Answer : C

Question 6

You develop a Microsoft SQL Server 2012 database.

You need to create and call a stored procedure that meets 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.)

ACREATE PROCEDURE dbo.GetCustomerRating
@CustomerID INT,
@CustomerRating INT OUTPUT
AS
SET NOCOUNT ON
SELECT @CustomerRating = CustomerOrders/CustomerValue
FROM Customers
WHERE CustomerID = @CustomerID
RETURN
GO

BEXECUTE dbo.GetCustomerRating 1745

CDECLARE @CustomerRatingByCustomer INT
DECLARE @Result INT
EXECUTE @Result = dbo.GetCustomerRating
1745,
@CustomerRatingByCustomer

DCREATE PROCEDURE dbo.GetCustomerRating
@CustomerID INT,
@CustomerRating INT OUTPUT
AS
SET NOCOUNT ON
SELECT @Result = CustomerOrders/CustomerValue
FROM Customers
WHERE CustomerID = @CustomerID
RETURN @Result
GO

EDECLARE @CustomerRatingByCustomer INT
EXECUTE dbo.GetCustomerRating
@CustomerID = 1745,
@CustomerRating = @CustomerRatingByCustomer OUTPUT

FCREATE PROCEDURE dbo.GetCustomerRating
@CustomerID INT
AS
DECLARE @Result INT
SET NOCOUNT ON
SELECT @Result = CustomerOrders/CustomerValue
FROM Customers
WHERE CustomerID = @CustomerID
RETURNS @Result
GO

Answer : A, E

Question 7

You are designing a Microsoft SQL Server database named Orders.

You create a table by running the following Transact-SQL statement:

You create a stored procedure to be used by an ASP.NET application that runs the following statement:

You need to ensure that the query runs as efficiently as possible.

Which Transact-SQL statement should you run?

AOption A

BOption B

COption C

DOption D

Answer : B

Creating a primary key automatically creates a corresponding unique clustered index, or a nonclustered index if specified as such.

Example: To create a primary key in an existing table

The following example creates a primary key on the column TransactionID in the AdventureWorks database.

ALTER TABLE Production.TransactionHistoryArchive

ADD CONSTRAINT PK_TransactionHistoryArchive_TransactionID PRIMARY KEY CLUSTERED (TransactionID);

References:

https://docs.microsoft.com/en-us/sql/relational-databases/tables/create-primary-keys

Microsoft Querying Microsoft SQL Server 2012/2014 70-461 Exam Questions