70-461 Exam
Ideas to 70 461 exam
Cause all that matters here is passing the Microsoft 70 461 book exam. Cause all that you need is a high score of exam 70 461 pdf Querying Microsoft SQL Server 2012 exam. The only one thing you need to do is downloading Ucertify 70 461 exam questions pdf exam study guides now. We will not let you down with our money-back guarantee.
Q11. Your application contains a stored procedure for each country. Each stored procedure accepts an employee identification number through the @EmplD parameter.
You need to build a single process for each employee that will execute the appropriate stored procedure based on the country of residence.
Which approach should you use?
A. A SELECT statement that includes CASE
B. Cursor
C. BULK INSERT
D. View
E. A user-defined function
Answer: E
Explanation: SQL Server user-defined functions are routines that accept parameters, perform an action, such as a complex calculation, and return the result of that action as a value. The return value can either be a single scalar value or a result set.
Q12. 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 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.
Part of the correct T-SQL statement has been provided in the answer area. Provide the complete code.
Answer:
Q13. 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
Q14. 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:
Q15. 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
Answer: E
Q16. You create a view based on the following statement:
You grant the Select permission to User1.
You need to change the view so that it displays only the records that were processed in the month prior to the current month. You need to ensure that after the changes, the view functions correctly for User1.
Which Transact-SQL statement should you use?
A. Option A
B. Option B
C. Option C
D. Option D
Answer: C
Q17. You have three tables that contain data for vendors, customers, and agents. You create a view that is used to look up telephone numbers for these companies.
The view has the following definition:
You need to ensure that users can update only the phone numbers by using this view.
What should you do?
A. Alter the view. Use the EXPAND VIEWS query hint along with each SELECT statement.
B. Drop the view. Re-create the view by using the SCHEMABINDING clause, and then create an index on the view.
C. Create an AFTER UPDATE trigger on the view.
D. Create an INSTEAD OF UPDATE trigger on the view.
Answer: D
Q18. 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)
Answer: B,D
Q19. CORRECT TEXT
You have a database that contains the tables shown in the exhibit. (Click the Exhibit button).
You need to create a query for a report. The query must meet the following requirements:
NOT use object delimiters.
Return the most recent orders first.
Use the first initial of the table as an alias.
Return the most recent order date for each customer.
Retrieve the last name of the person who placed the order.
Return the order date in a column named MostRecentOrderDate that appears as
the last column in the report. The solution must support the ANSI SQL-99 standard.
Which code segment should you use?
To answer, type the correct code in the answer area.
Answer:
Q20. You generate a daily report according to the following query:
You need to improve the performance of the query.
What should you do?
A. Drop the UDF and rewrite the report query as follows:
WITH cte(CustomerID, LastOrderDate) AS (
SELECT CustomerID, MAX(OrderDate) AS [LastOrderDate]
FROM Sales.SalesOrder
GROUP BY CustomerID
)
SELECT c.CustomerName
FROM cte
INNER JOIN Sales.Customer c
ON cte.CustomerID = c.CustomerID
WHERE cte.LastOrderDate < DATEADD(DAY, -90, GETDATE())
B. Drop the UDF and rewrite the report query as follows:
SELECT c.CustomerName
FROM Sales.Customer c
WHERE NOT EXISTS (
SELECT s.OrderDate
FROM Sales.SalesOrder
WHERE s.OrderDate > DATEADD(DAY, -90, GETDATE())
AND s.CustomerID = c.CustomerID)
C. Drop the UDF and rewrite the report query as follows:
SELECT DISTINCT c.CustomerName
FROM Sales.Customer c
INNER JOIN Sales.SalesOrder s
ON c.CustomerID = s.CustomerID
WHERE s.OrderDate < DATEADD(DAY, -90, GETDATE())
D. Rewrite the report query as follows:
SELECT c.CustomerName
FROM Sales.Customer c
WHERE NOT EXISTS (SELECT OrderDate FROM
Sales.ufnGetRecentOrders(c.CustomerID, 90))
Rewrite the UDF as follows:
CREATE FUNCTION Sales.ufnGetRecentOrders(@CustomerID int, @MaxAge datetime)
RETURNS TABLE AS RETURN (
SELECT OrderDate
FROM Sales.SalesOrder
WHERE s.CustomerID = @CustomerID
AND s.OrderDate > DATEADD(DAY, -@MaxAge, GETDATE())
Answer: A