Welcome to download the newest Pass4itsure 70-414 dumps:
Our Microsoft 70-461 with Explanations presents to you the most tried and tested methods of preparation for the actual exam. The Flydumps provides a very detailed preparation for your exam preparation, giving you answers to the entire exam question with the added explanation of which answer is right and why. These answers are prepared by professionals who have had years of experience and are fully competent to give you the best and the most excellent way to prepare for your actual exam.
QUESTION 20
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. Correct Answer: D
Explanation Explanation/Reference:
Explanation:
QUESTION 21
You develop a Microsoft SQL Server 2012 database that contains tables named Employee and Person.
The tables have the following definitions:
Users are able to use single INSERT statements or INSERT…SELECT statements into this view.
You need to ensure that users are able to use a single statement to insert records into both Employee and Person tables by using the VwEmployee view.
Which Transact-SQL statement should you use?
A. CREATE TRIGGER TrgVwEmployee ON VwEmployee FOR INSERT AS BEGIN INSERT INTO Person(Id, FirstName, LastName) SELECT Id, FirstName, LastName, FROM inserted INSERT INTO Employee(PersonId, EmployeeNumber) SELECT Id, EmployeeNumber FROM inserted END
B. CREATE TRIGGER TrgVwEmployee ON VwEmployee INSTEAD OF INSERT AS BEGIN INSERT INTO Person(Id, FirstName, LastName) SELECT Id, FirstName, LastName, FROM inserted INSERT INTO Employee(PersonId, EmployeeNumber) SELECT Id, EmployeeNumber FROM inserted END
C. CREATE TRIGGER TrgVwEmployee ON VwEmployee INSTEAD OF INSERT AS BEGIN DECLARE @ID INT, @FirstName NVARCHAR(25), @LastName NVARCHAR(25), @PersonID INT, @EmployeeNumber NVARCHAR(15) SELECT @ID = ID, @FirstName = FirstName, @LastName = LastName, @EmployeeNumber = EmployeeNumber FROM inserted INSERT INTO Person(Id, FirstName, LastName) VALUES(@ID, @FirstName, @LastName) INSERT INTO Employee(PersonID, EmployeeNumber) VALUES(@PersonID, @EmployeeNumber End
D. CREATE TRIGGER TrgVwEmployee ON VwEmployee INSTEAD OF INSERT AS BEGIN INSERT INTO Person(Id, FirstName, LastName) SELECT Id, FirstName, LastName FROM VwEmployee INSERT INTO Employee(PersonID, EmployeeNumber) SELECT Id, EmployeeNumber FROM VwEmployee End
Correct Answer: B Explanation
Explanation/Reference:
QUESTION 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:
QUESTION 23
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 Explanation
Explanation/Reference:
QUESTION 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:
QUESTION 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 26
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 Explanation
Explanation/Reference:
Explanation:
Be careful with this question,because on exam can be different options for answer. And none of them is
correct : D You should report this question.
QUESTION 27
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. Float
B. Money
C. Smallmoney
D. Numeric
Correct Answer: D Explanation
Explanation/Reference:
Explanation:
Numeric is the only one in the list that can give a fixed precision and scale.
QUESTION 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:
QUESTION 29
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.)
Flydumps.com will provide you with the most updates material to prepare for the tests all the Microsoft 70-461 torrent are available at the site. Studying with dumps makes it much easier to pass the certification. Number of networking downloads including the Microsoft 70-461 download are available on the website. Various websites offering such information have information in various formats you can easily download the format that is suitable for you it can be in Microsoft 70-461 Testing Engine or in html.
Welcome to download the newest Pass4itsure 70-414 dumps: https://www.pass4itsure.com/70-414.html
Microsoft 70-461 Exam PDF, 100% Pass Guarantee Microsoft 70-461 Practice Test Latest Version PDF&VCE
