CertBus ensures to provide the most update 70-461 Querying Microsoft SQL Server 2012 exam questions with the most accurate answers. CertBus Microsoft Business Intelligence 70-461 are the most complete and authoritative exam preparation materials with which one can pass the Microsoft Business Intelligence 70-461 exam in an easy way. Preparing for Microsoft Microsoft Business Intelligence 70-461 Querying Microsoft SQL Server 2012 exam is really a tough task to accomplish. But CertBus will simplified the process.
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: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: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:14
You develop a Microsoft SQL Server 2012 database that contains a heap named OrdersHistoncal.
You write the following Transact-SQL query:
INSERT INTO OrdersHistorical
SELECT * FROM CompletedOrders
You need to optimize transaction logging and locking for the statement. Which table hint should you use?
A. HOLDLOCK
B. ROWLOCK
C. XLOCK
D. UPDLOCK
E. TABLOCK
Correct Answer: E
Explanation
Explanation/Reference:
Explanation:
Reference: http://technet.microsoft.com/en-us/library/ms189857.aspx Reference: http://
msdn.microsoft.com/en-us/library/ms187373.aspx
QUESTION NO:16
Your database contains a table named Purchases. The table includes a DATETIME column named
PurchaseTime that stores the date and time each purchase is made. There is a non- clustered index on
the PurchaseTime column.
The business team wants a report that displays the total number of purchases made on the current day.
You need to write a query that will return the correct results in the most efficient manner.
Which Transact-SQL query should you use?
A. SELECT COUNT(*)
FROM Purchases
WHERE PurchaseTime = CONVERT(DATE, GETDATE())
B. SELECT COUNT(*)
FROM Purchases
WHERE PurchaseTime = GETDATE()
C. SELECT COUNT(*)
FROM Purchases
WHERE CONVERT(VARCHAR, PurchaseTime, 112) = CONVERT(VARCHAR, GETDATE(), 112)
D. SELECT COUNT(*)
FROM Purchases
WHERE PurchaseTime >= CONVERT(DATE, GETDATE())
AND PurchaseTime < DATEADD(DAY, 1, CONVERT(DATE, GETDATE()))
Correct Answer: D
Explanation
Explanation/Reference:
Explanation:
Two answers will return the correct results (the “WHERE CONVERT…” and “WHERE …
AND … ” answers).
The correct answer for Microsoft would be the answer that is most “efficient”. Anybody have a clue as to
which is most efficient? In the execution plan, the one that I\’ve selected as the correct answer is the query
with the shortest duration. Also, the query answer with “WHERE CONVERT…” threw warnings in the
execution plan…something about affecting CardinalityEstimate and SeekPlan.
I also found this article, which leads me to believe that I have the correct answer:
http://technet.microsoft.com/en-us/library/ms181034.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: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: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:15
You use a Microsoft SQL Server 2012 database that contains two tables named SalesOrderHeader and
SalesOrderDetail. The indexes on the tables are as shown in the exhibit.
(Click the Exhibit button.)
You write the following Transact-SQL query:
You discover that the performance of the query is slow. Analysis of the query plan shows table scans
where the estimated rows do not match the actual rows for SalesOrderHeader by using an unexpected
index on SalesOrderDetail.
You need to improve the performance of the query.
What should you do?
A. Use a FORCESCAN hint in the query.
B. Add a clustered index on SalesOrderId in SalesOrderHeader.
C. Use a FORCESEEK hint in the query.
D. Update statistics on SalesOrderId on both tables.
Correct Answer: D
Explanation
Explanation/Reference:
Explanation:
References: http://msdn.microsoft.com/en-us/library/ms187348.aspx
QUESTION NO:12
You develop a Microsoft SQL Server 2012 database.
You need to create a batch process that meets the following requirements:
Returns a result set based on supplied parameters.
Enables the returned result set to perform a join with a table.
Which object should you use?
A. Inline user-defined function
B. Stored procedure
C. Table-valued user-defined function
D. Scalar user-defined function
Correct Answer: C
Explanation
Explanation/Reference:
QUESTION NO:18
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.
Correct Answer: C
Explanation
Explanation/Reference:
Explanation:
Reference: http://msdn.microsoft.com/en-us/library/hh213505.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 | | | | | |