Microsoft 70-461 Online Practice Questions and Exam Preparation

Microsoft 70-461 Online Questions & Answers

• Question 311:

  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
  B. EXECUTE AS OWNER

  ---

  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 312:

  How can you express that the column categoryname allow NULLs?

  A. categoryname PERMIT NULL NVARCHAR(15)
  B. categoryname NVARCHAR(15) ALLOW NULL
  C. categoryname NVARCHAR(15) PERMIT NULL
  D. categoryname NVARCHAR(15) NULL
  D. categoryname NVARCHAR(15) NULL

• Question 313:

  In which phase of query execution does SQL Server check whether the objects referred to by the query exist?

  A. In the parsing phase
  B. In the binding phase
  C. In the optimization phase
  D. In the execution phase
  B. In the binding phase

• Question 314:

  You develop a Microsoft SQL Server 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?

  CREATE TRIGGER TrgVwEmployee

  A. 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 CREATE TRIGGER TrgVwEmployee
  C. 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
  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 CREATE TRIGGER TrgVwEmployee

• Question 315:

  What do the RANK and DENSE_RANK functions compute?

  A. The RANK function returns the number of rows that have a lower ordering value (assuming ascending ordering) than the current; the DENSE_RANK function returns the number of distinct ordering values that are lower than the current.
  B. The RANK function returns one more than the number of rows that have a lower ordering value than the current; the DENSE_RANK function returns one more than the number of distinct ordering values that are lower than the current.
  C. The RANK function returns one less than the number of rows that have a lower ordering value than the current; the DENSE_RANK function returns one less than the number of distinct ordering values that are lower than the current.
  D. The two functions return the same result unless the ordering is unique.
  B. The RANK function returns one more than the number of rows that have a lower ordering value than the current; the DENSE_RANK function returns one more than the number of distinct ordering values that are lower than the current.

• Question 316:

  Why are window functions allowed only in the SELECT and ORDER BY clauses of a query?

  A. Because they are supposed to operate on the underlying query's result, which is achieved when logical query processing gets to the SELECT phase.
  B. Because Microsoft didn't have time to implement them in other clauses.
  C. Because you never need to filter or group data based on the result of window functions.
  D. Because in the other clauses, the functions are considered door functions (also known as backdoor functions).
  A. Because they are supposed to operate on the underlying query's result, which is achieved when logical query processing gets to the SELECT phase.

• Question 317:

  You have a database named SALES that stored the sales data and the salespeople for your company.

  You need to create a function that is passed a ProductID and then returns a list of SalesOrderID values for orders that must be associated to a salesperson in the future. The function must be able to return multiple SalesOrderID values. 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.

  WHERE header.SalesPersonID IS NULL

  On line 8 add: IS NULL to get: CREATE FUNCTION dbo.OrdersWithNoSalesPerson(@ProductID INT) RETURNS AS header.SalesOrderID FROM Sales.SalesOrderDetail detail INNER JOIN Sales.SalesOrderHeader header on header.SalesOrderID = detail.SalesOrderID WHERE header.SalesPersonID IS NULL AND detail.ProductID = @ProductID; GO Note: IS NULL determines whether a specified expression is NULL. If the value of expression is NULL, IS NULL returns TRUE; otherwise, it returns FALSE. To determine whether an expression is NULL, use IS NULL or IS NOT NULL instead of comparison operators (such as = or !=). Comparison operators return UNKNOWN when either or both arguments are NULL. References:https://msdn.microsoft.com/en-us/library/ms188795.aspx

• Question 318:

  Your manager has asked you to extend one of the complex stored procedures used to calculate time differences between lab test results. In particular, during the stored procedures you need to check the exact minute value of a datatime value. Your manager has suggested that you use the DATENAME function and pass in the date and datepart argument. Which datepart argument should you use to get the minute?

  A. m
  B. n
  C. mcs
  D. mm
  B. n

  ---

  If you want to get the minute, you can either pass in mi or n as the argument to DATENAME.

• Question 319:

  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.
  D. Create a clustered index on the view.

  ---

  http://msdn.microsoft.com/en-us/library/ms188783.aspx

• Question 320:

  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 retrieve the students who scored the highest marks for each subject along with the marks. 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 FRCM ( SELECT StudentCode, Marks AS Marks, RANK() OVER (PARTITION BY SubjectCode ORDER 3Y Marks DESC) AS Rank FRCM 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
  F. SELECT StudentCode AS Code,Marks AS Value FRCM ( SELECT StudentCode, Marks AS Marks, RANK() OVER (PARTITION BY SubjectCode ORDER 3Y Marks DESC) AS Rank FRCM StudentMarks) tmp WHERE Rank = 1