Microsoft 70-461 Free Practice Questions

Q1. CORRECT TEXT 

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

You have an application named Appl. You have a parameter named @Count that uses the 

int data type. App1 is configured to pass @Count to a stored procedure. 

You need to create a stored procedure named usp_Customers for App1 that returns only 

the number of rows specified by the @Count parameter. 

The solution must NOT use BEGIN and END statements. 

Part of the correct T-SQL statement has been provided in the answer area. Provide the 

complete code. 

View Answer

Q2. DRAG DROP 

You create a view based on the following statement: 

You grant the Select permission to User1 for this view. 

You need to change the view so that it displays only the records that were processed in the month prior to the current month. You need to ensure that after the changes, the view functions correctly for User1. 

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.) 

View Answer

Q3. You use Microsoft SQL Server 2012 database to develop a shopping cart application. 

You need to rotate the unique values of the ProductName field of a table-valued expression into multiple columns in the output. 

Which Transact-SQL operator should you use? 

A. CROSS JOIN 

B. CROSS APPLY 

C. PIVOT 

D. UNPIVOT 

View Answer

Q4. CORRECT TEXT 

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

You need to create a query that returns a list of products from Sales.ProductCatalog. The solution must meet the following requirements: 

UnitPrice must be returned in descending order. 

The query must use two-part names to reference the table. 

The query must use the RANK function to calculate the results. 

The query must return the ranking of rows in a column named PriceRank. 

The list must display the columns in the order that they are defined in the table. 

PriceRank must appear last. 

Which code segment should you use? 

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

View Answer

Q5. Your database contains a table named SalesOrders. The table includes a DATETIME column named OrderTime that stores the date and time each order is placed. There is a non-clustered index on the OrderTime column. 

The business team wants a report that displays the total number of orders placed on the current day. 

You need to write a query that will return the correct results in the most efficient manner. 

Which Transact-SQL query should you use? 

A. SELECT COUNT(*) FROM SalesOrders 

WHERE OrderTime = CONVERT(DATE, GETDATE()) 

B. SELECT COUNT(*) FROM SalesOrders 

WHERE OrderTime = GETDATE() 

C. SELECT COUNT(*) FROM SalesOrders 

WHERE CONVERT(VARCHAR, OrderTime, 112) = CONVERT(VARCHAR, GETDATE(I, 

112)) 

D. SELECT COUNT(*) FROM SalesOrders 

WHERE OrderTime >= CONVERT(DATE, GETDATE()) 

AND OrderTime < DATEADD(DAY, 1, CONVERT(DATE, GETDATE())) 

View Answer

Q6. You develop a Microsoft SQL Server 2012 database. You create a view from the Orders and OrderDetails tables by using the following definition. 

You need to improve the performance of the view by persisting data to disk. What should you do? 

A. Create an INSTEAD OF trigger on the view. 

B. Create an AFTER trigger on the view. 

C. Modify the view to use the WITH VIEW_METADATA clause. 

D. Create a clustered index on the view. 

View Answer

Q7. You use Microsoft SQL Server 2012 to develop a database application. 

Your application sends data to an NVARCHAR(MAX) variable named @var. 

You need to write a Transact-SQL statement that will find out the success of a cast to a 

decimal (36,9). 

Which code segment should you use? 

A. BEGIN TRY SELECT convert(decimal(36,9), @var) AS Value, 'True' AS BadCast END TRY BEGIN CATCH SELECT convert(decimal(36,9), @var) AS Value, 'False' AS BadCast END CATCH 

B. TRY( SELECT convert(decimal(36,9), @var) SELECT 'True' AS BadCast ) CATCH( SELECT 'False' AS BadCast ) 

C. SELECT CASE WHEN convert(decimal(36,9), @var) IS NULL THEN 'True' ELSE 'False' END AS BadCast 

D. SELECT IIF(TRY_PARSE(@var AS decimal(36,9)) IS NULL, 'True', 'False') AS BadCast 

View Answer

Q8. You use Microsoft SQL Server 2012 to write code for a transaction that contains several statements. 

There is high contention between readers and writers on several tables used by your transaction. 

You need to minimize the use of the tempdb space. You also need to prevent reading queries from blocking writing queries. 

Which isolation level should you use? 

A. SERIALIZABLE 

B. SNAPSHOT 

C. READ COMMITTED SNAPSHOT 

D. REPEATABLE READ 

View Answer

Q9. You administer a Microsoft SQL Server 2012 database that 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? 

A. CREATE INDEX NCI_OrderDetail_CustomerID ON OrderDetail.CustomerID WITH DROP EXISTING 

B. ALTER INDEX NCI_OrderDetail_CustomerID ON OrderDetail.CustomerID REORGANIZE 

C. ALTER INDEX ALL ON OrderDetail REBUILD 

D. ALTER INDEX NCI_OrderDetail_CustomerID ON OrderDetail.CustomerID REBUILD 

View Answer

Q10. You generate a daily report according to the following query: 

You need to improve the performance of the query. 

What should you do? 

A. Drop the UDF and rewrite the report query as follows: 

WITH cte(CustomerID, LastOrderDate) AS ( 

SELECT CustomerID, MAX(OrderDate) AS [LastOrderDate] 

FROM Sales.SalesOrder 

GROUP BY CustomerID 

) 

SELECT c.CustomerName 

FROM cte 

INNER JOIN Sales.Customer c 

ON cte.CustomerID = c.CustomerID 

WHERE cte.LastOrderDate < DATEADD(DAY, -90, GETDATE()) 

B. Drop the UDF and rewrite the report query as follows: 

SELECT c.CustomerName 

FROM Sales.Customer c 

WHERE NOT EXISTS ( 

SELECT s.OrderDate 

FROM Sales.SalesOrder 

WHERE s.OrderDate > DATEADD(DAY, -90, GETDATE()) 

AND s.CustomerID = c.CustomerID) 

C. Drop the UDF and rewrite the report query as follows: 

SELECT DISTINCT c.CustomerName 

FROM Sales.Customer c 

INNER JOIN Sales.SalesOrder s 

ON c.CustomerID = s.CustomerID 

WHERE s.OrderDate < DATEADD(DAY, -90, GETDATE()) 

D. Rewrite the report query as follows: 

SELECT c.CustomerName 

FROM Sales.Customer c 

WHERE NOT EXISTS (SELECT OrderDate FROM 

Sales.ufnGetRecentOrders(c.CustomerID, 90)) 

Rewrite the UDF as follows: 

CREATE FUNCTION Sales.ufnGetRecentOrders(@CustomerID int, @MaxAge datetime) 

RETURNS TABLE AS RETURN ( 

SELECT OrderDate 

FROM Sales.SalesOrder 

WHERE s.CustomerID = @CustomerID 

AND s.OrderDate > DATEADD(DAY, -@MaxAge, GETDATE()) 

View Answer

Q11. You are developing a database application by using Microsoft SQL Server 2012. You have a query that runs slower than expected. 

You need to capture execution plans that will include detailed information on missing indexes recommended by the query optimizer. 

What should you do? 

A. Add a HASH hint to the query. 

B. Add a LOOP hint to the query. 

C. Add a FORCESEEK hint to the query. 

D. Add an INCLUDE clause to the index. 

E. Add a FORCESCAN hint to the Attach query. 

F. Add a columnstore index to cover the query. 

G. Enable the optimize for ad hoc workloads option. 

H. Cover the unique clustered index with a columnstore index. 

I. Include a SET FORCEPLAN ON statement before you run the query. 

J. Include a SET STATISTICS PROFILE ON statement before you run the query. 

K. Include a SET STATISTICS SHOWPLAN_XML ON statement before you run the query. 

L. Include a SET TRANSACTION ISOLATION LEVEL REPEATABLE READ statement before you run the query. 

M. Include a SET TRANSACTION ISOLATION LEVEL SNAPSHOT statement before you run the query. 

N. Include a SET TRANSACTION ISOLATION LEVEL SERIALIZABLE statement before you run the query. 

View Answer

Q12. Your database contains tables named Products and ProductsPriceLog. The Products table contains columns named ProductCode and Price. The ProductsPriceLog table contains columns named ProductCode, OldPrice, and NewPrice. 

The ProductsPriceLog table stores the previous price in the OldPrice column and the new price in the NewPrice column. 

You need to increase the values in the Price column of all products in the Products table by 5 percent. You also need to log the changes to the ProductsPriceLog table. 

Which Transact-SQL query should you use? 

A. UPDATE Products SET Price = Price * 1.05 

OUTPUT inserted.ProductCode, deleted.Price, inserted.Price 

INTO ProductsPriceLog(ProductCode, OldPrice, NewPrice) 

B. UPDATE Products SET Price = Price * 1.05 

OUTPUT inserted.ProductCode, inserted.Price, deleted.Price 

INTO ProductsPriceLog(ProductCode, OldPrice, NewPrice) 

C. UPDATE Products SET Price = Price * 1.05 

OUTPUT inserted.ProductCode, deleted.Price, inserted.Price * 

INTO ProductsPriceLog(ProductCode, OldPrice, NewPrice) 

D. UPDATE Products SET Price = Price * 1.05 

INSERT INTO ProductsPriceLog (ProductCode, CldPnce, NewPrice; 

SELECT ProductCode, Price, Price * 1.05 FROM Products 

View Answer

Q13. You use Microsoft SQL Server 2012 to create a stored procedure as shown in the following code segment. (Line numbers are included for reference only.) 

The procedure can be called within other transactions. 

You need to ensure that when the DELETE statement from the HumanResourcesJobCandidate table succeeds, the modification is retained even if the insert into the Audit.Log table fails. 

Which code segment should you add to line 14? 

A. IF @@TRANCOUNT = 0 

B. IF (XACT_STATE ( ) ) = 0 

C. IF (XACT_STATE ( ) ) = 1 

D. IF @@TRANCOUNT = l 

View Answer

Q14. You use a Microsoft SQL Server 2012 database that contains two tables named SalesOrderHeader and SalesOrderDetail. The indexes on the tables are as shown in the exhibit. (Click the Exhibit button.) 

You write the following Transact-SQL query: 

You discover that the performance of the query is slow. Analysis of the query plan shows table scans where the estimated rows do not match the actual rows for SalesOrderHeader by using an unexpected index on SalesOrderDetail. 

You need to improve the performance of the query. 

What should you do? 

A. Use a FORCESCAN hint in the query. 

B. Add a clustered index on SalesOrderId in SalesOrderHeader. 

C. Use a FORCESEEK hint in the query. 

D. Update statistics on SalesOrderId on both tables. 

View Answer

Q15. You use Microsoft SQL Server 2012 to develop a database application. You need to create an object that meets the following requirements: 

Takes an input variable 

Returns a table of values 

Cannot be referenced within a view 

Which object should you use? 

A. Scalar-valued function 

B. Inline function 

C. User-defined data type 

D. Stored procedure 

View Answer

Q16. CORRECT TEXT 

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

You need to create a query that returns a list of products from Sales.ProductCatalog. The solution must meet the following requirements: 

UnitPrice must be returned in descending order. 

The query must use two-part names to reference the table. 

The query must use the RANK function to calculate the results. 

The query must return the ranking of rows in a column named PriceRank. 

The list must display the columns in the order that they are defined in the table. 

PriceRank must appear last. 

Part of the correct T-SQL statement has been provided in the answer area. Provide the complete code. 

View Answer

Q17. 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? 

A. SQL user without login 

B. SQL user with a custom SID 

C. SQL user with login 

D. Domain user 

View Answer