70-461 Exam Questions - Online Test

70-461 Premium VCE File

Learn More 100% Pass Guarantee - Dumps Verified - Instant Download
150 Lectures, 20 Hours

Cause all that matters here is passing the Microsoft 70 461 querying microsoft sql server 2012 exam. Cause all that you need is a high score of 70 461 vce Querying Microsoft SQL Server 2012 exam. The only one thing you need to do is downloading Testking microsoft exam 70 461 exam study guides now. We will not let you down with our money-back guarantee.

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

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: D 

Q52. CORRECT TEXT 

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

You need to create a view named uv_CustomerFullName to meet the following requirements: 

The code must NOT include object delimiters. 

The view must be created in the Sales schema. 

Columns must only be referenced by using one-part names. 

The view must return the first name and the last name of all customers. 

The view must prevent the underlying structure of the customer table from being 

..... 

changed. 

. The view must be able to resolve all referenced objects, regardless of the user's default schema. 

Which code segment should you use? 

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

Answer: 

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

Answer: C 

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

Q55. You support a database structure shown in the exhibit. (Click the Exhibit button.) 

You need to write a query that displays the following details: 

Total sales made by sales people, year, city, and country 

Sub totals only at the city level and country level 

A grand total of the sales amount 

Which Transact-SQL query should you use? 

A. SELECT SalesPerson.Name, Country, City, 

DatePart(yyyy, SaleDate) AS Year, Sum(Amount) AS Total 

FROM Sale INNER JOIN SalesPerson 

ON Sale.SalesPersonID = SalesPerson.SalesPersonID 

GROUP BY GROUPING SETS((SalesPerson.Name, Country, City, DatePart(yyyy, 

SaleDate)), (Country, City), (Country), ()) 

B. SELECT SalesPerson.Name, Country, City, 

DatePart(yyyy, SaleDate) AS Year, Sum(Amount) AS Total 

FROM Sale INNER JOIN SalesPerson 

ON Sale.SalesPersonID = SalesPerson.SalesPersonID 

GROUP BY CUBE(SalesPerson.Name, Country, City, DatePart(yyyy, SaleDate)) 

C. SELECT SalesPerson.Name, Country, City, 

DatePart(yyyy, SaleDate) AS Year, Sum(Amount) AS Total 

FROM Sale INNER JOIN SalesPerson 

ON Sale.SalesPersonID = SalesPerson.SalesPersonID 

GROUP BY CUBE(SalesPerson.Name, DatePart(yyyy, SaleDate), City, Country) 

D. SELECT SalesPerson.Name, Country, City, DatePart(yyyy, SaleDate) AS Year, Sum(Amount) AS Total FROM Sale INNER JOIN SalesPerson ON Sale.SalesPersonID = SalesPerson.SalesPersonID GROUP BY ROLLUP(SalesPerson.Name, DatePart(yyyy, SaleDate), City, Country) 

Answer: A 

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

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

Q58. You create a view based on the following statement: 

You grant the Select permission to User1. 

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 Transact-SQL statement should you use? 

A. Option A 

B. Option B 

C. Option C 

D. Option D 

Answer: C 

Q59. DRAG DROP 

You administer a Microsoft SQL Server 2012 database. You use an OrderDetail table that has the following definition: 

You need to create a non-clustered index on the SalesOrderID column in the OrderDetail table to include only rows that contain a value in the SpecialOfferID column. Which four Transact-SQL statements should you use? 

(To answer, move the appropriate statements from the list of statements to the answer area and arrange them in the correct order.) 

Answer: 

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