Top 15 Q&A 70-461 for IT specialist (31 to 45)

★ 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


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.

2016 Mar 70-461 Study Guide Questions:

Q31. CORRECT TEXT 

You need to create a table named OrderDetails on a new server. OrderDetails must meet the following requirements: 

. Contain a new column named LineltemTotal that stores the product of ListPrice 

and Quantity for each row. 

. The calculation for a line item total must not be run every time the table is queried. 

. The code must NOT use any object delimiters. 

The solution must ensure that LineItemTotal is stored as the last column in the table. 

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


Answer: 


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


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

B. Money 

C. Smallmoney 

D. Decimal 

Answer: D 

Explanation: 

Decimal is the only one in the list that can give a fixed precision and scale. Reference: http://msdn.microsoft.com/en-us/library/ms187746.aspx 


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


70-461 braindumps

Up to date 70-461 download:

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


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


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


Q39. You administer a Microsoft SQL Server database that supports a shopping application. 

You need to retrieve a list of customers who live in territories that do not have a sales person. 

Which Transact- SQL query or queries should you use? (Each correct answer presents a complete solution. Choose all that apply.) 

A. SELECT CustomerID FROM Customer 

WHERE TerritoryID <> SOME(SELECT TerritoryID FROM Salesperson) 

B. SELECT CustomerID FROM Customer 

WHERE TerritoryID <> ALL(SELECT TerritoryID FROM Salesperson) 

C. SELECT CustomerID FROM Customer 

WHERE TerritoryID <> ANY(SELECT TerritoryID FROM Salesperson) 

D. SELECT CustomerID FROM Customer 

WHERE TerritoryID NOT IN(SELECT TerritoryID FROM Salesperson) 

Answer: B,D 


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


70-461 test question

Highest Quality 70-461 practice question:

Q41. DRAG DROP 

You use a Microsoft SQL Server 2012 database. 

You need to create an indexed view within the database for a report that displays Customer Name and the total revenue for that customer. 

Which four T-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: 



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


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



Q44. You use Microsoft SQL Server 2012 to develop a database application. 

Your application sends data to an NVARCHAR(MAX) variable named @var. 

You need to write a Transact-SQL statement that will find out the success of a cast to a 

decimal (36,9). 

Which code segment should you use? 

A. BEGIN TRY SELECT convert(decimal(36,9), @var) AS Value, 'True' AS BadCast END TRY BEGIN CATCH SELECT convert(decimal(36,9), @var) AS Value, 'False' AS BadCast END CATCH 

B. TRY( SELECT convert(decimal(36,9), @var) SELECT 'True' AS BadCast ) CATCH( SELECT 'False' AS BadCast ) 

C. SELECT CASE WHEN convert(decimal(36,9), @var) IS NULL THEN 'True' ELSE 'False' END AS BadCast 

D. SELECT IIF(TRY_PARSE(@var AS decimal(36,9)) IS NULL, 'True', 'False') AS BadCast 

Answer: D 


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