Examcollection offers free demo for microsoft exam 70 461 exam. "Querying Microsoft SQL Server 2012", also known as sql 70 461 exam, is a Microsoft Certification. This set of posts, Passing the Microsoft 70 461 training kit pdf exam, will help you answer those questions. The 70 461 vce Questions & Answers covers all the knowledge points of the real exam. 100% real Microsoft exam 70 461 dumps free download pdf exams and revised by experts!
♥♥ 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
Q41. DRAG DROP
You use Microsoft SQL Server 2012 to develop a database application.
You create a table by using the following definition:
CREATE TABLE Prices (
PriceId int IDENTITY(1,1) PRIMARY KEY,
ActualPrice NUMERIC(16,9),
PredictedPrice NUMERIC(16,9)
)
You need to create a computed column based on a user-defined function named udf_price_index. You also need to ensure that the column supports an index.
Which three 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:
Q42. CORRECT TEXT
You have a database named Sales that contains the tables as shown in the exhibit. (Click
the Exhibit button.)
You need to create a query that meets the following requirements:
References columns by using one-part names only.
Groups aggregates by SalesTerritorylD, and then by ProductlD.
Orders the results in descending order by SalesTerritorylD and then by ProductlD.
Part of the correct T-SQL statement has been provided in the answer area. Provide the complete code.
Answer:
Q43. 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:
Q44. You use Microsoft SQL Server 2012 to create a stored procedure as shown in the following code segment. (Line numbers are included for reference only.)
The procedure can be called within other transactions.
You need to ensure that when the DELETE statement from the HumanResourcesJobCandidate table succeeds, the modification is retained even if the insert into the Audit.Log table fails.
Which code segment should you add to line 14?
A. IF @@TRANCOUNT = 0
B. IF (XACT_STATE ( ) ) = 0
C. IF (XACT_STATE ( ) ) = 1
D. IF @@TRANCOUNT = l
Answer: C
Q45. CORRECT TEXT
You have a database that contains the tables as shown in the exhibit. (Click the Exhibit button.)
You need to create a query that returns a list of products from Sales.ProductCatalog. The solution must meet the following requirements:
UnitPrice must be returned in descending order.
The query must use two-part names to reference the table.
The query must use the RANK function to calculate the results.
The query must return the ranking of rows in a column named PriceRank.
The list must display the columns in the order that they are defined in the table.
PriceRank must appear last.
Which code segment should you use?
To answer, type the correct code in the answer area.
Answer:
Q46. 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: F
Q47. 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
Q48. 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:
Q49. You support a database structure shown in the exhibit. (Click the Exhibit button.)
You need to write a query that displays the following details:
Total sales made by sales people, year, city, and country
Sub totals only at the city level and country level
A grand total of the sales amount
Which Transact-SQL query should you use?
A. SELECT SalesPerson.Name, Country, City,
DatePart(yyyy, SaleDate) AS Year, Sum(Amount) AS Total
FROM Sale INNER JOIN SalesPerson
ON Sale.SalesPersonID = SalesPerson.SalesPersonID
GROUP BY GROUPING SETS((SalesPerson.Name, Country, City, DatePart(yyyy,
SaleDate)), (Country, City), (Country), ())
B. SELECT SalesPerson.Name, Country, City,
DatePart(yyyy, SaleDate) AS Year, Sum(Amount) AS Total
FROM Sale INNER JOIN SalesPerson
ON Sale.SalesPersonID = SalesPerson.SalesPersonID
GROUP BY CUBE(SalesPerson.Name, Country, City, DatePart(yyyy, SaleDate))
C. SELECT SalesPerson.Name, Country, City,
DatePart(yyyy, SaleDate) AS Year, Sum(Amount) AS Total
FROM Sale INNER JOIN SalesPerson
ON Sale.SalesPersonID = SalesPerson.SalesPersonID
GROUP BY CUBE(SalesPerson.Name, DatePart(yyyy, SaleDate), City, Country)
D. SELECT SalesPerson.Name, Country, City, DatePart(yyyy, SaleDate) AS Year, Sum(Amount) AS Total FROM Sale INNER JOIN SalesPerson ON Sale.SalesPersonID = SalesPerson.SalesPersonID GROUP BY ROLLUP(SalesPerson.Name, DatePart(yyyy, SaleDate), City, Country)
Answer: A
Q50. 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:
