All the essential guides are in here to help you prepare to the Microsoft 70-461 exam. We have developed our Study guides, Q&As with Detailed Explanations, Total Preparation labs to ensure you pass your analyze on the very first try. The particular Pdf files are printable and also portable. You can carry these with you and evaluation the Microsoft exam questions and also answers anytime and anywhere. If you dont pass your Microsoft Microsoft exam, Testking.com may offer you full refund or perhaps another free product according to your need to have. You should get in touch with our buyer support and also claim the exam dump youd like. If you claim your full money , you should email us your current failed transcript. Our workers may remit you soon after making sure your information.

2021 Mar 70-461 practice test

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

Answer: 


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

A. Scalar user-defined function 

B. Inline user-defined function 

C. Table-valued user-defined function 

D. Stored procedure 

Answer: D 


Q33. You develop a database application. You create four tables. Each table stores different categories of products. 

You create a Primary Key field on each table. 

You need to ensure that the following requirements are met: 

The fields must use the minimum amount of space. 

The fields must be an incrementing series of values. 

The values must be unique among the four tables. 

What should you do? 

A. Create a ROWVERSION column. 

B. Create a SEQUENCE object that uses the INTEGER data type. 

C. Use the INTEGER data type along with IDENTITY 

D. Use the UNIQUHDENTTFIER data type along with NEWSEQUENTIALID() 

E. Create a TIMESTAMP column. 

Answer: D 


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

Answer: D 


Q35. You administer a Microsoft SQL Server 2012 database named ContosoDb. Tables are defined as shown in the exhibit. (Click the Exhibit button.) 

You need to display rows from the Orders table for the Customers row having the 

CustomerId value set to 1 in the following XML format. 

<CUSTOMERS Name="Customer A" Country="Australia"> 

<ORDERS OrderID="1" OrderDate="2001-01-01" Amount="3400.00" /> 

<ORDERS OrderID="2" OrderDate="2002-01-01" Amount="4300.00" /> 

</CUSTOMERS> 

Which Transact-SQL query should you use? 

A. SELECT OrderId, OrderDate, Amount, Name, Country FROM Orders INNER JOIN Customers ON Orders.CustomerId = Customers.CustomerId WHERE Customers.CustomerId = 1 FOR XML RAW 

B. SELECT OrderId, OrderDate, Amount, Name, Country FROM Orders INNER JOIN Customers ON Orders.CustomerId = Customers.CustomerId WHERE Customers.CustomerId = 1 FOR XML RAW, ELEMENTS 

C. SELECT OrderId, OrderDate, Amount, Name, Country FROM Orders INNER JOIN Customers ON Orders.CustomerId = Customers.CustomerId WHERE Customers.CustomerId = 1 FOR XML AUTO 

D. SELECT OrderId, OrderDate, Amount, Name, Country FROM Orders INNER JOIN Customers ON Orders.CustomerId - Customers.CustomerId WHERE Customers.CustomerId= 1 FOR XML AUTO, ELEMENTS 

E. SELECT Name, Country, OrderId, OrderDate, Amount FROM Orders INNER JOIN Customers ON Orders.CustomerId= Customers.CustomerId WHERE Customers.CustomerId= 1 FOR XML AUTO 

F. SELECT Name, Country, Orderld, OrderDate, Amount FROM Orders INNER JOIN Customers ON Orders.CustomerId= Customers.CustomerId WHERE Customers.CustomerId= 1 FOR XML AUTO, ELEMENTS 

G. SELECT Name AS '@Name', Country AS '@Country', OrderId, OrderDate, Amount FROM Orders INNER JOIN Customers ON Orders.CustomerId= Customers.CustomerId WHERE Customers.CustomerId= 1 FOR XML PATH ('Customers') 

H. SELECT Name AS 'Customers/Name', Country AS 'Customers/Country', OrderId, OrderDate, Amount FROM Orders INNER JOIN Customers ON Orders.CustomerId= Customers.CustomerId WHERE Customers.CustomerId= 1 FOR XML PATH ('Customers') 

Answer: E 


Leading 70-461 test question:

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

Answer: A 


Q37. 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 accessing the 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? 

A. views 

B. table partitions 

C. table-valued functions 

D. stored procedures 

Answer: A 


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

Answer: C 


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

Answer: 


Q40. 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 retrieve the students who scored the highest marks for each subject along with the marks. 

Which Transact-SQL query should you use? 

A. SELECT StudentCode as Code, RANK() OVER(ORDER BY AVG(Marks) DESC) AS Value FROM StudentMarks GROUP BY StudentCode 

B. SELECT Id, Name, Marks, DENSE_RANK() OVER(ORDER BY Marks DESC) AS Rank FROM StudentMarks 

C. SELECT StudentCode as Code, DENSE_RANK() OVER(ORDER BY AVG(Marks) DESC) AS Value FROM StudentMarks GROUP BY StudentCode 

D. SELECT StudentCode as Code, NTILE(2) OVER(ORDER BY AVG(Marks) DESC) AS Value FROM StudentMarks GROUP BY StudentCode 

E. SELECT StudentCode AS Code, Marks AS Value FROM ( SELECT StudentCode, Marks AS Marks, RANK() OVER(PARTITION BY SubjectCode ORDER BY Marks ASC) AS Rank FROM StudentMarks) tmp WHERE Rank = 1 

F. SELECT StudentCode AS Code, Marks AS Value FROM ( SELECT StudentCode, Marks AS Marks, RANK() OVER(PARTITION BY SubjectCode ORDER BY Marks DESC) AS Rank FROM StudentMarks) tmp WHERE Rank = 1 

G. SELECT StudentCode AS Code, Marks AS Value FROM ( SELECT StudentCode, Marks AS Marks, RANK() OVER(PARTITION BY StudentCode ORDER BY Marks ASC) AS Rank FROM StudentMarks) tmp WHERE Rank = 1 

H. SELECT StudentCode AS Code, Marks AS Value FROM ( SELECT StudentCode, Marks AS Marks, RANXO OVER(PARTITION BY StudentCode ORDER BY Marks DESC) AS Rank FROM StudentMarks) tmp WHERE Rank = 1 

Answer: F