Microsoft 70-461 Online Practice Questions and Exam Preparation

Microsoft 70-461 Online Questions & Answers

• Question 101:

  You have a view that was created by using the following code:

  

  You need to create an inline table-valued function named Sales.fn_OrdersByTerritory that returns sales information from a specified SalesTerritoryID. Sales.fn_OrdersByTerritory must meet the following requirements:

  1. Use one-part names to reference columns.

  2. Return all the columns in the OrdersByTerritory View. The function should return the same columns as they exist in the OrdersByTerritory view and in the same order.

  3. Declare the input variable as @T.

  4. Use SalesTerritoryID as an integer.

  Part of the correct T-SQL statement has been provided in the answer area. Provide the complete code.

  

  Key Words

  

  Check the answer below

  CREATE FUNCTION Sales.fn_OrdersByTerritory (@T integer) RETURNS TABLE AS RETURN (SELECT OrderID, OrderDate, SalesTerritoryID, TotalDue FROM Sales.OrdersByTerritory WHERE SalesTerritoryID = @T)

• Question 102:

  DRAG DROP

  You are a developer for a Microsoft SQL Server database that contains a complaint table created by running the following Transact-SQL statement:

  

  Which Transact-SQL statement or statements should you run? To answer, drag the appropriate Transact-SQL statements to the correct functions. Each Transact-SQL statement may be used once, more than once, or not at all. You may need to drag the split bar between panes or scroll to view content.

  Select and Place:

  

  

  ---

  Reference: https://social.technet.microsoft.com/wiki/contents/articles/17948.t-sql-right-left-substring-and-charindex-functions.aspx https://docs.microsoft.com/en-us/sql/t-sql/language-elements/if-else-transact-sql?view=sql-server-ver15

• Question 103:

  How can you update a table, setting a column to the result of a window function?

  A. By using an UPDATE based on a join
  B. By using an UPDATE based on a table expression
  C. By using an UPDATE with a variable
  D. The task cannot be achieved.
  B. By using an UPDATE based on a table expression

  ---

  With an UPDATE based on table expressions, you can invoke a window function in the inner query's SELECT list. You can then refer to the alias you assigned to the result column in the outer UPDATE statement's SET clause.

• Question 104:

  DRAG DROP

  A database contains tables as shown in the exhibit. (Click the Exhibit button.)

  Customer who are inactive are moved from the Customers table to the InactiveCustomers table. Any orders for inactive customers are removed from the Orders table.

  You write the following SELECT statement to return all the inactive customers:

  SELECT CustomerId FROM InactiveCustomers

  You need to extend the SELECT statement to include customers who do not have any orders.

  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.

  Exhibit

  

  Select and Place:

  

  

  ---

  EXCEPT returns distinct rows from the left input query that aren't output by the right input query.

  References: https://docs.microsoft.com/en-us/sql/t-sql/language-elements/set-operators-except-and-intersect-transact-sql?view=sql-server-2017

• Question 105:

  What are the actions of the optimization phase of query execution? (Choose all that apply.)

  A. Generation of the algebrized tree
  B. Generation of candidate plans
  C. Selection of the best candidate plan
  D. Caching the plan
  E. Query execution
  B. Generation of candidate plans
  C. Selection of the best candidate plan

• Question 106:

  DRAG DROP

  A Microsoft SQL Server database contains a production table named Loan and a data staging table named LoanStaging. LoanStaging is periodical and populated with data that needs to be imported into Loan. The tables were created running the following Transact-SQL statements: The following conversions need to occur:

  

  1. The BankID in the LoanStaging table must be converted to the name of the bank based on the following translation:

  1='BBV Bank',

  2='First Bank',

  and 3='Best Bank'.

  2. The LoanDay, LoanMonth, and LoanYear data in the LoanStaging table are used to calculate the value for the LoanStartDate in the Loan table. For successful imports, you need to ensure that:

  1. existing records in the Loan table are updated,

  2. records that do not exist in the Loan table are inserted, and

  3. the LoanGuid value never changes for a particular record, as this field is used to determine whether a record needs to be updated or inserted.

  How should you complete the Transact-SQL statement? To answer, drag the appropriate Transact-SQL segment to the correct locations. Answer choices may be used one more than once, or not at all. You may need to drag the split bar between panes or scroll to view content.

  Select and Place:

  

  

  ---

  Reference: https://docs.microsoft.com/en-us/sql/t-sql/statements/merge-transact-sql?view=sql-server-ver15

• Question 107:

  What aggregation algorithms does SQL Server use? (Choose all that apply.)

  A. Merge aggregation
  B. Stream aggregation
  C. Hash aggregation
  D. Nested loops aggregation
  B. Stream aggregation
  C. Hash aggregation

• Question 108:

  DRAG DROP

  You develop a Microsoft SQL Server database for an educational institution. The database contains tables named ExamEvaluation and ProjectEvaluation, which are used to track student scores. The tables are created by using the following code:

  

  Students are evaluated based on the following rules:

  1. Each student will complete a maximum of one exam and one project.

  2. A student must complete at least one exam.

  3. A single exam consists of multiple sections, where the student is evaluated based on the average score of all sections.

  4. A single project consists of Section A and Section B, where the student is evaluated by adding 60 percent of Section A and 40 percent of Section B.

  5. At any given time, a student may have completed only an exam, or both an exam and a project.

  You want to create a summary report of student evaluations.

  You need to write a query that will return evaluation results for students who have completed any form of evaluation, grouped by the year of undertaling. A sample result set is shown in the following table.

  

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

  

  

  ---

  Box 1: SELECT..

  The outer select statement

  Box 2: (SELECT ..AVG(SectionScore)

  Need the average score: A single exam consists of multiple sections, where the student is evaluated based on the average score of all sections.

  Box 3: LEFT OUTER JOIN

  There might be no finished Project

  Box 4: ON..

  Box 5: (SELECT ..SUM

  Need the SUM function to handle addition of NULL values.

  References:

  https://docs.microsoft.com/en-us/sql/t-sql/functions/sum-transact-sql

• Question 109:

  What is the difference between the COUNT(*) aggregate function and the COUNT() general set function?

  A. COUNT(*) counts rows; COUNT() counts rows where is not NULL.
  B. COUNT(*) counts columns; COUNT() counts rows.
  C. COUNT(*) returns a BIGINT; COUNT() returns an INT.
  D. There's no difference between the functions.
  A. COUNT(*) counts rows; COUNT() counts rows where is not NULL.

• Question 110:

  DRAG DROP

  You use Microsoft SQL Server Management Studio to develop a Microsoft Azure SQL Database instance that will support a purchase order application. You write the following query:

  

  A partial view of the query results can be seen in the following table.

  

  You must generate a summary report that displays the total number of purchase orders received by each vendor for the following regions: North, East, South, West, and Central. You need to write a query that returns the results in the format shown in the following table.

  

  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:

  

  

  ---

  Explanation:

  Step 1: SELECT Vendorname, [North], [East],..FROM SourceTable

  Step 2: WITH SourceTable AS..

  Step 3: UNPIVOT ..

  UNPIVOT carries out almost the reverse operation of PIVOT, by rotating columns into rows.

  Reference:

  https://docs.microsoft.com/en-us/sql/t-sql/queries/from-using-pivot-and-unpivot