Tips to Pass 70-461 Exam (31 to 45)

70-461 Royal Pack Testengine pdf

100% Actual & Verified — 100% PASS

Unlimited access to the world's largest Dumps library! Try it Free Today!

Get it now →

Product Description:
Exam Number/Code: 70-461
Exam name: Querying Microsoft SQL Server 2012
n questions with full explanations
Certification: Microsoft Certification
Last updated on Global synchronizing

Free Certification Real IT 70-461 Exam pdf Collection

Actual of 70-461 practice materials and preparation labs for Microsoft certification for IT professionals, Real Success Guaranteed with Updated 70-461 pdf dumps vce Materials. 100% PASS Querying Microsoft SQL Server 2012 exam Today!

2016 Apr 70-461 Study Guide Questions:

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

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

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

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

D. Grant the db_owner role on the database to User1. 

E. Grant the sysadmin role on the database to User1. 

Answer: D,E 


Q32. Your application contains a stored procedure for each country. Each stored procedure accepts an employee identification number through the @EmpID parameter. 

You plan to build a single process for each employee that will execute the stored procedure based on the country of residence. 

Which approach should you use? 

A. A recursive stored procedure 

B. Trigger 

C. An UPDATE statement that includes CASE 

D. Cursor 

E. The foreach SQLCLR statement 

Answer: D 


Q33. You administer a Microsoft SQL Server 2012 database that includes a table named Products. The Products table has columns named Productld, ProductName, and CreatedDateTime. 

The table contains a unique constraint on the combination of ProductName and CreatedDateTime. 

You need to modify the Products table to meet the following requirements: 

. Remove all duplicates of the Products table based on the ProductName column. . Retain only the newest Products row. 

Which Transact-SQL query should you use? 

A. WITH CTEDupRecords AS ( SELECT MAX(CreatedDateTime) AS CreatedDateTime, ProductName FROM Products GROUP BY ProductName HAVING COUNT(*) > 1 ) DELETE p FROM Products p JOIN CTEDupRecords cte ON B. ProductName = cte.ProductName AND p.CreatedDateTime > cte.CreatedDateTime 

C. WITH CTEDupRecords AS ( SELECT MAX(CreatedDateTime) AS CreatedDateTime, ProductName FROM Products GROUP BY ProductName HAVING COUNT(*) > 1 ) DELETE p FROM Products p JOIN CTEDupRecords cte ON cte.ProductName = p.ProductName AND cte.CreatedDateTime > p.CreatedDateTime 

D. WITH CTEDupRecords AS ( SELECT MIN(CreatedDateTime) AS CreatedDateTime, ProductName FROM Products GROUP BY ProductName ) DELETE p FROM Products p JOIN CTEDupRecords cte ON 

E. ProductName = cte.ProductName 

F. WITH CTEDupRecords AS ( SELECT MAX(CreatedDateTime) AS CreatedDateTime, ProductName FROM Products GROUP BY ProductName HAVING COUNT(*) > 1 ) DELETE p FROM Products p JOIN CTEDupRecords cte ON 

G. ProductName = cte.ProductName 

Answer: B 


Q34. DRAG DROP 

You want to add a new GUID column named BookGUID to a table named dbo.Book that already contains data. 

BookGUID will have a constraint to ensure that it always has a value when new rows are inserted into dbo.Book. 

You need to ensure that the new column is assigned a GUID for existing rows. 

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: 



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

Answer: D 


70-461 sample question

Avant-garde 70-461 exam fees:

Q36. 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 Customerld value set to 1 in the following XML format. 


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, Crderld, 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: G 


Q37. You develop a Microsoft SQL Server 2012 database that has two tables named SavingAccounts and LoanAccounts. Both tables have a column named AccountNumber of the nvarchar data type. 

You use a third table named Transactions that has columns named TransactionId AccountNumber, Amount, and TransactionDate. 

You need to ensure that when multiple records are inserted in the Transactions table, only the records that have a valid AccountNumber in the SavingAccounts or LoanAccounts are inserted. 

Which Transact-SQL statement should you use? 

A. CREATE TRIGGER TrgValidateAccountNumber ON Transactions INSTEAD OF INSERT AS BEGIN INSERT INTO Transactions SELECT TransactionID,AccountNumber,Amount,TransactionDate FROM inserted WHERE AccountNumber IN (SELECT AccountNumber FROM LoanAccounts UNION SELECT AccountNumber FROM SavingAccounts)) END 

B. CREATE TRIGGER TrgValidateAccountNumber ON Transactions FOR INSERT AS BEGIN INSERT INTO Transactions SELECT TransactionID,AccountNumber,Amount,TransactionDate FROM inserted WHERE AccountNumber IN (SELECT AccountNumber FROM LoanAccounts UNION SELECT AccountNumber FROM SavingAccounts)) END 

C. CREATE TRIGGER TrgValidateAccountNumber ON Transactions INSTEAD OF INSERT AS BEGIN IF EXISTS ( SELECT AccountNumber FROM inserted EXCEPT (SELECT AccountNumber FROM LoanAccounts UNION SELECT AccountNumber FROM SavingAccounts)) BEGIN ROLLBACK TRAN END END 

D. CREATE TRIGGER TrgValidateAccountNumber ON Transactions FOR INSERT AS BEGIN IF EXISTS ( SELECT AccountNumber FROM inserted EXCEPT (SELECT AccountNumber FROM LoanAccounts UNION SELECT AccountNumber FROM SavingAccounts)) BEGIN ROLLBACK TRAN END END 

Answer: A 


Q38. CORRECT TEXT 

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? 

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

Answer: 


Q39. CORRECT TEXT 

You have a database that contains the tables as shown below: 


You have a stored procedure named Procedure1. Procedure1 retrieves all order ids after a specific date. The rows for Procedure1 are not sorted. Procedure1 has a single parameter named Parameter1. Parameter1 uses the varchar type and is configured to pass the specific date to Procedure1. A database administrator discovers that OrderDate is not being compared correctly to Parameter1 after the data type of the column is changed to datetime. You need to update the SELECT statement to meet the following requirements: 

The code must NOT use aliases. 

The code must NOT use object delimiters. 

The objects called in Procedure1 must be able to be resolved by all users. 

OrderDate must be compared to Parameter1 after the data type of Parameter1 is 

changed to datetime. 

Which SELECT statement should you use? 

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

Answer: 


Q40. 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: 


70-461 answers

Certified 70-461 guidance:

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

Answer: A 


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

A. ENCRYPTBYKEY 

B. ENCRYPTION 

C. ENCRYPTBYPASSPHRASE 

D. ENCRYPTBYCERT 

Answer: B 


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

Answer: D 


Q44. CORRECT TEXT 

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. 

Answer: 

DECLARE @XML1 XML(Sales.InvoiceSchema) 


Q45. 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 ensure that users are able to modify data by using the view. 

What should you do? 

A. Create an AFTER trigger on the view. 

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

C. Create an INSTEAD OF trigger on the view. 

D. Modify the view to an indexed view. 

Answer: C 



see more 70-461 - Querying Microsoft SQL Server 2012