Microsoft 70-461 Online Practice Questions and Exam Preparation

Microsoft 70-461 Online Questions & Answers

• Question 401:

  You need to extract data from a table in a SQL Server 2012 database. What connection manager types can you use? (Choose all that apply.)

  A. An ODBC connection manager
  B. An OLE DB connection manager
  C. A File connection manager
  D. An ADO.NET connection manager
  A. An ODBC connection manager
  B. An OLE DB connection manager
  D. An ADO.NET connection manager

• Question 402:

  You work as a database developer at ABC.com. ABC has an in-house application named ABCApp3 that runs a Transact-SQL query against a SQL Server 2012 database named SalesDB.

  ABC.com users report that ABCApp3 is functioning sluggishly. You discover that concurrent updates are causing blockages on the SalesDB database.

  How would you ensure that the query to use the original data when updates occur?

  A. You should have the query run in the REPEATABLE READ ISOLATION LEVEL.
  B. You should have the query run in the SERIALIZABLE ISOLATION LEVEL.
  C. You should have the query run in the READCOMMITTED ISOLATION LEVEL.
  D. You should have the query run in the SNAPSHOT ISOLATION LEVEL.
  E. You should have the query run in the READPAST ISOLATION LEVEL.
  D. You should have the query run in the SNAPSHOT ISOLATION LEVEL.

• Question 403:

  Your application contains a stored procedure for each country. Each stored procedure accepts an employee identification number through the @EmplD 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
  E. A user-defined function

  ---

  SQL Server user-defined functions are routines that accept parameters, perform an action, such as a complex calculation, and return the result of that action as a value. The return value can either be a single scalar value or a result set.

• Question 404:

  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.

  

  Check the answer below

  SELECT CatID, CatName, ProductID, ProdName, UnitPrice, RANK (ORDER BY UnitPrice DESC) OVER () AS PriceRank FROM Sales.ProductCatalog ORDER BY PriceRank

• Question 405:

  DRAG DROP

  You are developing a Microsoft Azure SQL Database instance to support a company's payroll system. The database contains a table named Employee defined by the following Transact-SQL statement:

  

  The company defines five employee levels. Each employee's level is stored in the Level column. Levels 1,2, and 3 are used for non-executive employees. Levels 4 and 5 are used for executives. Each of the non-executive levels has a defined

  range that constrains the salary of employees at that level.

  You need to create a view that allows non-executive Employees entries to be updated based on the following rules:

  Only non-executive employees can be viewed or updated.

  Employees can be promoted to a non-executive level.

  Employees cannot be promoted to an executive level.

  If an employees' salary changes, the new salary must adhere to the company-defined range for that level.

  Which four Transact-SQL segments should you use to develop the solution? To answer, move the appropriate Transact-SQL segments from the list of Transact-SQL segments to the answer area and arrange them in the correct order.

  Select and Place:

  

  

• Question 406:

  DRAG DROP

  You develop a database application for Microsoft SQL Server and Microsoft Azure SQL Database.

  You create a table named Purchasing.vVendorWithAddresses as shown in the following table.

  

  You write the following Transact-SQL (Line numbers are included for reference only.) 01 CREATE PROCEDURE

  

  You need to add Transact-SQL statements at line 08 to ensure that GetVendorInStateNeighbors returns the names of vendors that are located in all states where the vendor specified in the @vendorname parameter has a location. Which three Transact-SQL segments should you use to develop the solution? To answer, move the appropriate Transact-SQL segments from the list of Transact-SQL segments to the answer area and arrange them in the correct order.

  Select and Place:

  

  

  ---

  The IN statement determines whether a specified value matches any value in a subquery or a list.

  Incorrect:

  The EXISTS command specifies a subquery to test for the existence of rows.

  References: https://docs.microsoft.com/en-us/sql/t-sql/language-elements/in-transact-sql?view=sql-server-2017

• Question 407:

  SQL Server 2012 uses the standard four-part naming convention when referencing database objects. Which of the following is the correct example of the four-part naming?

  A. server_name.schema_name.database_name.object_name
  B. schema_name.object_name.server_name.database_name
  C. object_name.server_name.schema_name.database_name
  D. server_name.database_name.schema_name.object_name
  D. server_name.database_name.schema_name.object_name

  ---

  You can use variations of the four-part naming such as: database_name.[schema_name].object_name schema_name.object_name object_name

• Question 408:

  You develop a Microsoft SQL Server 2012 database that contains a heap named OrdersHistoncal.

  You write the following Transact-SQL query:

  INSERT INTO OrdersHistorical SELECT * FROM CompletedOrders

  You need to optimize transaction logging and locking for the statement. Which table hint should you use?

  A. HOLDLOCK
  B. ROWLOCK
  C. XLOCK
  D. UPDLOCK
  E. TABLOCK
  E. TABLOCK

  ---

  http://technet.microsoft.com/en-us/library/ms189857.aspx http://msdn.microsoft.com/en-us/library/ms187373.aspx

• Question 409:

  Which of the following are true about table-valued UDFs?

  A. Table-valued UDFs can return scalar values or tables.
  B. Table-valued UDFs always involve multiple T-SQL statements.
  C. Table-valued UDFs can be invoked in a SELECT list or a WHERE clause.
  D. Table-valued UDFs can be invoked in the FROM clause of a SELECT statement.
  D. Table-valued UDFs can be invoked in the FROM clause of a SELECT statement.

  ---

  The FROM clause requires a table and table-valued UDFs return tables.

• Question 410:

  You use a Microsoft SQL Server database that contains a table. The table has records of customer orders.

  Your company has three divisions that have the following names:

  East

  Central West

  You need to create a query that displays the following information: The number of sales for each product (ProductName) grouped by the division (Division) that sold the product A column for each division

  Which Transact-SQL query should you use?

  

  

  A. Option A
  B. Option B
  C. Option C
  D. Option D
  D. Option D

  ---

  You can use the PIVOT and UNPIVOT relational operators to change a table-valued expression into another table. PIVOT rotates a table-valued expression by turning the unique values from one column in the expression into multiple columns in the output, and performs aggregations where they are required on any remaining column values that are wanted in the final output. UNPIVOT performs the opposite operation to PIVOT by rotating columns of a table-valued expression into column values.

  References: https://technet.microsoft.com/en-us/library/ms177410(v=sql.105).aspx