Exam Code: 70-461 (Practice Exam Latest Test Questions VCE PDF)
Exam Name: Querying Microsoft SQL Server 2012
Certification Provider: Microsoft
Free Today! Guaranteed Training- Pass 70-461 Exam.


♥♥ 2021 NEW RECOMMEND ♥♥

Free VCE & PDF File for Microsoft 70-461 Real Exam (Full Version!)

★ Pass on Your First TRY ★ 100% Money Back Guarantee ★ Realistic Practice Exam Questions

Free Instant Download NEW 70-461 Exam Dumps (PDF & VCE):
Available on: http://www.surepassexam.com/70-461-exam-dumps.html

2021 Apr 70-461 vce

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


Q52. You administer a Microsoft SQL Server database named Sales. The database is 3 terabytes in size. The Sales database is configured as shown in the following table. 

You discover that Sales_2.ndf is corrupt. You need to recover the corrupted data in the minimum amount of time. What should you do? 

A. Perform a file restore. 

B. Perform a transaction log restore. 

C. Perform a restore from a full backup. 

D. Perform a filegroup restore. 

Answer: A 


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


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


Q55. You develop a Microsoft SQL Server 2012 server database that supports an application. The application contains a table that has the following definition: 

CREATE TABLE Inventory 

(ItemID int NOT NULL PRIMARY KEY, 

ItemsInStore int NOT NULL, 

ItemsInWarehouse int NOT NULL) 

You need to create a computed column that returns the sum total of the ItemsInStore and ItemsInWarehouse values for each row. 

Which Transact-SQL statement should you use? 

A. ALTER TABLE Inventory 

ADD TotalItems AS ItemsInStore + ItemsInWarehouse 

B. ALTER TABLE Inventory 

ADD ItemsInStore - ItemsInWarehouse = TotalItemss 

C. ALTER TABLE Inventory 

ADD TotalItems = ItemsInStore + ItemsInWarehouse 

D. ALTER TABLE Inventory 

ADD TotalItems AS SUM(ItemsInStore, ItemslnWarehouse); 

Answer: A 


Renew 70-461 sample question:

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

Answer: C 

Explanation: 

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


Q57. You are developing a database that will contain price information. 

You need to store the prices that include a fixed precision and a scale of six digits. 

Which data type should you use? 

A. Real 

B. Small money 

C. Money 

D. Decimal 

Answer: D 


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

Answer: 


Q59. You develop a database for a travel application. You need to design tables and other database objects. 

You need to store media files in several tables. 

Each media file is less than 1 MB in size. The media files will require fast access and will be retrieved frequently. 

What should you do? 

A. Use the CAST function. 

B. Use the DATE data type. 

C. Use the FORMAT function. 

D. Use an appropriate collation. 

E. Use a user-defined table type. 

F. Use the VARBINARY data type. 

G. Use the DATETIME data type. 

H. Use the DATETIME2 data type. 

I. Use the DATETIMEOFFSET data type. 

J. Use the TODATETIMEOFFSET function. 

Answer: F 


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