Do not worry about your Microsoft Business Intelligence 70-461 exam preparation? Hand over your problems to CertBus in change of the Microsoft Business Intelligence 70-461 Querying Microsoft SQL Server 2012 certifications! CertBus provides the latest Microsoft Microsoft Business Intelligence 70-461 exam preparation materials with PDF and VCEs. We CertBus guarantees you passing Microsoft Business Intelligence 70-461 exam for sure.
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:22
You develop a Microsoft SQL Server 2012 database that contains a table named Products. The Products
table has the following definition:
You need to create an audit record only when either the RetailPrice or WholeSalePrice column is updated.
Which Transact-SQL query should you use?
A. CREATE TRIGGER TrgPriceChange ON Products FOR UPDATE AS IF CCLUMNS_CHANGED
(RetailPrice, WholesalePrice)
– – Create Audit Records
B. CREATE TRIGGER TrgPriceChange ON Products FOR UPDATE AS IF EXISTS(SELECT RetailPrice
from inserted) OR
EXISTS (SELECT WholeSalePnce FROM inserted)
– – Create Audit Records
C. CREATE TRIGGER TrgPriceChange ON Products FOR UPDATE AS IF COLUMNS_UPDATED
(RetailPrice, WholesalePrice)
– – Create Audit Records
D. CREATE TRIGGER TrgPriceChange ON Products FOR UPDATE AS IF UPDATE(RetailPrice) OR
UPDATE(WholeSalePrice)
– – Create Audit Records
Correct Answer: D
Explanation
Explanation/Reference:
Explanation:
Reference: http://msdn.microsoft.com/en-us/library/bb510663.aspx Reference: http://msdn.microsoft.com/
en-us/library/ms186329.aspx
QUESTION NO:2
You develop a Microsoft SQL Server 2012 database. You create a view from the Orders and OrderDetails
tables by using the following definition.
You need to improve the performance of the view by persisting data to disk. What should you do?
A. Create an INSTEAD OF trigger on the view.
B. Create an AFTER trigger on the view.
C. Modify the view to use the WITH VIEW_METADATA clause.
D. Create a clustered index on the view.
Correct Answer: D
Explanation
Explanation/Reference:
Explanation:
Reference: http://msdn.microsoft.com/en-us/library/ms188783.aspx
QUESTION NO:7
Your database contains two tables named DomesticSalesOrders and InternationalSalesOrders. Both
tables contain more than 100 million rows. Each table has a Primary Key column named SalesOrderId.
The data in the two tables is distinct from one another.
Business users want a report that includes aggregate information about the total number of global sales
and total sales amounts.
You need to ensure that your query executes in the minimum possible time.
Which query should you use?
A. SELECT COUNT(*) AS NumberOfSales, SUM(SalesAmount) AS TotalSalesAmount FROM (
SELECT SalesOrderId, SalesAmount
FROM DomesticSalesOrders
UNION ALL
SELECT SalesOrderId, SalesAmount
FROM InternationalSalesOrders
) AS p
B. SELECT COUNT(*) AS NumberOfSales, SUM(SalesAmount) AS TotalSalesAmount FROM (
SELECT SalesOrderId, SalesAmount
FROM DomesticSalesOrders
UNION
SELECT SalesOrderId, SalesAmount
FROM InternationalSalesOrders
) AS p
C. SELECT COUNT(*) AS NumberOfSales, SUM(SalesAmount) AS TotalSalesAmount FROM
DomesticSalesOrders
UNION
SELECT COUNT(*) AS NumberOfSales, SUM(SalesAmount) AS TotalSalesAmount FROM
InternationalSalesOrders
D. SELECT COUNT(*) AS NumberOfSales, SUM(SalesAmount) AS TotalSalesAmount FROM
DomesticSalesOrders
UNION ALL
SELECT COUNT(*) AS NumberOfSales, SUM(SalesAmount) AS TotalSalesAmount FROM
InternationalSalesOrders
Correct Answer: A
Explanation
Explanation/Reference:
Explanation:
Reference: http://msdn.microsoft.com/en-us/library/ms180026.aspx Reference: http://
blog.sqlauthority.com/2009/03/11/sql-server-difference-between-union-vs- union-all-optimalperformancecomparison/
QUESTION NO:25
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)
Correct Answer: BD
Explanation
Explanation/Reference:
QUESTION NO:30
You administer a Microsoft SQL Server 2012 database. The database contains a table named Employee.
Part of the Employee table is shown in the exhibit. (Click the Exhibit button.)
Unless stated above, no columns in the Employee table reference other tables.
Confidential information about the employees is stored in a separate table named EmployeeData. One
record exists within EmployeeData for each record in the Employee table. You need to assign the
appropriate constraints and table properties to ensure data integrity and visibility.
On which column in the Employee table should you use an identity specification to include a seed of 1,000
and an increment of 1?
A. DateHired
B. DepartmentID
C. EmployeeID
D. EmployeeNum
E. FirstName
F. JobTitle
G. LastName
H. MiddleName
I. ReportsToID
Correct Answer: C
Explanation
Explanation/Reference:
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:28
You administer a Microsoft SQL Server database that supports a banking transaction management
application.
You need to retrieve a list of account holders who live in cities that do not have a branch location.
Which Transact-SQL query or queries should you use? (Each correct answer presents a complete
solution. Choose all that apply.)
A. SELECT AccountHolderID
FROM AccountHolder
WHERE CityID NOT IN (SELECT CityID FROM BranchMaster)
B. SELECT AccountHolderID
FROM AccountHolder
WHERE CityID ALL (SELECT CityID FROM BranchMaster)
C. SELECT AccountHolderlD
FROM AccountHolder
WHERE CityID SOME (SELECT CityID FROM BranchMaster)
D. SELECT AccountHolderID
FROM AccountHolder
WHERE CityID ANY (SELECT CityID FROM BranchMaster)
Correct Answer: AB
Explanation
Explanation/Reference:
Explanation:
Verified the answers as correct.
Reference: http://msdn.microsoft.com/en-us/library/ms188047.aspx
Reference: http://msdn.microsoft.com/en-us/library/ms177682.aspx
Reference: http://msdn.microsoft.com/en-us/library/ms173545.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: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:9
You use a Microsoft SQL Server 2012 database.
You want to create a table to store Microsoft Word documents.
You need to ensure that the documents must only be accessible via Transact-SQL queries.
Which Transact-SQL statement should you use?
A. CREATE TABLE DocumentStore
(
[Id] INT NOT NULL PRIMARY KEY,
[Document] VARBINARY(MAX) NULL
)
GO
B. CREATE TABLE DocumentStore
(
[Id] hierarchyid,
[Document] NVARCHAR NOT NULL
)
GO
C. CREATE TABLE DocumentStore AS FileTable
D. CREATE TABLE DocumentStore
(
[Id] [uniqueidentifier] ROWGUIDCOL NOT NULL UNIQUE, [Document] VARBINARY(MAX)
FILESTREAM NULL
)
GO
Correct Answer: A
Explanation
Explanation/Reference:
Explanation:
Reference: http://msdn.microsoft.com/en-us/library/gg471497.aspx Reference: http://msdn.microsoft.com/
en-us/library/ff929144.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 | | | | | |