Microsoft 70-461 Online Practice Questions and Exam Preparation

Microsoft 70-461 Online Questions & Answers

• Question 371:

  You work as a database developer at ABC.com. ABC.com has a SQL Server 2012 database infrastructure with several databases. You have permissions on two of these databases, namely, CommDB and SalesDB.

  You need to develop a stored procedure named dbo.ABC_addEntry in a database named AssetsDB. The dbo.ABC_addEntry stored procedure will run stored procedures in CommDB and SalesDB.

  How should you design the stored procedure so that callers that do not paver permissions on CommDB and SalesDB can run the dbo.ABC_addEntry stored procedure successfully?

  A. You should configure the stored procedure to call the xp_cmdshell extended stored procedure.
  B. You should configure the stored procedure to call the sp_configure system stored procedure.
  C. You should assign users permission to the stored procedure.
  D. You should include the EXECUTE AS CALLER clause when creating the stored procedure.
  E. You should include the EXECUTE AS OWNER clause when creating the stored procedure.
  E. You should include the EXECUTE AS OWNER clause when creating the stored procedure.

• Question 372:

  You develop a database application for a university. You need to create a view that will be indexed that meets the following requirements:

  Displays the details of only students from Canada.

  Allows insertion of details of only students from Canada.

  Which four 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:

  

  

• Question 373:

  You have a database that contains the tables shown in the exhibit. (Click the Exhibit button.)

  

  

  You have an application named Appl. You have a parameter named @Count that uses the int data type. App1 is configured to pass @Count to a stored procedure. You need to create a stored procedure named usp_Customers for Appl. Usp_Customers must meet the following requirements:

  1. NOT use object delimiters.

  2. Minimize sorting and counting.

  3. Return only the last name of each customer in alphabetical order.

  4. Return only the number of rows specified by the @Count parameter.

  5. The solution must NOT use BEGIN and END statements.

  Which code segment should you use?

  To answer, type the correct code in the answer area.

  Check the answer below

  CREATE PROCEDURE usp_Customers @Count int AS SELECT TOP(@Count) Customers.LastName FROM Customers ORDER BY Customers.LastName

• Question 374:

  You need to write a new query that will return names from the employee table for people who are not part of the contractors table (the two tables share a unique id, similar to social security or NI number). Your query will use a sub query to

  check that the employee is not in the contractors table.

  How might you achieve this?

  A. Use COALESCE
  B. Use EXISTS
  C. Use IS_MEMBER
  D. Use EXP
  B. Use EXISTS

• Question 375:

  You need to create a query that calculates the total sales of each OrderID from a table named Sales.Details. The table contains two columns named OrderID and ExtendedAmount. The solution must meet the following requirements:

  1. Use one-part names to reference columns.

  2. Order the results by OrderID with the smallest value first.

  3. NOT depend on the default schema of a user.

  4. Use an alias of TotalSales for the calculated ExtendedAmount.

  5. Display only the OrderID column and the calculated TotalSales column.

  Provide the correct code in the answer area.

  Key Words:

  

  Check the answer below

  SELECT OrderId, SUM(ExtendedAmount) AS TotalSales FROM Sales.Details ORDER BY OrderID ASC

• Question 376:

  What are the benefits of using an UPDATE statement based on joins? (Choose all that apply.)

  A. You can filter the rows to update based on information in related rows in other tables.
  B. You can update multiple tables in one statement.
  C. You can collect information from related rows in other tables to be used in the source expressions in the SET clause.
  D. You can use data from multiple source rows that match one target row to update the data in the target row.
  A. You can filter the rows to update based on information in related rows in other tables.
  C. You can collect information from related rows in other tables to be used in the source expressions in the SET clause.

• Question 377:

  Which of the following strategies can help reduce blocking and deadlocking by reducing shared locks? (Choose all that apply.)

  A. Add the READUNCOMMITTED table hint to queries.
  B. Use the READ COMMTTED SNAPSHOT option.
  C. Use the REPEATABLE READ isolation level.
  D. Use the SNAPSHOT isolation level.
  A. Add the READUNCOMMITTED table hint to queries.
  B. Use the READ COMMTTED SNAPSHOT option.
  D. Use the SNAPSHOT isolation level.

• Question 378:

  SIMULATION

  You have a view named Person.vAdditionalContactInfo that uses data from a table named Customers. The Customers table was created by running the following Transact-SQL statement:

  

  You need to create a query that returns the first and last names of a customer from the view. If the CustomerID column does not have a value defined, the following message must be added to the query result: “Customer ID Does Not Exist.” Construct the query using the following quidelines:

  1. Do not use aliases for column or table names.

  2. Display the columns in the same order as they are defined in the Customers table.

  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 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.

  Check the answer below

  1 SELECT FirstName, LastName, CustomerID = 2 CASE 3 WHEN CustomerID IS NULL THEN "Customer ID Does Not Exist" 4 ELSE CustomerID 5 END 6 FROM Person.vAdditionalContactInfo; 7

  To line 1 add: CustomerID = To line 2 add: CASE To line 3 add: IS NULL To line 5 add: END

  ---

  https://technet.microsoft.com/en-us/library/ms181765(v=sql.105).aspx https://docs.microsoft.com/en-us/sql/t-sql/queries/is-null-transact-sql

• Question 379:

  Which function can be used to rank documents based on proximity of words?

  A. CONTAINSTABLE()
  B. FREETEXTTABLE()
  C. SEMANTICKEYPHRASETABLE()
  D. SEMANTICSIMILARITYTABLE()
  E. SEMANTICSIMILARITYDETAILSTABLE()
  A. CONTAINSTABLE()

  ---

  A. Correct: You use the CONTAINSTABLE function to rank documents based on proximity of words.

  B. Incorrect: You use the FREETEXTTABLE function to rank documents based on containment of words.

  C. Incorrect: You use the SEMANTICKEYPHRASETABLE function to return key phrases associated with the full-text indexed column.

  D. Incorrect: You use the SEMANTICSIMILARITYTABLE function to retrieve documents scored by similarity to a specified document.

  E. Incorrect: You use the SEMANTICSIMILARITYDETAILSTABLE function to return key phrases that are common across two documents.

• Question 380:

  You work as a SQL Server 2012 database developer at ABC.com.

  You are developing a batch process that accepts an input parameter and allows the result set to be joined with a table.

  How would you accomplish this?

  A. You should make use of a stored procedure to perform the batch process.
  B. You should make use of a table-valued user-defined function.
  C. You should make use of the SET XACT_ABORT ON statement.
  D. You should make use of a scalar user-defined function.
  E. You should make use of a schema-bound user-defined function.
  B. You should make use of a table-valued user-defined function.

  ---

  Ref: h http://msdn.microsoft.com/en-us/library/ms191007.aspx