Examcollection is the only one provider that presents the complete Microsoft Microsoft online training course for your 70-461 test. Examcollections site has each of the more traditional and precise Microsoft practice questions that based on the actual 70-461 exam. We have essentially the most updated Microsoft 70-461 braindumps which are revised in accordance with the Microsoft certification syllabus. Naturally, you can pick other Microsoft 70-461 training course, however, Examcollection has the most accurate, recent and also valued Microsoft 70-461 study manual available. Whether or not you are a new beginner or perhaps a veteran, you can get the Microsoft 70-461 certificate with ease simply by using the Microsoft online training materials. You may get the greatest Microsoft 70-461 exam dumps but within a low price tag. Wait for just what? Click the mouse now and acquire the Microsoft certification tomorrow!
2021 Jan 70-461 exam question
Q1. Your application contains a stored procedure for each country. Each stored procedure accepts an employee identification number through the @EmpID parameter.
You plan to build a single process for each employee that will execute the stored procedure based on the country of residence.
Which approach should you use?
A. A recursive stored procedure
B. Trigger
C. An UPDATE statement that includes CASE
D. Cursor
E. The foreach SQLCLR statement
Answer: D
Q2. CORRECT TEXT
You have an XML schema collection named Sales.InvoiceSchema.
You need to declare a variable of the XML type named invoice. The solution must ensure
that the invoice is validated by using Sales.InvoiceSchema.
Provide the correct code in the answer area.
Answer:
DECLARE @invoice XML(Sales.InvoiceSchema)
Q3. DRAG DROP
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.)
Answer:
Q4. 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.)
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 a create a unique constraint?
A. DateHired
B. DepartmentID
C. EmployeelD
D. EmployeeNum
E. FirstName
F. JobTitle
G. LastName
H. MiddleName
I. ReportsToID
Answer: D
Q5. Your database contains tables named Products and ProductsPriceLog. The Products table contains columns named ProductCode and Price. The ProductsPriceLog table contains columns named ProductCode, OldPrice, and NewPrice.
The ProductsPriceLog table stores the previous price in the OldPrice column and the new price in the NewPrice column.
You need to increase the values in the Price column of all products in the Products table by 5 percent. You also need to log the changes to the ProductsPriceLog table.
Which Transact-SQL query should you use?
A. UPDATE Products SET Price = Price * 1.05
OUTPUT inserted.ProductCode, deleted.Price, inserted.Price
INTO ProductsPriceLog(ProductCode, OldPrice, NewPrice)
B. UPDATE Products SET Price = Price * 1.05
OUTPUT inserted.ProductCode, inserted.Price, deleted.Price
INTO ProductsPriceLog(ProductCode, OldPrice, NewPrice)
C. UPDATE Products SET Price = Price * 1.05
OUTPUT inserted.ProductCode, deleted.Price, inserted.Price *
INTO ProductsPriceLog(ProductCode, OldPrice, NewPrice)
D. UPDATE Products SET Price = Price * 1.05
INSERT INTO ProductsPriceLog (ProductCode, CldPnce, NewPrice;
SELECT ProductCode, Price, Price * 1.05 FROM Products
Answer: A
Rebirth 70-461 testing engine:
Q6. 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
Answer: D
Q7. DRAG DROP
You create the following stored procedure. (Line numbers are included for reference only.)
You need to ensure that the stored procedure performs the following tasks:
. If a record exists, update the record.
. If no record exists, insert a new record.
Which four Transact-SQL statements should you insert at line 07? (To answer, move the appropriate statements from the list of statements to the answer area and arrange them in the correct order.)
Answer:
Q8. You create a table that has the StudentCode, SubjectCode, and Marks columns to record mid-year marks for students. The table has marks obtained by 50 students for various subjects.
You need to ensure that the top half of the students arranged by their average marks must be given a rank of 1 and the remaining students must be given a rank of 2. Which Transact-SQL query should you use?
A. SELECT StudentCode as Code, RANK() OVER (ORDER BY AVG (Marks) DESC) AS Value FROM StudentMarks GROUP BY StudentCode
B. SELECT Id, Name, Marks, DENSE_RANK() OVER (ORDER BY Marks DESC) AS Rank FROM StudentMarks
C. SELECT StudentCode as Code, DENSE_RANK() OVER (ORDER BY AVG (Marks) DESC) AS Value FROM StudentMarks GROUP BY StudentCode
D. SELECT StudentCode as Code, NTILE (2) OVER (ORDER BY AVG (Marks) DESC) AS Value FROM StudentMarks GROUP BY StudentCode
E. SELECT StudentCode AS Code,Marks AS Value FROM ( SELECT StudentCode, Marks AS Marks, RANK() OVER (PARTITION BY SubjectCode ORDER BY Marks ASC) AS Rank FROM StudentMarks) tmp WHERE Rank = 1
F. SELECT StudentCode AS Code,Marks AS Value FROM ( SELECT StudentCode, Marks AS Marks, RANK() OVER (PARTITION BY SubjectCode ORDER BY Marks DESC) AS Rank FROM StudentMarks) tmp WHERE Rank = 1
G. SELECT StudentCode AS Code,Marks AS Value FROM ( SELECT StudentCode, Marks AS Marks, RANK () OVER (PARTITION BY StudentCode ORDER BY Marks ASC) AS Rank FROM StudentMarks) tmp WHERE Rank = 1
H. SELECT StudentCode AS Code,Marks AS Value FROM ( SELECT StudentCode, Marks AS Marks, RANXO OVER (PARTITION BY StudentCode ORDER BY Marks DESC) AS Rank FROM StudentMarks) tmp WHERE Rank = 1
Answer: D
Q9. 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
Q10. 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
Answer: B