Microsoft 70-461 brain dumps 2020 [q1-q10]
QUESTION 1
You use a Microsoft SQL Server 2012 database.
You need to create an indexed view within the database for a report that displays Customer Name and the total revenue
for that customer.
Which four T-SQL statements should you use? (To answer, move the appropriate SQL statements from the list of
statements to the answer area and arrange them in the correct order.)
Select and Place:
Correct Answer:
QUESTION 2
You need to create a stored procedure that enters values into multiple tables. The solution must ensure that if a single
insert fails, none of the values are inserted into the tables.
How should you complete the stored procedure? To answer, drag the appropriate values to the correct locations. Each
value may be used once, more than once, or not at all. You may need to drag the split bar between panes or scroll to
view
content.
Select and Place: 
Correct Answer:
Box 1: BEGIN TRY Box 2:SAVE TRANSACTION Box 3: ROLLBACK Box 4: COMMIT TRANSACTION
References:https://msdn.microsoft.com/en-us/library/ms188378.aspx
QUESTION 3
You\\’ve created a standard stored procedure that\\’s not a CLR procedure. This procedure has several parameters,
some of which are output parameters. Which of the following CANNOT be passed back as an output parameter from a
stored procedure?
A. Text
B. image
C. Ntext
D. Any of these
Correct Answer: D
Unless you create a CLR procedure, you can use these data types are return values.
QUESTION 4
You use Microsoft SQL Server 2012 to write code for a transaction that contains several statements. There is high
contention between readers and writers on several tables used by your transaction. You need to minimize the use of the
tempdb space. You also need to prevent reading queries from blocking writing queries. Which isolation level should you
use?
A. SERIALIZABLE
B. SNAPSHOT
C. READ COMMITTED SNAPSHOT
D. REPEATABLE READ
Correct Answer: C
1.
“There is high contention between readers and writers”
SNAPSHOT and RED COMMITTED SNAPSHOT will not apply share locks so minimize the contention.
https://msdn.microsoft.com/en-us/library/ms173763.aspx
2.
“need to minimize the use of the tempdb space”
RED COMMITTED SNAPSHOT uses less tempdb space.
https://msdn.microsoft.com/en-us/library/ms378149(v=sql.110).aspx So the answer is RED COMMITTED SNAPSHOT
QUESTION 5
What is missing from the T-SQL statement below for creating and using a cursor? DECLARE mycursor CURSOR FOR
SELECT * FROM Vendor FETCH NEXT FROM mycursor;
A. KEYSET
B. OPEN
C. STATIC
D. SCROLL
Correct Answer: B
You need to open the CURSOR before you can call FETCH.
QUESTION 6
You are the database administrator of a SQL Server 2012 database infrastructure at ABC.com.
You need to optimize a very large database table that contains several million rows of data by designing a view based
on the table. The view must allow users to perform aggregations on several columns.
How should you configure the view to ensure optimal performance?
A. You should create the view as an indexed view.
B. You should create a clustered index on the view.
C. You should make use of a stored procedure to return that data.
D. You should make use of a table-valued function.
Correct Answer: A
QUESTION 7
You work as a SQL Server 2012 database developer at ABC.com. You are developing a query for a database driven
Web application that allows visitors to vote for the cricket player of the week. The number of votes is stored in a table
named
WeeklyVotes that has columns named Week, PlayerName, Votes.
You need to write a Transact-SQL query that ranks the top 30 cricket players by the average votes over the last 12
months. You want the top 10 cricket players to have a rank of 1, the next 10 to have a rank of 2, and the last 10 to have
a rank
of 3.
Which of the following SELECT statement would you use? To answer, type the correct code in the answer area.
A. check the answer in explanation.
Correct Answer: A
SELECT TOP 30 PlayerName, NTILE (3) OVER (ORDER BY AVG (Votes) DESC) AS AveVotes FROM WeeklyVotes
GROUP BY PlayerName
QUESTION 8
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 9
Which of the following statements are true about an INSTEAD OF trigger? (Choose all that apply.)
A. INSTEAD OF triggers can be created on views.
B. INSTEAD OF triggers execute instead of AFTER triggers.
C. INSTEAD OF triggers can only be declared for UPDATE statements.
D. INSTEAD OF triggers execute code in place of the original DML statement
Correct Answer: AD
QUESTION 10
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
Correct Answer: D
http://technet.microsoft.com/en-us/library/ms181034.aspx
Can you still buy 70-461 dumps?
Preparing your 70-461 dumps? 70-461, Querying Microsoft SQL Server 2012 is consists of different types of multiple choice questions and answers. In the https://www.pass4itsure.com/70-461.html dumps exam resources, It will be the hot test associated with Microsoft 70-461 dumps practice exam qualification and you will cover every field and category in Querying Microsoft SQL Server 2012 helping to ready you for your successful MCP, MCSA, MCSE certification.
Exam 70-461
SQL Server
Published: June 11, 2012
Languages: English, Chinese (Simplified), French, German, Japanese, Portuguese (Brazil)
Audiences: IT professionals
Technology: Microsoft SQL Server 2012/2014
Credit towards certification: MCP, MCSA, MCSE
[2018-5-NEW Pass4itsure 70-461 PDF Dumps Questions From Google Drive]: https://drive.google.com/open?id=0BwxjZr-ZDwwWRFNLVl8xNFJPejg
[2018-5-NEW Pass4itsure 70-463 PDF Dumps Questions From Google Drive]:https://drive.google.com/open?id=0BwxjZr-ZDwwWanJic19DTmpWLUU
Up to the immediate present 70-461 exam cram(1-28)
QUESTION 1
You administer a Microsoft SQL Server 2012 database named ContosoDb. Tables are defined as shown in the exhibit. (Click the Exhibit button.)
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
QUESTION 2
You are developing a database application by using Microsoft SQL Server 2012.
An application that uses a database begins to run slowly.
Your investigation shows the root cause is a query against a read-only table that has a clustered index.
The query returns the following six columns:
One column in its WHERE clause contained in a non-clustered index Four additional columns One COUNT (*) column based on a grouping of the four additional columns
You need to optimize the statement.
What should you do?
A. Add a HASH hint to the query.
B. Add a LOOP hint to the query.
C. Add a FORCESEEK hint to the query.
D. Add an INCLUDE clause to the index.
E. Add a FORCESCAN hint to the Attach query.
F. Add a columnstore index to cover the query.
G. Enable the optimize for ad hoc workloads option.
H. Cover the unique clustered index with a columnstore index.
I. Include a SET FORCEPLAN ON statement before you run the query.
J. Include a SET STATISTICS PROFILE ON statement before you run the query.
K. Include a SET STATISTICS SHOWPLAN_XML ON statement before you run the query.
L. Include a SET TRANSACTION ISOLATION LEVEL REPEATABLE READ statement before you run the query.
M. Include a SET TRANSACTION ISOLATION LEVEL SNAPSHOT statement before you run the query.
N. Include a SET TRANSACTION ISOLATION LEVEL SERIALIZABLE statement before you run the query.
Correct Answer: F
QUESTION 3
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 s
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 s
WHERE s.CustomerID = @CustomerID
AND s.OrderDate > DATEADD(DAY, [email protected], GETDATE())
Correct Answer: A
QUESTION 4
You are maintaining a Microsoft SQL Server database that stores order information for an online store website. The database contains a table that is defined by the following Transact-SQL statement:
You need to ensure that purchase order numbers are used only for a single order.
What should you do?
A. Create a new CLUSTERED constraint on the PurchaseOrderNumber column.
B. Create a new UNIQUE constraint on the PurchaseOrderNumber column.
C. Create a new PRIMARY constraint on the PurchaseOrderNumber column.
D. Create a new FOREIGN KEY constraint on the PurchaseOrderNumber column.
Correct Answer: B
QUESTION 5
You use a contained database named ContosoDb within a domain. You need to create a user who can log on to the ContosoDb database. You also need to ensure that you can port the database to different database servers within the
domain without additional user account configurations.
Which type of user should you create?
A. SQL user without login
B. SQL user with a custom SID
C. SQL user with login
D. Domain user
Correct Answer: A
QUESTION 6
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 create a Foreign Key constraint that references a different table in the database?
A. DateHired
B. DepartmentID
C. EmployeeID
D. EmployeeNum
E. FirstName
F. JobTitle
G. LastName
H. MiddleName
I. ReportsToID
Correct Answer: C
QUESTION 7
You support a database structure shown in the exhibit. (Click the Exhibit button.)
You need to write a query that displays the following details:
Total sales made by sales people, year, city, and country Sub totals only at the city level and country level A grand total of the sales amount
Which Transact-SQL query should you use?
A. SELECT SalesPerson.Name, Country, City,
DatePart(yyyy, SaleDate) AS Year, Sum(Amount) AS Total FROM Sale INNER JOIN SalesPerson ON Sale.SalesPersonID = SalesPerson.SalesPersonID GROUP BY GROUPING SETS ((SalesPerson.Name, Country, City, DatePart
(yyyy, SaleDate)), (Country, City), (Country), ())
B. SELECT SalesPerson.Name, Country, City,
DatePart(yyyy, SaleDate) AS Year, Sum(Amount) AS Total FROM Sale INNER JOIN SalesPerson ON Sale.SalesPersonID = SalesPerson.SalesPersonID GROUP BY CUBE(SalesPerson.Name, Country, City, DatePart(yyyy, SaleDate))
C. SELECT SalesPerson.Name, Country, City,
DatePart(yyyy, SaleDate) AS Year, Sum(Amount) AS Total FROM Sale INNER JOIN SalesPerson ON Sale.SalesPersonID = SalesPerson.SalesPersonID GROUP BY CUBE(SalesPerson.Name, DatePart(yyyy, SaleDate), City, Country)
D. SELECT SalesPerson.Name, Country, City,
DatePart(yyyy, SaleDate) AS Year, Sum(Amount) AS Total FROM Sale INNER JOIN SalesPerson ON Sale.SalesPersonID = SalesPerson.SalesPersonID GROUP BY ROLLUP(SalesPerson.Name, DatePart(yyyy, SaleDate), City,
Country)
Correct Answer: A
QUESTION 8
You plan to write a query for a new business report that will contain several nested queries.
You need to ensure that a nested query can call a table-valued function for each row in the main query.
Which query operator should you use in the nested query?
A. CROSS APPLY
B. INNER JOIN
C. OUTER JOIN
D. PIVOT
Correct Answer: A
QUESTION 9
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. Smallmoney
B. Numeric
C. Money
D. Varchar
Correct Answer: B
QUESTION 10
You develop a Microsoft SQL Server 2012 server database that supports an application.
The application contains a table that has the following definition:
CREATE TABLE Inventory (
ItemID int NOT NULL PRIMARY KEY,
ItemsInStore int NOT NULL,
ItemsInWarehouse int NOT NULL)
You need to create a computed column that returns the sum total of the ItemsInStore and ItemsInWarehouse values for each row.
The new column is expected to be queried heavily, and you need to be able to index the column. Which Transact-SQL statement should you use?
A. ALTER TABLE Inventory
ADD TotalItems AS ItemslnStore + ItemsInWarehouse
B. ALTER TABLE Inventory
ADD TotalItems AS ItemsInStore + ItemsInWarehouse PERSISTED
C. ALTER TABLE Inventory
ADD TotalItems AS SUM(ItemsInStore, ItemsInWarehouse) PERSISTED
D. ALTER TABLE Inventory
ADD TotalItems AS SUM(ItemsInStore, ItemsInWarehouse)
Correct Answer: B
QUESTION 11
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.
<CUSTOMERS Name=”Customer A” Country=”Australia”>
<ORDERS OrderID=”1″ OrderDate=”2001-01-01″ Amount=”3400.00″ />
<ORDERS OrderID=”2″ OrderDate=”2002-01-01″ Amount=”4300.00″ />
</CUSTOMERS>
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: E
QUESTION 12
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 PreviousYearProfit FROM Profits
B. SELECT Territory, Year, Profit, LAG(Profit, 1, 0) OVER (PARTITION BY Year ORDER BY Territory) AS PreviousYearProfit FROM Profits
C. SELECT Territory, Year, Profit, LAG(Profit, 1, 0) OVER (PARTITION BY Territory ORDER BY Year) AS PreviousYearProfit FROM Profits
D. SELECT Territory, Year, Profit, LEAD(Profit, 1, 0) OVER (PARTITION BY Year ORDER BY Territory) AS PreviousYearProfit FROM Profits
Correct Answer: C
QUESTION 13
You are a database developer for an application hosted on a Microsoft SQL Server 2012 server.
The database contains two tables that have the following definitions:
Global customers place orders from several countries.
You need to view the country from which each customer has placed the most orders.
Which Transact-SQL query do you use?
A. SELECT c.CustomerID, c.CustomerName, o.ShippingCountry FROM Customer c INNER JOIN
(SELECT CustomerID, ShippingCountry,
RANK() OVER (PARTITION BY CustomerID
ORDER BY COUNT(OrderAmount) DESC) AS Rnk
FROM Orders
GROUP BY CustomerID, ShippingCountry) AS o
ON c.CustomerID = o.CustomerID
WHERE o.Rnk = 1
B. SELECT c.CustomerID, c.CustomerName, o.ShippingCountry FROM (SELECT c.CustomerID, c.CustomerName, o.ShippingCountry, RANK() OVER (PARTITION BY CustomerID
ORDER BY COUNT(o.OrderAmount) ASC) AS Rnk
FROM Customer c
INNER JOIN Orders o
ON c.CustomerID = o.CustomerID
GROUP BY c.CustomerID, c.CustomerName, o.ShippingCountry) cs WHERE Rnk = 1
C. SELECT c.CustomerID, c.CustomerName, o.ShippingCountry FROM Customer c INNER JOIN
(SELECT CustomerID, ShippingCountry,
RANK() OVER (PARTITION BY CustomerID
ORDER BY OrderAmount DESC) AS Rnk
FROM Orders
GROUP BY CustomerID, ShippingCountry) AS o
ON c.CustomerID = o.CustomerID
WHERE o.Rnk = 1
D. SELECT c.CustomerID, c.CustomerName, o.ShippingCountry FROM Customer c INNER JOIN
(SELECT CustomerID, ShippingCountry,
COUNT(OrderAmount) DESC) AS OrderAmount
FROM Orders
GROUP BY CustomerID, ShippingCountry) AS o
ON c.CustomerID = o.CustomerID
ORDER BY OrderAmount DESC
Correct Answer: A
QUESTION 14
You are maintaining a Microsoft SQL Server database. You run the following query:
You need to ensure that the query performs returns the results as quickly as possible.
Which action should you perform?
A. Add a new index to the ID column of the Person table.
B. Add a new index to the EndDate column of the History table.
C. Create a materialized view that is based on joining data from the ActiveEmployee and History tables.
D. Create a computed column that concatenates the GivenName and SurName columns.
Correct Answer: A
QUESTION 15
You administer a Microsoft SQL Server 2012 database named ContosoDb. Tables are defined as shown in the exhibit. (Click the Exhibit button.)
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: G
QUESTION 16
You are developing a database in SQL Server 2012 to store information about current employee project assignments.
You are creating a view that uses data from the project assignment table.
You need to ensure that the view does not become invalid if the schema of the project assignment table changes.
What should you do?
A. Create the view by using an account in the sysadmin role.
B. Add a DDL trigger to the project assignment table to re-create the view after any schema change.
C. Create the view in a new schema.
D. Add a DDL trigger to the view to block any changes.
Correct Answer: B
QUESTION 17
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
QUESTION 18
You develop a Microsoft SQL Server 2012 database that has two tables named SavingAccounts and LoanAccounts. Both tables have a column named AccountNumber of the nvarchar data type.
You use a third table named Transactions that has columns named TransactionId AccountNumber, Amount, and TransactionDate.
You need to ensure that when multiple records are inserted in the Transactions table, only the records that have a valid AccountNumber in the SavingAccounts or LoanAccounts are inserted.
Which Transact-SQL statement should you use?
A. CREATE TRIGGER TrgValidateAccountNumber
ON Transactions
INSTEAD OF INSERT
AS
BEGIN
INSERT INTO Transactions
SELECT TransactionID,AccountNumber,Amount,TransactionDate FROM inserted WHERE AccountNumber IN
(SELECT AccountNumber FROM LoanAccounts
UNION SELECT AccountNumber FROM SavingAccounts) END
B. CREATE TRIGGER TrgValidateAccountNumber
ON Transactions
FOR INSERT
AS
BEGIN
INSERT INTO Transactions
SELECT TransactionID,AccountNumber,Amount,TransactionDate FROM inserted WHERE AccountNumber IN
(SELECT AccountNumber FROM LoanAccounts
UNION SELECT AccountNumber FROM SavingAccounts) END
C. CREATE TRIGGER TrgValidateAccountNumber
ON Transactions
INSTEAD OF INSERT
AS
BEGIN
IF EXISTS (
SELECT AccountNumber FROM inserted EXCEPT
(SELECT AccountNumber FROM LoanAccounts
UNION SELECT AccountNumber FROM SavingAccounts)) BEGIN
ROLLBACK TRAN
END
END
D. CREATE TRIGGER TrgValidateAccountNumber
ON Transactions
FOR INSERT
AS
BEGIN
IF EXISTS (
SELECT AccountNumber FROM inserted EXCEPT
(SELECT AccountNumber FROM LoanAccounts
UNION SELECT AccountNumber FROM SavingAccounts)) BEGIN
ROLLBACK TRAN
END
END
Correct Answer: A
QUESTION 19
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
QUESTION 20
You have a vendor application that uses a scalar function.
You discover that the queries for the application run slower than expected.
You need to gather the runtime information of the scalar function.
What are two possible ways to achieve this goal? Each correct answer presents a complete solution.
A. Enable the Include Actual Execution Plan option.
B. Enable the Display Estimated Execution Planoption.
C. Create and then enable a profiler trace.
D. Create and then enable an extended events trace.
E. Run the Database Engine Tuning Advisor.
Correct Answer: AD
QUESTION 21
Your application contains a stored procedure for each country. Each stored procedure accepts an employee identification number through the @EmpID 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
Correct Answer: E
QUESTION 22
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:
Return rows ordered by descending values in the UnitPrice column.
Use the Rank function to calculate the results based on the UnitPrice column.
Return the ranking of rows in a column that uses the alias PriceRank.
Use two-part names to reference tables.
Display the columns in the order that they are defined in the table. The PriceRank column must appear last.
Part of the correct T-SQL statement has been provided in the answer area. Provide the complete code.
Correct Answer: Explanation
QUESTION 23
You have an XML schema collection named Sales.InvoiceSchema.
You need to declare a variable of the XML type named XML1. The solution must ensure that XML1 is validated by using Sales.InvoiceSchema.
Which code segment should you use?
To answer, type the correct code in the answer area.
Correct Answer: DECLARE @XML1 XML(Sales.InvoiceSchema)
QUESTION 24
You generate a daily report according to the following query:
A. Option A
B. Option B
C. Option C
D. Option D
Correct Answer: A
QUESTION 25
You develop a SQL Server database for a large multinational company.
You must develop a stored procedure to loop through employee information and return data for one employee at a time.
How should you complete the stored procedure? To answer, drag the appropriate Transact-SQL segment to the correct locations in the answer area. Each Transact-SQL segment may be used once, more than once, or not at all. You may
need to drag the split bar between panes or scroll to view content.
Select and Place:
Correct Answer:
QUESTION 26
You have a database that contains the tables shown in the exhibit. (Click the Exhibit button.)
You need to create a query that calculates the total sales of each OrderId from the Sales.Details table. The solution must meet the following requirements:
Use one-part names to reference columns.
Sort the order of the results from OrderId.
NOT depend on the default schema of a user.
Use an alias of TotalSales for the calculated ExtendedAmount.
Display only the OrderId column and the calculated TotalSales column.
Which code segment should you use?
To answer, type the correct code in the answer area.
Correct Answer: Please review the explanation part for this answer
QUESTION 27
You use Microsoft SQL Server to develop a database application.
You create a stored procedure named usp_calculategrowth. The stored procedure modifies rows and can result in several different exceptions.
You need to ensure that when the stored procedure is executed, the following requirements are met:
Which six Transact-SQL statements should you use? (To answer, move the appropriate SQL statements from the list of statements to the answer area and arrange them in the correct order.)
Select and Place:
Correct Answer:
QUESTION 28
You have a database that contains the tables shown in the exhibit. (Click the Exhibit button.)
You need to create a view named uv_CustomerFullName to meet the following requirements:
The code must NOT include object delimiters.
The view must be created in the Sales schema.
Columns must only be referenced by using one-part names.
The view must return the first name and the last name of all customers.
The view must prevent the underlying structure of the customer table from being changed.
The view must be able to resolve all referenced objects, regardless of the user’s default schema.
Which code segment should you use?
To answer, type the correct code in the answer area.
Correct Answer: Please review the explanation part for this answe
Pass4itsure 70-461 dumps,
Querying Microsoft SQL Server 2012
is a proctored exam offered by pass4itsure Microsoft. This exam is specially linked with pass4itsure (SQL Server) Microsoft certified solutionse expert. This exam is available in English, Chinese (Simplified), French, German, Japanese, Portuguese (Brazil). Candidates connected with IT field can apply for 70-461 dumps Microsoft certification exams, and this is a MCP, MCSA, MCSE certification technology. The exam is closed book and no outside reference materials are allowed.
[2018-5-NEW Pass4itsure 70-461 PDF Dumps Questions From Google Drive]: https://drive.google.com/open?id=0BwxjZr-ZDwwWRFNLVl8xNFJPejg
[2018-5-NEW Pass4itsure 70-463 PDF Dumps Questions From Google Drive]:https://drive.google.com/open?id=0BwxjZr-ZDwwWanJic19DTmpWLUU
In most cases pass4itsure 70-461 exam collection may include 80% or so of the real test questions. If you master all questions and answers you will get 80% at least. So spending a small amount of time and money in exchange for such a good result is worthful. Please add pass4itsure 70-461 dumps pdf training tool in your shopping cart now. Pass4itsure’s experienced expert team has developed effective training program a for Microsoft certification 70-461 dumps, which is very fit for candidates.
I will highly recommend you to try pass4itsure.com for your 70-461 dumps for exam preparation. More important is that pass4itsure 70-461 dumps exam answers are applicable to all the IT exam. They provides you 100% valid and accurate 70-461 dumps with money back guarantee in case of failure.
Easily To Pass Microsoft 70-461 Dumps Exam Test, Real Microsoft 70-461 Dumps Covers All Key Points, We Help You Pass Querying Microsoft SQL Server 2012.
Pass4itsure Promo Code 15% Off
