Microsoft 70-461 Exam Practice Test Instant Access

Question 1

You administer a database that includes a table named Customers that contains more than 750 rows. You create a new column named PartitionNumber of the int type in the table.

You need to assign a PartitionNumber for each record in the Customers table. You also need to ensure that the PartitionNumber satisfies the following conditions:

* Always starts with 1.

* Starts again from 1 after it reaches 100.

Which Transact-SQL statement should you use?

ACREATE SEQUENCE CustomerSequence AS int
START WITH 0
INCREMENT BY 1
MINVALUE 1
MAXVALUE 100
UPDATE Customers SET PartitionNumber = NEXT VALUE FOR CustomerSequence
DROP SEQUENCE CustomerSequence

BCREATE SEQUENCE CustomerSequence AS int
START WITH 1
INCREMENT BY 1
MINVALUE 1
MAXVALUE 100
CYCLE
UPDATE Customers SET PartitionNumber = NEXT VALUE FOR CustomerSequence
DROP SEQUENCE CustomerSequence

CCREATE SEQUENCE CustomerSequence AS int
START WITH 1
INCREMENT BY 1
MINVALUE 1
MAXVALUE 100
UPDATE Customers SET PartitionNumber = NEXT VALUE FOR CustomerSequence + 1
DROP SEQUENCE CustomerSequence

DCREATE SEQUENCE CustomerSequence AS int
START WITH 1
INCREMENT BY 1
MINVALUE 0
MAXVALUE 100
CYCLE
UPTATE Customers SET PartitionNumber = NEXT VALUE FOR CustomerSequence
DROP SEQUENCE CustomerSequence

Answer : B

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

Question 2

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 3

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

You deploy a new server that has SQL Server 2012 installed. You need to create a table named

Sales.OrderDetails on the new server. Sales.OrderDetails must meet the following requirements:

* Write the results to a disk.

* Contain a new column named LineItemTotal that stores the product of ListPrice and Quantity for each row.

* The code must NOT use any object delimiters.

The solution must ensure that LineItemTotal is stored as the last column in the table. Which code segment should you use?

ACREATE TABLE Sales.OrderDetails (
ListPrice money not null,
Quantity int not null,
LineItemTotal as (ListPrice * Quantity) PERSISTED)

BCREATE TABLE Sales.OrderDetails (
Quantity int not null,
LineItemTotal as (ListPrice * Quantity) PERSISTED)

Answer : A

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

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

Question 4

You have a vendor application that uses a scalar function.

You discover that the queries for the application run slower than expected.

You need to gather the runtime information of the scalar function.

What are two possible ways to achieve this goal? Each correct answer presents a complete solution.

AEnable the Include Actual Execution Plan option.

BEnable the Display Estimated Execution Planoption.

CCreate and then enable a profiler trace.

DCreate and then enable an extended events trace.

ERun the Database Engine Tuning Advisor.

Answer : A, D

A: An execution plan is the result of the query optimizer's attempt to calculate the mostefficient way to implement the request represented by the T-SQL query you submitted. To generate the first execution plan, you can enable the Include Actual Execution Plan option.

D: SQL Server Extended Events can be used to capture User Defined Function(UDF) counts

References:https://www.mssqltips.com/sqlservertip/4100/how-to-find-udfs-causing-sql-server-performance-issues/

Question 5

You are a database developer for an application hosted on a Microsoft SQL Server 2012 server.

The database contains two tables that have the following definitions:

Global customers place orders from several countries.

You need to view the country from which each customer has placed the most orders.

Which Transact-SQL query do you use?

ASELECT CustomerID, CustomerName, ShippingCountry
FROM
(SELECT c.CustomerID, c.CustomerName, o.ShippingCountry,
RANK() OVER (PARTITION BY c.CustomerID
ORDER BY COUNT(o.OrderAmount) ASC) AS Rnk
FROM Customer c
INNER JOIN Orders o
ON c.CustomerID = o.CustomerID
GROUP BY c.CustomerID, c.CustomerName,
o.ShippingCountry) cs
WHERE Rnk = 1

BSELECT c.CustomerID, c.CustomerName, o.ShippingCountry
FROM Customer c
INNER JOIN
(SELECT CustomerID, ShippingCountry,
COUNT(OrderAmount) AS OrderAmount
FROM Orders
GROUP BY CustomerID, ShippingCountry) AS o
ON c.CustomerID = o.CustomerID
ORDER BY OrderAmount DESC

CSELECT CustomerID, CustomerName, ShippingCountry
FROM
(SELECT c.CustomerID, c.CustomerName,
o. ShippingCountry,
RANK() OVER (PARTITION BY c. CustomerID
ORDER BY o. OrderAmount DESC) AS Rnk
FROM Customer c
INNER JOIN Orders o
ON c.CustomerID = o.CustomerID
GROUP BY c.CustomerID, c.CustomerName,
o.ShippingCountry) cs
WHERE Rnk = 1

DSELECT c.CustomerID, c.CustomerName, o.ShippingCountry
FROM Customer c
INNER JOIN
(SELECT CustomerID, ShippingCountry,
RANK() OVER (PARTITION BY CustomerID
ORDER BY COUNT(OrderAmount) DESC) AS Rnk
FROM Orders
GROUP BY CustomerID, ShippingCountry) AS o
ON c.CustomerID = o.CustomerID
Where o.Rnk = 1

Answer : C

Question 6

You need to execute an UPDATE statement that modifies a subset of rows in a table. The rows affected by the UPDATE statement must only be committed if the row count criteria is met.

Part of the correct Transact-SQL has been provided in the answer are 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.

Use the 'Check Syntax' button to verify your work. Any syntax or spelling errors will be reported by line and character position.

A1 DECLARE @ExprectedRowCount int = 0
2.
3. BEGIN TRAN OrderUpdate
4.
5. UPDATE dbo.OrderHeader
6. SET PromisedDate = DATEADD(dd, 5, PromisedDate)
7. WHERE CustomerID = 0432
8. AND Shipped =0
9.
10. If @@RowCount = @ExpectedRowCount
11.
12. COMMIT OrderUpdate
13.
14. else
15.
16. ROLLBACK OrderUpdate
On line 3 add: BEGIN TRAN
On line 12 add: COMMIT
On line 16 add: ROLLBACK

B1. DECLARE @ExprectedRowCount int = 0
2.
3. BEGIN TRAN OrderUpdate
4.
5. UPDATE dbo.OrderHeader
6. SET PromisedDate = DATEADD(dd, 5, PromisedDate)
7. WHERE CustomerID = 0432
8. AND Shipped =0
9.
10. If @@RowCount = @ExpectedRowCount
11.
12. ROLLBACK OrderUpdate
On line 3 add: BEGIN TRAN
On line 12 add: COMMIT
On line 16 add: ROLLBACK

Answer : A

References: https://docs.microsoft.com/en-us/sql/t-sql/language-elements/begin-transaction-transact-sql

Question 7

You create a table that has the StudentCode, SubjectCode, and Marks columns to record mid-year marks for students. The table has marks obtained by 50 students for various subjects.

You need to ensure that the following requirements are met:

* Students must be ranked based on their average marks.

* If one or more students have the same average, the same rank must be given to these students.

* Ranks must be sequential without gaps in between.

Which Transact-SQL query should you use?

AOption A

BOption B

COption C

DOption D

EOption E

FOption F

GOption G

HOption H

Answer : B

The DENSE_RANK function returns the rank of each row within a result set partition, with no gaps in the ranking values. The rank of a specific row is one plus the number of distinct rank values that come before that specific row.

References: https://docs.microsoft.com/en-us/sql/t-sql/functions/dense-rank-transact-sql?view=sql-server-2017

Microsoft 70-461 Querying Microsoft SQL Server 2012/2014 Exam Practice Test