This dump is 100% valid to pass Microsoft Microsoft Business Intelligence 70-461 exam. The only tips is please do not just memorize the questions and answers, you need to get through understanding of it because the question changed a little in the real exam. Follow the instructions in the CertBus Microsoft Business Intelligence 70-461 Querying Microsoft SQL Server 2012 PDF and VCEs. All CertBus materials will help you pass your Microsoft Microsoft Business Intelligence exam successfully.
We CertBus has our own expert team. They selected and published the latest 70-461 preparation materials from Microsoft Official Exam-Center: http://www.certgod.com/70-461.html
QUESTION NO:36
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\’)
Correct Answer: A
Explanation
Explanation/Reference:
Explanation:
Reference: http://msdn.microsoft.com/en-us/library/bb510464.aspx
QUESTION NO:39
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)
Correct Answer: C
Explanation
Explanation/Reference:
QUESTION NO:33
You administer a Microsoft SQL Server 2012 database that has multiple tables in the Sales schema. Some
users must be prevented from deleting records in any of the tables in the Sales schema. You need to
manage users who are prevented from deleting records in the Sales schema.
You need to achieve this goal by using the minimum amount of administrative effort. What should you do?
A. Create a custom database role that includes the users. Deny Delete permissions on the Sales schema
for the custom database role.
B. Include the Sales schema as an owned schema for the db_denydatawriter role. Add the users to the
db_denydatawriter role.
C. Deny Delete permissions on each table in the Sales schema for each user.
D. Create a custom database role that includes the users. Deny Delete permissions on each table in the
Sales schema for the custom database role.
Correct Answer: A
Explanation
Explanation/Reference:
QUESTION NO:37
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
Correct Answer: D
Explanation
Explanation/Reference:
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
QUESTION NO:31
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
p.ProductName = cte.ProductName
AND p.CreatedDateTime > cte.CreatedDateTime
B. 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
C. WITH CTEDupRecords
AS
(
SELECT MIN(CreatedDateTime) AS CreatedDateTime, ProductName
FROM Products
GROUP BY ProductName
)
DELETE p
FROM Products p
JOIN CTEDupRecords cte ON
p.ProductName = cte.ProductName
D. 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
p.ProductName = cte.ProductName
Correct Answer: B
Explanation
Explanation/Reference:
QUESTION NO:43
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
Correct Answer: D
Explanation
Explanation/Reference:
Explanation:
Reference: http://msdn.microsoft.com/en-us/library/hh213126.aspx
QUESTION NO:24
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
Correct Answer: C
Explanation
Explanation/Reference:
Explanation:
http://technet.microsoft.com/en-us/library/ms177634.aspx
QUESTION NO:10
You administer a Microsoft SQL Server 2012 database that contains a table named OrderDetail. You
discover that the NCI_OrderDetail_CustomerID non-clustered index is fragmented. You need to reduce
fragmentation.
You need to achieve this goal without taking the index offline. Which Transact-SQL batch should you use?
A. CREATE INDEX NCI_OrderDetail_CustomerID ON OrderDetail.CustomerID WITH DROP EXISTING
B. ALTER INDEX NCI_OrderDetail_CustomerID ON OrderDetail.CustomerID REORGANIZE
C. ALTER INDEX ALL ON OrderDetail REBUILD
D. ALTER INDEX NCI_OrderDetail_CustomerID ON OrderDetail.CustomerID REBUILD
Correct Answer: B
Explanation
Explanation/Reference:
Explanation:
Reference: http://msdn.microsoft.com/en-us/library/ms188388.aspx
QUESTION NO:23
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 previous
year.
Which Transact-SQL query should you use?
A. SELECT Territory, Year, Profit,
LEAD(Profit, 1, 0) OVER (PARTITION BY Territory ORDER BY Year) AS PrevProfit FROM Profits
B. SELECT Territory, Year, Profit,
LAG(Profit, 1, 0) OVER (PARTITION BY Year ORDER BY Territory) AS PrevProfit FROM Profits
C. SELECT Territory, Year, Profit,
LAG(Profit, 1, 0) OVER (PARTITION BY Territory ORDER BY Year) AS PrevProfit FROM Profits
D. SELECT Territory, Year, Profit,
LEAD(Profit, 1, 0) OVER (PARTITION BY Year ORDER BY Territory) AS PrevProfit FROM Profits
Correct Answer: C
Explanation
Explanation/Reference:
Explanation:
Reference: http://msdn.microsoft.com/en-us/library/hh231256.aspx
Reference: http://msdn.microsoft.com/en-us/library/hh213125.aspx
QUESTION NO:44
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
Correct Answer: E
Explanation
Explanation/Reference:
Explanation:
Reference: http://msdn.microsoft.com/en-us/library/ms188792.aspx
CertBus exam braindumps are pass guaranteed. We guarantee your pass for the 70-461 exam successfully with our Microsoft materials. CertBus Querying Microsoft SQL Server 2012 exam PDF and VCE are the latest and most accurate. We have the best Microsoft in our team to make sure CertBus Querying Microsoft SQL Server 2012 exam questions and answers are the most valid. CertBus exam Querying Microsoft SQL Server 2012 exam dumps will help you to be the Microsoft specialist, clear your 70-461 exam and get the final success.
70-461 Latest questions and answers on Google Drive(100% Free Download): https://drive.google.com/file/d/0B_3QX8HGRR1mRWxFa3pFMDR0M2M/view?usp=sharing
70-461 Microsoft exam dumps (100% Pass Guaranteed) from CertBus: http://www.certgod.com/70-461.html [100% Exam Pass Guaranteed]
Why select/choose CertBus?
Millions of interested professionals can touch the destination of success in exams by certgod.com. products which would be available, affordable, updated and of really best quality to overcome the difficulties of any course outlines. Questions and Answers material is updated in highly outclass manner on regular basis and material is released periodically and is available in testing centers with whom we are maintaining our relationship to get latest material.
 |  |  |  |  | |
|---|---|---|---|---|---|
 |  | | |  | |
| Brand | Certbus | Testking | Pass4sure | Actualtests | Others |
| Price | $45.99 | $124.99 | $125.99 | $189 | $69.99-99.99 |
| Up-to-Date Dumps |  |  | | | |
| Free 365 Days Update | | | | | |
| Real Questions | | | | | |
| Printable PDF | | | | | |
| Test Engine | | | | | |
| One Time Purchase | | | | | |
| Instant Download | | | | | |
| Unlimited Install | | | | | |
| 100% Pass Guarantee | | | | | |
| 100% Money Back | | | | | |
| Secure Payment | | | | | |
| Privacy Protection | | | | | |