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

Validated of 70-461 free practice questions materials and testing engine for Microsoft certification for IT examinee, Real Success Guaranteed with Updated 70-461 pdf dumps vce Materials. 100% PASS Querying Microsoft SQL Server 2012 exam Today!

2016 Mar 70-461 Study Guide Questions:

Q16. You are a database developer of a Microsoft SQL Server 2012 database. 

You are designing a table that will store Customer data from different sources. The table will include a column that contains the CustomerID from the source system and a column that contains the SourceID. 

A sample of this data is as shown in the following table. 


You need to ensure that the table has no duplicate CustomerID within a SourceID. You also need to ensure that the data in the table is in the order of SourceID and then CustomerID. 

Which Transact- SQL statement should you use? 

A. CREATE TABLE Customer 

(SourceID int NOT NULL IDENTITY, 

CustomerID int NOT NULL IDENTITY, 

CustomerName varchar(255) NOT NULL); 

B. CREATE TABLE Customer 

(SourceID int NOT NULL, 

CustomerID int NOT NULL PRIMARY KEY CLUSTERED, 

CustomerName varchar(255) NOT NULL); 

C. CREATE TABLE Customer 

(SourceID int NOT NULL PRIMARY KEY CLUSTERED, 

CustomerID int NOT NULL UNIQUE, 

CustomerName varchar(255) NOT NULL); 

D. CREATE TABLE Customer 

(SourceID int NOT NULL, 

CustomerID int NOT NULL, 

CustomerName varchar(255) NOT NULL, 

CONSTRAINT PK_Customer PRIMARY KEY CLUSTERED 

(SourceID, CustomerID)); 

Answer: D 


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

You need to ensure that if the stored procedure raises a run-time 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 XACT_ABORT ON 

B. SET ARITHABORT ON 

C. TRY 

D. BEGIN 

E. SET ARITHABORT OFF 

F. SET XACT_ABORT OFF 

Answer: A 


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


Q19. CORRECT TEXT 

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


You need to create a query that calculates the total sales of each OrderId from the Sales.Details table. The solution must meet the following requirements: 

Use one-part names to reference columns. 

Sort the order of the results from OrderId. 

NOT depend on the default schema of a user. 

Use an alias of TotalSales for the calculated ExtendedAmount. 

Display only the OrderId column and the calculated TotalSales column. 

..... 

Which code segment should you use? 

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

Answer: 


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

Most recent 70-461 exam guide:

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


Q22. 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 applications. 

What should you create for each application? 

A. Synonyms 

B. Common table expressions 

C. Views 

D. Temporary tables 

Answer: C 


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



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


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

An application that uses a database begins to run slowly. 

Your investigation shows the root cause is a query against a read-only table that has a 

clustered index. 

The query returns the following six columns: 

... 

One column in its WHERE clause contained in a non-clustered index · Four additional columns One COUNT (*) column based on a grouping of the four additional columns 

You need to optimize the statement. 

What should you do? 

A. Add a HASH hint to 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 columnstore index to cover the query. 

G. Enable the optimize for ad hoc workloads option. 

H. Cover the unique clustered index with a columnstore index. 

I. 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: F 


70-461 sample question

Validated 70-461 brain dumps:

Q26. You have a Microsoft SQL Server 2012 database that contains tables named Customers and Orders. 

The tables are related by a column named CustomerID. 

You need to create a query that meets the following requirements: 

. Returns the CustomerName for all customers and the OrderDate for any orders that they have placed. . Results must include customers who have not placed any orders. 

Which Transact-SQL query should you use? 

A. SELECT CustomerName, OrderDate FROM Customers RIGHT OUTER JOIN Orders ON Customers.CustomerID = Orders.CustomerID 

B. SELECT CustomerName, CrderDate FROM Customers JOIN Orders ON Customers.CustomerID = Orders.CustomerID 

C. SELECT CustomerName, OrderDate FROM Customers CROSS JOIN Orders ON Customers.CustomerID = Orders.CustomerID 

D. SELECT CustomerName, OrderDate FROM Customers LEFT OUTER JOIN Orders ON Customers.CustomerID = Orders.CustomerID 

Answer: D 


Q27. Your database contains tables named Products and ProductsPriceLog. The Products table contains columns named ProductCode and Price. The ProductsPriceLog table contains columns named ProductCode, OldPrice, and NewPrice. 

The ProductsPriceLog table stores the previous price in the OldPrice column and the new price in the NewPrice column. 

You need to increase the values in the Price column of all products in the Products table by 5 percent. You also need to log the changes to the ProductsPriceLog table. 

Which Transact-SQL query should you use? 

A. UPDATE Products SET Price = Price * 1.05 

OUTPUT inserted.ProductCode, deleted.Price, inserted.Price 

INTO ProductsPriceLog(ProductCode, OldPrice, NewPrice) 

B. UPDATE Products SET Price = Price * 1.05 

OUTPUT inserted.ProductCode, inserted.Price, deleted.Price 

INTO ProductsPriceLog(ProductCode, OldPrice, NewPrice) 

C. UPDATE Products SET Price = Price * 1.05 

OUTPUT inserted.ProductCode, deleted.Price, inserted.Price * 

INTO ProductsPriceLog(ProductCode, OldPrice, NewPrice) 

D. UPDATE Products SET Price = Price * 1.05 

INSERT INTO ProductsPriceLog (ProductCode, CldPnce, NewPrice; 

SELECT ProductCode, Price, Price * 1.05 FROM Products 

Answer: A 


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


Q29. You administer a Microsoft SQL Server 2012 database. The database contains a Product table created by using the following definition: 


You need to ensure that the minimum amount of disk space is used to store the data in the Product table. 

What should you do? 

A. Convert all indexes to Column Store indexes. 

B. Implement Unicode Compression. 

C. Implement row-level compression. 

D. Implement page-level compression. 

Answer: D 


Q30. DRAG DROP 

You create the following stored procedure. (Line numbers are included for reference only.) 

You need to ensure that the stored procedure performs the following tasks: 

. If a record exists, update the record. 

. If no record exists, insert a new record. 

Which four Transact-SQL statements should you insert at line 07? (To answer, move the appropriate statements from the list of statements to the answer area and arrange them in the correct order.) 


Answer: 




see more 70-461 - Querying Microsoft SQL Server 2012

Microsoft 70-461 Certification Sample Questions and Answers: http://www.braindumpsall.net/70-461-dumps/

P.S. New 70-461 dumps PDF: http://www.4easydumps.com/70-461-dumps-download.html