Share real and effective Microsoft Business Intelligence 70-461 exam dumps for free. 13 Online 70-461 Exam Practice test questions and answers,online 70-461 pdf download, easy to learn! Get the full 70-461 Dumps: https://www.leads4pass.com/70-461.html (Total Questions: 323 Q&A) to make it easy to pass the exam!
[PDF] Free Microsoft Business Intelligence 70-461 pdf dumps download from Google Drive: https://drive.google.com/open?id=1s9_DWVmtzecejmoM6gF9OBqCFDTC3iM2
[PDF] Free Full Microsoft pdf dumps download from Google Drive: https://drive.google.com/open?id=1AwBFPqkvdpJBfxdZ3nGjtkHQZYdBsRVz
Exam 70-461: Querying Microsoft SQL Server 2012/2014: https://www.microsoft.com/en-us/learning/exam-70-461.aspx
Skills measured
- Create database objects (20–25%)
- Work with data (25–30%)
- Modify data (20–25%)
- Troubleshoot and optimize (25–30%)
Latest effective Microsoft Business Intelligence 70-461 Exam Practice Tests
QUESTION 1
What is the difference between the simple CASE expression and the searched CASE expression?
A. The simple CASE expression is used when the database recovery model is simple, and the searched CASE
expression is used when it\\’s full or bulk logged.
B. The simple CASE expression compares an input expression to multiple possible expressions in the WHEN clauses,
and the searched CASE expression uses independent predicates in the WHEN clauses.
C. The simple CASE expression can be used anywhere in a query, and the searched CASE expression can be used
only in the WHERE clause.
D. The simple CASE expression can be used anywhere in a query, and the searched CASE expression can be used
only in query filters (ON, WHERE, HAVING).
Correct Answer: B
QUESTION 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
http://msdn.microsoft.com/en-us/library/ms188783.aspx
QUESTION 3
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, OrderId, 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: C
Reference: http://msdn.microsoft.com/en-us/library/ms188273.aspx
QUESTION 4
You have a Microsoft SQL Server database that includes two tables named EmployeeBonus and BonusParameters.
The tables are defined by using the following Transact-SQL statements:
The tables are used to compute a bonus for each employee. The EmployeeBonus table has a non-null value in either
the Quarterly, HalfYearly or Yearly column. This value indicates which type of bonus an employee receives. The
BonusParameters table contains one row for each calendar year that stores the amount of bonus money available and a
company performance indicator for that year.
You need to calculate a bonus for each employee at the end of a calendar year.
Which Transact-SQL statement should you use?
A. SELECT CAST(CHOOSE((Quarterly * AvailableBonus * CompanyPerformance)/40, (HalfYearly * AvailableBonus *
CompanyPerformance)/20, (Yearly * AvailableBonus * CompanyPerformance)/10) AS money) AS `Bonus\\’ FROM
EmployeeBonus, BonusParameters
B. SELECT “Bonus” = CASE EmployeeBonus WHEN Quarterly=1 THEN (Quarterly * AvailableBonus *
CompanyPerformance)/40 WHEN HalfYearly=1 THEN (HalfYearly * AvailableBonus * CompanyPerformance)/20
WHEN Yearly=1 THEN (Yearly * AvailableBonus * CompanyPerformance)/10 END FROM
EmployeeBonus,BonusParameters
C. SELECT CAST(COALESCE((Quarterly * AvailableBonus * CompanyPerformance)/40, (HalfYearly * AvailableBonus
* CompanyPerformance)/20, (Yearly * AvailableBonus * CompanyPerformance)/10) AS money) AS `Bonus\\’ FROM
EmployeeBonus, BonusParameters
D. SELECT NULLIF(NULLIF((Quarterly * AvailableBonus * CompanyPerformance)/40,(HalfYearly * AvailableBonus
* CompanyPerformance)/20),
(Yearly * AvailableBonus * CompanyPerformance)/10) AS `Bonus\\’ FROM EmployeeBonus, BonusParameters
Correct Answer: B
QUESTION 5
You work as a SQL Server 2012 database developer at ABC.com.
ABC.com has a database SalesDB with a large Orders table. You create a heap named OldData that will store historical
data from the Orders table.
You need to write a Transact-SQL query that will insert rows of data from the Orders table that are marked as closed
and are more than six months old.
Which of the following table hints should you use in your query if you want to optimize transaction logging and locking
for the query?
A. You should make use of the READPAST hint.
B. You should make use of the HOLDLOCK hint.
C. You should make use of the READCOMMITTED hint.
D. You should make use of the NOLOCK hint.
E. You should make use of the TABLOCK hint.
F. You should make use of the UPDLOCK hint.
Correct Answer: E
QUESTION 6
You administer a Microsoft SQL Server 2012 database that has Trustworthy set to On. You create a stored procedure
that returns database-level information from Dynamic Management Views. You grant User1 access to execute the
stored
procedure. You need to ensure that the stored procedure returns the required information when User1 executes the
stored procedure. You need to achieve this goal by granting the minimum permissions required. What should you do?
(Each
correct answer presents a complete solution.
Choose all that apply.)
A. Create a SQL Server login that has VIEW SERVER STATE permissions. Create an application role and a secured
password for the role.
B. Modify the stored procedure to include the EXECUTE AS OWNER statement. Grant VIEW SERVER STATE
permissions to the owner of the stored procedure.
C. Create a SQL Server login that has VIEW SERVER STATE permissions. Modify the stored procedure to include the
EXECUTE AS {newlogin} statement.
D. Grant the db_owner role on the database to User1.
E. Grant the sysadmin role on the database to User1.
Correct Answer: DE
QUESTION 7
What levels can an index have? (Choose all that apply.)
A. Intermediate level
B. Heap level
C. Root level
D. Leaf level
Correct Answer: ACD
QUESTION 8
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 BlogEntrySET 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
According to the append possibility of .Write method when offset is specified as NULL. https://msdn.microsoft.com/en-
us/library/ms177523.aspx
QUESTION 9
You develop a Microsoft SQL Server 2012 database that contains a heap named OrdersHistorical.
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
Reference: http://technet.microsoft.com/en-us/library/ms189857.aspx
Reference: http://msdn.microsoft.com/en-us/library/ms187373.aspx
QUESTION 10
You develop three Microsoft SQL Server 2012 databases named Database1, Database2, and Database3. You have
permissions on both Database1 and Database2. You plan to write and deploy a stored procedure named
dbo.usp_InsertEvent in Database3. dbo.usp_InsertEvent must execute other stored procedures in the other databases.
You need to ensure that callers that do not have permissions on Database1 or Database2 can execute the stored
procedure. Which Transact-SQL statement should you use?
A. USE Database2
B. EXECUTE AS OWNER
C. USE Database1
D. EXECUTE AS CALLER
Correct Answer: B
http://msdn.microsoft.com/en-us/library/ms188354.aspx http://blog.sqlauthority.com/2007/10/06/sql-server-executing-
remote-stored-procedure-calling- stored-procedure-on- linked-server/
QUESTION 11
You use Microsoft SQL Server 2012 database to develop a shopping cart application.
You need to invoke a table-valued function for each row returned by a query.
Which Transact-SQL operator should you use?
A. CROSS JOIN
B. UNPIVOT
C. PIVOT
D. CROSS APPLY
Correct Answer: D
Reference: http://msdn.microsoft.com/en-us/library/ms175156.aspx
QUESTION 12
You have a SQL Server database that contains all of the customer data for your company.
You need to extract a random 1,000 row sample from a table Customers.
Part of the correct Transact-SQL has been provided in the answer area below. Enter the code in the answer area that
resolves the problem and meets the stated goals or requirements. You can add code within the code that has been
provided as well as below it.
Use the Check Syntax button to verify your work. Any syntax or spelling errors will be reported by line and character
position.
Correct Answer: TABLESAMPLE SYSTEM (1000 ROWS)
Update line 3 to get the following:
SELECT *
FROM Customers
TABLESAMPLE SYSTEM (1000 ROWS)
The TABLESAMPLE clause limits the number of rows returned from a table in the FROM clause to a sample number
orPERCENT of rows.
Syntax: TABLESAMPLE [SYSTEM] (sample_number [ PERCENT | ROWS ] )
References: https://technet.microsoft.com/en-us/library/ms189108(v=sql.105).aspx
QUESTION 13
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
Share 13 of the latest Microsoft Business Intelligence 70-461 exam questions and answers for free to help you improve your skills and experience! Easily select the complete 70-461 Dumps: https://www.leads4pass.com/70-461.html (Total Questions: 323 Q&A) through the exam! Guaranteed to be true and effective! Easily pass the exam!
[PDF] Free Microsoft Business Intelligence 70-461 pdf dumps download from Google Drive: https://drive.google.com/open?id=1s9_DWVmtzecejmoM6gF9OBqCFDTC3iM2
[PDF] Free Full Microsoft pdf dumps download from Google Drive: https://drive.google.com/open?id=1AwBFPqkvdpJBfxdZ3nGjtkHQZYdBsRVz
Lead4pass Promo Code 12% Off
Why Choose Lead4pass?
Lead4pass is the best provider of IT learning materials and the right choice for you to prepare for Microsoft Business Intelligence 70-461 exam. Other brands started earlier, but the price is relatively expensive and the questions are not the newest. Lead4pass provide the latest real questions and answers with lowest prices, help you pass Microsoft 70-461 exam easily at first try.
related: https://passexamleader.com/useful-microsoft-mcse-70-463-dumps/