Microsoft 70-761 Online Practice Questions and Exam Preparation

Microsoft 70-761 Online Questions & Answers

• Question 41:

  Note: This question is part of a series of questions that present the same scenario. Each question in the series contains a unique solution that might meet the stated goals. Some question sets might have more than one correct solution, while

  others might not have a correct solution.

  After you answer a question in this section. You will NOT be able to return to it. As a result, these questions will not appear in the review screen.

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

  

  You need to create a Transact-SQL query that returns the following information:

  

  the customer number

  

  the customer contact name

  

  the date the order was placed, with a name of DateofOrder

  

  a column named Salesperson, formatted with the employee first name, a space, and the employee last name

  

  orders for customers where the employee identifier equals 4

  The output must be sorted by order date, with the newest orders first.

  The solution must return only the most recent order for each customer.

  Solution: You run the following Transact-SQL statement:

  

  Does the solution meet the goal?

  A. Yes
  B. No
  A. Yes

  ---

  The MAX(orderdate) in the SELECT statement makes sure we return only the most recent order.

  AWHERE o.empiD =4 clause is correctly used.

  GROUP BY is also required.

• Question 42:

  You are building a stored procedure that will update data in a table named Table1 by using a complex query as the data source.

  You need to ensure that the SELECT statement in the stored procedure meets the following requirements:

  

  Data being processed must be usable in several statements in the stored procedure.

  

  Data being processed must contain statistics.

  What should you do?

  A. Update Table1 by using a common table expression (CTE).
  B. Insert the data into a temporary table, and then update Table1 from the temporary table.
  C. Place the SELECT statement in a derived table, and then update Table1 by using a JOIN to the derived table.
  D. Insert the data into a table variable, and then update Table1 from the table variable.
  B. Insert the data into a temporary table, and then update Table1 from the temporary table.

  ---

  Temp Tables... Are real materialized tables that exist in tempdb Have dedicated stats generated by the engine Can be indexed Can have constraints Persist for the life of the current CONNECTION Can be referenced by other queries or subproce Incorrect Answers:

  A: CTEs do not have dedicated stats. They rely on stats on the underlying objects

  C: Unlike a derived table, a CTE can be self-referencing and can be referenced multiple times in the same query.

  References: https://technet.microsoft.com/en-us/library/ms190766(v=sql.105).aspx https://dba.stackexchange.com/questions/13112/whats-the-difference-between-a-cte-and-a-temp-table

• Question 43:

  Note: This question is part of a series of questions that present the same scenario. Each question in the series contains a unique solution that might meet the stated goals. Some question sets might have more than one correct solution, while

  others might not have a correct solution.

  After you answer a question in this section. You will NOT be able to return to it. As a result, these questions will not appear in the review screen.

  You have a database that contains a single table named tblVehicleRegistration. The table is defined as follows:

  

  You run the following query:

  

  The query output window displays the following error message: "Conversion failed when converting the varchar value `AB012' to data type int."

  You need to resolve the error.

  Solution: You modify the Transact-SQL statement as follows:

  

  Does the solution meet the goal?

  A. Yes
  B. No
  A. Yes

• Question 44:

  You have a database named DB1 that contains a temporal table named Sales.Customers.

  You need to create a query that returns the credit limit that was available to each customer in DB1 at the beginning of 2017.

  Which query should you execute?

  

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

  ---

  AS OF: Returns a table with a rows containing the values that were actual (current) at the specified point in time in the past.

  Incorrect Answers:

  A, B: CONTAINED IN has two parameters: CONTAINED IN ( , )

  References: https://docs.microsoft.com/en-us/sql/relational-databases/tables/querying-data-in-a-system-versioned-temporal-table

• Question 45:

  You run the following Transact-SQL statement:

  

  You use the table to store data about training courses: when they finished the location, and the number of participants in the courses.

  You need to display a result set that shows aggregates for all possible combinations of the number of participants.

  Which Transact-SQL statement should you run?

  A. SELECT CourseID, CourseDate, SUM(NumParticipants) FROM CourseParticipants GROUP BY CourseID, CourseDate
  B. SELECT CourseID, CourseDate, SUM(DISTINCT NumParticipants) FROM CourseParticipants GROUP BY CourseID, CourseDate
  C. SELECT CourseID, CourseDate, SUM(NumParticipants) FROM CourseParticipants GROUP BY CourseID, CourseDate WITH CUBE
  D. SELECT CourseID, CourseDate, SUM(DISTINCT NumParticipants) FROM CourseParticipants GROUP BY CourseID, CourseDate WITH ROLLUP
  C. SELECT CourseID, CourseDate, SUM(NumParticipants) FROM CourseParticipants GROUP BY CourseID, CourseDate WITH CUBE

  ---

  The WITH CUBE clause causes the query to compute all possible totals

  References: https://blogs.msdn.microsoft.com/craigfr/2007/09/27/aggregation-with-cube/

• Question 46:

  A company produces and ships concrete blocks. You store information about factory and shipping centers in tables that were created by running the following Transact-SQL statements:

  

  You must create a report that shows the regions that have a factory but do not have a shipping center.

  You need to create the query for the report.

  Which two Transact-SQL statements can you use? Each correct answer presents a complete solution.

  NOTE: Each correct selection is worth one point.

  

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

• Question 47:

  DRAG DROP

  You need to create a stored procedure that meets the following requirements:

  Produces a warning if the credit limit parameter is greater than 7,000

  Propagates all unexpected errors to the calling process How should you complete the Transact-SQL statement? To answer, drag the appropriate Transact-SQL segments to the correct locations. Each Transact-SQL segments 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:

  

  

  ---

  Box 1: THROW 51000, 'Warning: Credit limit isover 7,000!",1

  THROW raises an exception and transfers execution to a CATCH block of a TRY...CATCH construct in SQL Server.

  THROW syntax:

  THROW [ { error_number | @local_variable },

  { message | @local_variable },

  { state | @local_variable } ]

  [ ; ]

  Box2: RAISERROR (@ErrorMessage, 16,1)

  RAISERROR generates an error message and initiates error processing for the session. RAISERROR can either reference a user-defined message stored in the sys.messages catalog view or build a message dynamically. The message is

  returned as a server error message to the calling application or to an associated CATCH block of a TRY...CATCH construct. New applications should use THROW instead. Severity levels from 0 through 18 can be specified by any user.

  Severity levels from 19through 25 can only be specified by members of the sysadmin fixed server role or users with ALTER TRACE permissions. For severity levels from 19 through 25, the WITH LOG option is required.

  On Severity level 16. Using THROW to raise an exception

  The following example shows how to use the THROW statement to raise an exception.

  Transact-SQL

  THROW 51000, 'The record does not exist.', 1;

  Here is the result set.

  Msg 51000, Level 16, State 1, Line 1

  The record does not exist.

  Note: RAISERROR syntax:

  RAISERROR( { msg_id | msg_str | @local_variable }

  { ,severity ,state }

  [ ,argument [ ,...n ] ] )

  [ WITH option [ ,...n ] ]

  Note: The ERROR_MESSAGE function returns the message text of the error that caused the CATCH block of a TRY...CATCH construct to be run.

  References:

  https://msdn.microsoft.com/en-us/library/ms178592.aspx

  https://msdn.microsoft.com/en-us/library/ms190358.aspx

  https://msdn.microsoft.com/en-us/library/ee677615.aspx

• Question 48:

  SIMULATION

  You have a database that contains a table named Products in the Sales schema. The table was created by running the following Transact-SQL statement:

  

  The table includes the data shown below:

  

  You are developing a report that displays the following values and column headers in the order listed below:

  

  average price of a product named Average

  

  the smallest number of products in stock named LowestNumber

  

  the highest product price named HighestPrice

  You need to write a query to return the results for the report. The query must meet the following requirements:

  

  Use built-in, aggregate and mathematical functions.

  

  Use two-part names and tables.

  

  Use the table alias to qualify column names.

  

  Define the alias for all fields by using the AS keyword.

  

  Use the first letter of the table name as the table alias.

  

  Do not use the ROW_NUMBER function.

  

  Do not surround object names with square brackets.

  

  Do not use variables.

  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.

  

  1.

  SELECT

  2.

  FROM Sales.Products AS P

  Use the Check Syntax button to verify your work. Any syntax or spelling errors will be reported by line and character position. You may check syntax as many times as needed.

  A. Check the answer below
  A. Check the answer below

  ---

  Correct Answer:

  1.

  SELECT avg(P.ProductPrice) AS Average, min(P.ProductsInStock) AS LowestNumber, max(P.ProductPrice) AS HighestPrice

  2.

  FROM Sales.Products AS P Make the additions to line 1.

  References: https://www.mssqltips.com/sqlservertip/4424/max-min-and-avg-sql-server-functions/

• Question 49:

  Note: This question is part of a series of questions that present the same scenario. Each question in the series contains a unique solution that might meet the stated goals. Some question sets might have more than onecorrect solution, while others might not have a correct solution.

  After you answer a question in this section. You will NOT be able to return to it. As a result, these questions will not appear in the review screen.

  You have a database that tracks orders and deliveries for customers in North America. The database contains the following tables:

  Sales.Customers

  

  Application.Cities

  

  Sales.CustomerCategories

  

  Your company is developing a new social application that connects customers to each other based on the distance between their delivery locations.

  You need to write a query that returns the nearest customer.

  Solution: You run the following Transact-SQL statement:

  

  The variable @custID is set to a valid customer. Does the solution meet the goal?

  A. Yes
  B. No
  B. No

  ---

  It is better to use add a WITH ... AS statement in this solution.

  References:

  https://blogs.msdn.microsoft.com/isaac/2008/10/22/nearest-neighbors/

  https://docs.microsoft.com/en-us/sql/t-sql/spatial-geometry/shortestlineto-geometry-data-type

• Question 50:

  HOTSPOT

  Note: This question is part of a series of questions that use the same scenario. For your convenience, the scenario is repeated in each question. Each question presents a different goal and answer choices, but the text of the scenario is exactly the same in each question in this series.

  Start of repeated scenario

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

  

  You review the Employee table and make the following observations:

  Every record has a value in the ManagerID except for the Chief Executive Officer (CEO).

  The FirstName and MiddleName columns contain null values for some records.

  The valid values for the Title column are Sales Representative manager, and CEO.

  You review the SalesSummary table and make the following observations:

  The ProductCode column contains two parts: The first five digits represent a product code, and the last seven digits represent the unit price. The unit price uses the following pattern: ####.##.

  You observe that for many records, the unit price portion of the ProductCode column contains values.

  The RegionCode column contains NULL for some records.

  Sales data is only recorded for sales representatives.

  You are developing a series of reports and procedures to support the business. Details for each report or procedure follow. Sales Summary report: This report aggregates data by year and quarter. The report must resemble the following table.

  

  Sales Manager report: This report lists each sales manager and the total sales amount for all employees that report to the sales manager.

  Sales by Region report: This report lists the total sales amount by employee and by region. The report must include the following columns: EmployeeCode, MiddleName, LastName, RegionCode, and SalesAmount. If MiddleName is NULL,

  FirstName must be displayed. If both FirstName and MiddleName have null values, the world Unknown must be displayed/ If RegionCode is NULL, the word Unknown must be displayed. Report1: This report joins data from SalesSummary with the Employee table and other tables. You plan to create an object to support Report1. The object has the following requirements:

  be joinable with the SELECT statement that supplies data for the report

  can be used multiple times with the SELECT statement for the report

  be usable only with the SELECT statement for the report

  not be saved as a permanent object

  Report2: This report joins data from SalesSummary with the Employee table and other tables. You plan to create an object to support Report1. The object has the following requirements:

  be joinable with the SELECT statement that supplies data for the report

  can be used multiple times for this report and other reports

  accept parameters

  be saved as a permanent object

  Sales Hierarchy report: This report aggregates rows, creates subtotal rows, and super-aggregates rows over the SalesAmount column in a single result-set. The report uses SaleYear, SaleQuarter, and SaleMonth as a hierarchy. The result set must not contain a grand total or cross-tabulation aggregate rows.

  Current Price Stored Procedure: This stored procedure must return the unit price for a product when a product code is supplied. The unit price must include a dollar sign at the beginning. In addition, the unit price must contain a comma every three digits to the left of the decimal point, and must display two digits to the left of the decimal point. The stored procedure must not throw errors, even if the product code contains invalid data.

  End of Repeated Scenario

  You need to create the query for the Sales by Region report.

  Which function should you apply to each column? To answer, select the appropriate options in the answer area.

  Hot Area:

  

  

  ---

  Box 1: COALESCE

  COALESCE evaluates the arguments in order and returns the current value of the first expression that initially does not evaluate to NULL.

  If MiddleName is NULL, FirstName must be displayed. If both FirstName and MiddleName have null values, the world Unknown must be displayed.

  The following example shows how COALESCE selects the data from the first column that has a nonnull value.

  SELECT Name, Class, Color, ProductNumber, COALESCE(Class, Color, ProductNumber) AS FirstNotNull

  FROM Production.Product;

  Not NULLIF: NULLIF returns the first expression if the two expressions are not equal. If the expressions are equal, NULLIF returns a null value of the type of the first expression.

  Box 2: COALESCE

  If RegionCodeis NULL, the word Unknown must be displayed.

  References:https://docs.microsoft.com/en-us/sql/t-sql/language-elements/coalesce-transact-sql