How to pass Microsoft 70-461 Real Exam in 24 Hours [free exam 16-30]

★ 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: https://www.certleader.com/70-461-dumps.html


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

Instant Access to Free VCE Files: Microsoft 70-461 Querying Microsoft SQL Server 2012

70-461 examcollection

Refined of 70-461 training materials and secret for Microsoft certification for examinee, Real Success Guaranteed with Updated 70-461 pdf dumps vce Materials. 100% PASS Today!

2016 Mar 70-461 Study Guide Questions:

Q16. You administer a database that includes a table named Customers that contains more than 750 rows. You create a new column named PartitionNumber of the int type in the table. 

You need to assign a PartitionNumber for each record in the Customers table.You also need to ensure that the PartitionNumber satisfies the following conditions: 

. Always starts with 1. 

. Starts again from 1 after it reaches 100. 

Which Transact-SQL statement should you use? 

A. CREATE SEQUENCE CustomerSequence AS int START WITH 0 INCREMENT BY 1 MINVALUE 1 MAXVALUE 100 UPDATE Customers SET PartitionNumber = NEXT VALUE FOR CustomerSequence DROP SEQUENCE CustomerSequence 

B. CREATE SEQUENCE CustomerSequence AS int START WITH 1 INCREMENT BY 1 MINVALUE 1 MAXVALUE 100 CYCLE UPDATE Customers SET PartitionNumber = NEXT VALUE FOR CustomerSequence DROP SEQUENCE CustomerSequence 

C. CREATE SEQUENCE CustomerSequence AS int START WITH 1 INCREMENT BY 1 MINVALUE 1 MAXVALUE 100 UPDATE Customers SET PartitionNumber = NEXT VALUE FOR CustomerSequence + 1 DROP SEQUENCE CustomerSequence 

D. CREATE SEQUENCE CustomerSequence AS int START WITH 1 INCREMENT BY 1 MINVALUE 0 MAXVALUE 100 CYCLE UPTATE Customers SET PartitionNumber = NEXT VALUE FOR CustomerSequence DROP SEQUENCE CustomerSequence 

Answer: B 


Q17. You are writing a set of queries against a FILESTREAM-enabled database. 

You create a stored procedure that will update multiple tables within a transaction. 

You need to ensure that if the stored procedure raises a runtime error, the entire 

transaction is terminated and rolled back. 

Which Transact-SQL statement should you include at the beginning of the stored 

procedure? 

A. SET TRANSACTION ISOLATION LEVEL SERIALIZABLE 

B. SET XACT_ABORT OFF 

C. SET TRANSACTION ISOLATION LEVEL SNAPSHOT 

D. SET IMPLICIT_TRANSACTIONS ON 

E. SET XACT_ABORT ON 

F. SET IMPLICIT TRANSACTIONS OFF 

Answer: E 


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


Q19. CORRECT TEXT 

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


You need to create a query for a report. The query must meet the following requirements: 

NOT use object delimiters. 

Use the first initial of the table as an alias. 

Return the most recent order date for each customer. 

Retrieve the last name of the person who placed the order. 

The solution must support the ANSI SQL-99 standard. 

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

Answer: 

78. You are developing a database application by using Microsoft SQL Server 2012. 

An application that uses a database begins to run slowly. 

You discover that the root cause is a query against a frequently updated table that has a clustered index. The query returns four columns: three columns in its WHERE clause contained in a non-clustered index and one additional column. 

You need to optimize the statement. 

What should you do? 

A. Add a HASH hint lo 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 FORCESCAN hint to the Attach query. 

G. Add a columnstore index to cover the query. 

H. Enable the optimize for ad hoc workloads option. 

I. Cover the unique clustered index with a columnstore index. 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. 

Answer: C 


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


70-461 test questions

Up to date 70-461 test questions:

Q21. You administer a Microsoft SQL Server 2012 database. The database contains a table named Employee. 

Part of the Employee table is shown in the exhibit. (Click the Exhibit button.) 



Unless stated above, no columns in the Employee table reference other tables. 

Confidential information about the employees is stored in a separate table named EmployeeData. One record exists within EmployeeData for each record in the Employee table. 

You need to assign the appropriate constraints and table properties to ensure data integrity and visibility. 

On which column in the Employee table should you a create a self-reference foreign key constraint? 

A. DateHired 

B. DepartmentID 

C. EmployeelD 

D. EmployeeNum 

E. FirstName 

F. JobTitle 

G. LastName 

H. MiddleName 

I. ReportsToID 

Answer: I 


Q22. You use a Microsoft SQL Server 2012 database that contains a table named BlogEntry that has the following columns: 


Id is the Primary Key. 

You need to append the "This is in a draft stage" string to the Summary column of the recent 10 entries based on the values in EntryDateTime. 

Which Transact-SQL statement should you use? 

A. UPDATE TOP(10) BlogEntry 

SET Summary.WRITE(N' This is in a draft stage', NULL, 0) 

B. UPDATE BlogEntry 

SET Summary = CAST(N' This is in a draft stage' as nvarchar(max)) 

WHERE Id IN(SELECT TOP(10) Id FROM BlogEntry ORDER BY EntryDateTime DESC) 

C. UPDATE BlogEntry 

SET Summary.WRITE(N' This is in a draft stage', NULL, 0) FROM ( 

SELECT TOP(10) Id FROM BlogEntry ORDER BY EntryDateTime DESC) AS s WHERE BlogEntry.Id = s.ID 

D. UPDATE BlogEntry 

SET Summary.WRITE(N' This is in a draft stage', 0, 0) 

WHERE Id IN(SELECT TOP(10) Id FROM BlogEntry ORDER BY EntryDateTime DESC) 

Answer: C 


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


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


Q25. 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 ensure that the top half of the students arranged by their average marks must be given a rank of 1 and the remaining students must be given a rank of 2. 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: D 


70-461 exam cram

Realistic 70-461 dumps:

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


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

<row OrderId="1" OrderDate="2000-01-01T00:00:00" Amount="3400.00" Name="Customer 

A" Country="Australia" /> 

<row OrderId="2" OrderDate="2001-01-01T00:00:00" Amount="4300.00" Name="Customer 

A" Country="Australia" /> 

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


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


Q29. Your database contains two tables named DomesticSalesOrders and InternationalSalesOrders. Both tables contain more than 100 million rows. Each table has a Primary Key column named SalesOrderId. The data in the two tables is distinct from one another. 

Business users want a report that includes aggregate information about the total number of global sales and total sales amounts. 

You need to ensure that your query executes in the minimum possible time. 

Which query should you use? 

A. SELECT COUNT(*) AS NumberOfSales, SUM(SalesAmount) AS TotalSalesAmount FROM ( SELECT SalesOrderId, SalesAmount FROM DomesticSalesOrders UNION ALL SELECT SalesOrderId, SalesAmount FROM InternationalSalesOrders ) AS p 

B. SELECT COUNT(*) AS NumberOfSales, SUM(SalesAmount) AS TotalSalesAmount FROM ( SELECT SalesOrderId, SalesAmount FROM DomesticSalesOrders UNION SELECT SalesOrderId, SalesAmount FROM InternationalSalesOrders ) AS p 

C. SELECT COUNT(*) AS NumberOfSales, SUM(SalesAmount) AS TotalSalesAmount FROM DomesticSalesOrders UNION SELECT COUNT(*) AS NumberOfSales, SUM(SalesAmount) AS TotalSalesAmount FROM InternationalSalesOrders 

D. SELECT COUNT(*) AS NumberOfSales, SUM(SalesAmount) AS TotalSalesAmount FROM DomesticSalesOrders UNION ALL SELECT COUNT(*) AS NumberOfSales, SUM(SalesAmount) AS TotalSalesAmount FROM InternationalSalesOrders 

Answer: A 


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