100% Correct of 70 461 exam brain dumps materials and braindump for Microsoft certification for IT engineers, Real Success Guaranteed with Updated 70 461 book pdf dumps vce Materials. 100% PASS Querying Microsoft SQL Server 2012 exam Today!


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

Q1. A table named Profits stores the total profit made each year within a territory. The Profits table has columns named Territory, Year, and Profit. You need to create a report that displays the profits made by each territory for each year and its preceding year. Which Transact-SQL query should you use? 

A. SELECT Territory, Year, Profit, LAG(Profit, 1, 0) OVER(PARTITION BY Year ORDER BY Territory) AS NextProfit FROM Profits 

B. SELECT Territory, Year, Profit, LAG(Profit, 1, 0) OVER(PARTITION BY Territory ORDER BY Year) AS NextProfit FROM Profits 

C. SELECT Territory, Year, Profit, LEAD(Profit, 1, 0) OVER(PARTITION BY Territory ORDER BY Year) AS NextProfit FROM Profits 

D. SELECT Territory, Year, Profit, LEAD(Profit, 1, 0) OVER(PARTITION BY Year ORDER BY Territory) AS NextProfit FROM Profits 

Answer:


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


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


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


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

You need to implement a computed column that references a lookup table by using an INNER JOIN against another table. 

What should you do? 

A. Reference a user-defined function within the computed column. 

B. Create a BEFORE trigger that maintains the state of the computed column. 

C. Add a default constraint to the computed column that implements hard-coded values. 

D. Add a default constraint to the computed column that implements hard-coded CASE statements. 

Answer:


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

You create a view that displays the dates and times of the airline schedules on a report. 

You need to display dates and times in several international formats. 

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:


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


Q8. CORRECT TEXT 

You need to create a query that calculates the total sales of each OrderlD from a table named Sales.Details. The table contains two columns named OrderlD and ExtendedAmount. 

The solution must meet the following requirements: 

Use one-part names to reference columns. 

Start the order of the results from OrderlD. 

NOT depend on the default schema of a user. 

Use an alias of TotalSales for the calculated ExtendedAmount. 

Display only the OrderlD column and the calculated TotalSales column. 

Provide the correct code in the answer area. 

Answer: 


Q9. DRAG DROP 

You create a view based on the following statement: 

You grant the Select permission to User1 for this view. 

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


Q10. DRAG DROP 

You develop a database application for a university. You need to create a view that will be indexed that meets the following requirements: 

. Displays the details of only students from Canada. 

. Allows insertion of details of only students from Canada. 

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: