Microsoft 70-461 Exam Questions Instant Access

Question 1

You have a SQL Server database that contains all of the sales data for your company.

You need to create a query that returns the customers who represent the top five percent of the total actual sales.

Part of the correct Transact-SQL has been provided in the answer area below. Enter the code in the answer area that resolves the problem and meets the stated goals or requirements. You can add code within the code that has been provided as well as below it.

A1 SELECT CustomerID, N,
2 Top5= TOP 5 PERCENT MaX (0.95)
3 RANK() (ORDER BY N) OVER (PARTITION BY Sales)
4
On line 2 add TOP 5 PERCENT
On line 3 add: RANK()

B1 SELECT CustomerID, N,
2 Top5= TOP 5
3 RANK() (ORDER BY N) OVER (PARTITION BY Sales)
4
On line 2 add TOP 5 PERCENT
On line 3 add: RANK()

Answer : A

References:

https://docs.microsoft.com/en-us/sql/t-sql/queries/select-over-clause-transact-sql

Question 2

A local bank uses a SQL Server database to manage accounts. You are developing a stored procedure that contains multiple Transact-SQL INSERT statements.

The stored procedure must use transaction management to handle errors.

You need to ensure that the stored procedure rolls back the entire transaction if a run-time occurs.

Which Transact-SQL statement should you add to the stored procedure?

ASET ARITHABORT ON

BSET NOEXEC ON

CSET TRANSACTION ISOLATION LEVEL ON

DSET XACT_ABORT ON

Answer : D

SET XACT_ABORT specifies whether SQL Server automatically rolls back the current transaction when a Transact-SQL statement raises a run-time error.

When SET XACT_ABORT is ON, if a Transact-SQL statement raises a run-time error, the entire transaction is terminated and rolled back.

References: https://docs.microsoft.com/en-us/sql/t-sql/statements/set-xact-abort-transact-sql?view=sql-server-2017

Question 3

You have a Microsoft SQL Server 2012 database that contains tables named Customers and Orders.

The tables are related by a column named CustomerID.

You need to create a query that meets the following requirements:

* Returns the CustomerName for all customers and the OrderDate for any orders that they have placed.

* Results must include customers who have not placed any orders.

Which Transact-SQL query should you use?

ASELECT CustomerName, OrderDate
FROM Customers
RIGHT OUTER JOIN Orders
ON Customers.CustomerID = Orders.CustomerID

BSELECT CustomerName, OrderDate
FROM Customers
JOIN Orders
ON Customers.CustomerID = Orders.CustomerID

CSELECT CustomerName, OrderDate
FROM Customers
CROSS JOIN Orders
ON Customers.CustomerID = Orders.CustomerID

DSELECT CustomerName, OrderDate
FROM Customers
LEFT OUTER JOIN Orders
ON Customers.CustomerID = Orders.CustomerID

Answer : D

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

Question 4

You develop a database application for Microsoft SQL Server and Microsoft Azure SQL Database.

You need to raise an exception and transfer execution to a CATCH block.

You need to ensure that the exception returns output in the following format:

Msg 51000, Level 16, State 1, Line 1

The record does not exist.

Which Transact-SQL statement should you run?

AOption A

BOption B

COption C

DOption D

Answer : B

The following example shows how to use the THROW statement to raise an exception.

THROW 51000, 'The record does not exist.', 1;

Here is the result set.

Msg 51000, Level 16, State 1, Line 1

The record does not exist.

References: https://docs.microsoft.com/en-us/sql/t-sql/language-elements/throw-transact-sql?view=sql-server-2017

Question 5

Your database contains a table named Products that has columns named ProductID and Name.

You want to write a query that retrieves data from the Products table sorted by Name listing 15 rows at a time.

You need to view rows 31 through 45.

Which Transact-SQL query should you create?

AOption A

BOption B

COption C

DOption D

Answer : C

The OFFSET-FETCH clause provides you with an option to fetch only a window or page of results from the result set. OFFSET-FETCH can be used only with the ORDER BY clause.

Example: Skip first 10 rows from the sorted resultset and return next 5 rows.

SELECT First Name + ' ' + Last Name FROM Employees ORDER BY First Name OFFSET 10 ROWS FETCH NEXT 5 ROWS ONLY;

References: https://technet.microsoft.com/en-us/library/gg699618(v=sql.110).aspx

Question 6

You use a contained database named ContosoDb within a domain. You need to create a user who can log on to the ContosoDb database. You also need to ensure that you can port the database to different database servers within the domain without additional user account configurations.

Which type of user should you create?

ASQL user without login

BSQL user with a custom SID

CSQL user with login

DDomain user

Answer : A

Question 7

You are a database developer at an independent software vendor. You create stored procedures that contain proprietary code.

You need to protect the code from being viewed by your customers.

Which stored procedure option should you use?

AENCRYPTBYKEY

BENCRYPTION

CENCRYPTBYPASSPHRASE

DENCRYPTBYCERT

Answer : B

http://technet.microsoft.com/en-us/library/bb510663.aspx

http://technet.microsoft.com/en-us/library/ms174361.aspx

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

http://technet.microsoft.com/en-us/library/ms190357.aspx

http://technet.microsoft.com/en-us/library/ms188061.aspx

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