Microsoft 70-761 Online Practice Questions and Exam Preparation

Microsoft 70-761 Online Questions & Answers

• Question 101:

  Note: This question is part of a series of questions that use the same or similar answer choices. An answer choice may be correct for more than one question in the series. Each question is independent of the other questions in this series.

  Information and details provided in a question apply only to that question.

  You have a database that contains tables named Customer_CRMSystem and Customer_HRSystem. Both tables use the following structure:

  The tables include the following records:

  Customer_CRMSystem

  

  Customer_HRSystem

  

  Records that contain null values for CustomerCode can be uniquely identified by CustomerName.

  You need to create a list of all unique customers that appear in either table.

  Which Transact-SQL statement should you run?

  

  A. B. C. D. E. F. G.
  E

  ---

  UNION combines the results of two or more queries into a single result set that includes all the rows that belong to all queries in the union. The UNION operation is different from using joins that combine columns from two tables. Incorrect Answers:

  F: UNION ALL incorporates all rows into the results. This includes duplicates. If not specified, duplicate rows are removed. References: https://msdn.microsoft.com/en-us/library/ms180026.aspx

• Question 102:

  Note: This question is part of a series of questions that use the same or similar answer choices. An answer choice may be correct for more than one question in the series. Each question is independent of the other questions in this series. Information and details provided in a question apply only to that question.

  You create a table by running the following Transact-SQL statement:

  

  You need to develop a query that meets the following requirements:

  

  Output data by using a tree-like structure.

  

  Allow mixed content types.

  

  Use custom metadata attributes.

  Which Transact-SQL statement should you run?

  

  A. Option A
  B. Option B
  C. Option C
  D. Option D
  E. Option E
  F. Option F
  G. Option G
  H. Option H
  F. Option F

  ---

  In a FOR XML clause, you specify one of these modes: RAW, AUTO, EXPLICIT, and PATH.

  

  The EXPLICIT mode allows more control over the shape of the XML. You can mix attributes and elements at will in deciding the shape of the XML. It requires a specific format for the resulting rowset that is generated because of query execution. This row set format is then mapped into XML shape. The power of EXPLICIT mode is to mix attributes and elements at will, create wrappers and nested complex properties, create space-separated values (for example, OrderID attribute may have a list of order ID values), and mixed contents.

  

  The PATH mode together with the nested FOR XML query capability provides the flexibility of the EXPLICIT mode in a simpler manner.

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

• Question 103:

  A company's sales team is divided in two different regions, North and South. You create tables named SalesNorth and SalesSouth. The SalesNorth table stores sales data from the North region. The SalesSouth table stores sales data from the South region. Both tables use the following structure:

  

  You need to create a consolidated result set that includes all records from both tables.

  Which Transact-SQL statement should you run?

  A. SELECT SalesNorth.salesID, SalesNorth.customer, SalesNorth.amount, SalesSouth.SalesID, SalesSouth.customer, SalesSouth.amount FROM SalesNorth JOIN SalesSouth ON SalesNorth.salesID = SalesSouth.salesID
  B. SELECT SalesNorth.salesID, SalesNorth.customer, SalesNorth.amount, SalesSouth.salesID, SalesSouth.customer, SalesSouth.amount FROM SalesNorth LEFT JOIN SalesSouth ON SalesNorth.salesID=SalesSouth.salesID
  C. SELECT salesID, customer, amount FROM SalesNorth UNION ALL SELECT salesID, customer, amount FROM SalesSouth
  D. SELECT salesID, customer, amount FROM SalesNorth UNION SELECT salesID, customer, amount FROM SalesSouth
  C. SELECT salesID, customer, amount FROM SalesNorth UNION ALL SELECT salesID, customer, amount FROM SalesSouth

  ---

  References: https://docs.microsoft.com/en-us/sql/t-sql/queries/from-transact-sql?view=sql-server-2017

• Question 104:

  Note: This question is part of a series of questions that use the same or similar answer choices. An answer choice may be correct for more than one question in the series. Each question is independent of the other questions in this series. Information and details provided in a question apply only to that question.

  You create a table by running the following Transact-SQL statement:

  

  You need to return normalized data for all customers that were added in the year 2014. Which Transact-SQL statement should you run?

  

  B. C. D. E. F. G. H.
  G

  ---

  The following query searches for row versions for Employee row with EmployeeID = 1000 that were active at least for a portion of period between 1st January of 2014 and 1st January 2015 (including the upper boundary):

  SELECT * FROM Employee

  FOR SYSTEM_TIME

  BETWEEN '2014-01-01 00:00:00.0000000' AND '2015-01-01 00:00:00.0000000'

  WHERE EmployeeID = 1000ORDER BY ValidFrom;

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

• Question 105:

  SIMULATION

  You have a database that includes the following tables. All of the tables are in the Production schema.

  

  You need to create a query that returns a list of product names for all products in the Beverages category.

  Construct the query using the following guidelines:

  

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

  

  Use two-part column names.

  

  Do not surround object names with square brackets.

  

  Do not use implicit joins.

  

  Do not use variables.

  

  Use single quotes to surround literal 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.

  

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

  ---

  Correct Answer:

  1.

  SELECT p.productname

  2.

  FROM Production.Categories As c

  3.

  JOIN Production.Products As p ON ( c.categoryid = p.categoryid )

  4.

  WHERE c.categoryname = 'Beverages'

  References: https://docs.microsoft.com/en-us/sql/relational-databases/performance/joins?view=sql-server-2017

• Question 106:

  Note: This question is part of a series of questions that use the same or similar answer choices. An answer choice may be correct for more than one question in the series. Each question is independent of the other questionsin this series. Information and details provided in a question apply only to that question.

  You have a database for a banking system. The database has two tables named tblDepositAcct and tblLoanAcct that store deposit and loan accounts, respectively. Both tables include the following columns:

  

  You need to determine the total number of different customers who have at least one account. Which Transact-SQL statement should you run?

  

  A. Option A
  B. Option B
  C. Option C
  D. Option D
  E. Option E
  F. Option F
  G. Option G
  H. Option H
  A. Option A

• Question 107:

  You have a database that contains the following tables: Customer

  

  CustomerAudit

  

  Where the value of the CustomerID column equals 3, you need to update the value of the CreditLimit column to 1000 for the customer. You must ensure that the change to the record in the Customer table is recorded on the CustomerAudit table.

  Which Transact-SQL statement should you run?

  

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

  ---

  The OUTPUT Clause returns information from, or expressions based on, each row affected by an INSERT, UPDATE, DELETE, or MERGE statement. These results can be returned to the processing application for use in such things as confirmation messages, archiving, and other such application requirements. The results can also be inserted into a table or table variable. Additionally, you can capture the results of an OUTPUT clause in a nested INSERT, UPDATE, DELETE, or MERGE statement, and insert those results into a target table or view.

  Note: If the column modified by the .RITE clause is referenced in an OUTPUT clause, the complete value of the column, either the before image in deleted.column_name or the after image in inserted.column_name, is returned to the specified column in the tablevariable.

  Incorrect Answers:

  C: The deleted.Creditlimit should be inserted in the second column, the OldCreditLimit column, not the third column.

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

• Question 108:

  DRAG DROP

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

  

  You have the following Comma Separated Values (CSV) file:

  You need to load data from the CSV file into the Users table while meeting the following requirements:

  If a field value is not provided in the file, insert a NULL value for the corresponding column

  Load all records into the table with the correct UserId from the file 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:

  Select and Place:

  

  

  ---

  Reference: https://docs.microsoft.com/en-us/sql/t-sql/functions/openrowset-transact-sql?view=sql-server-2017

• Question 109:

  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 tracks orders and deliveries for customers in North America. The database contains the following tables:

  Sales.Customers

  

  Application.Cities

  

  Sales.CustomerCategories

  

  The company's development team is designing a customer directory application. The application must list customers by the area code of their phone number. The area code is defined as the first three characters of the phone number.

  The main page of the application will be based on an indexed view that contains the area and phone number for all customers.

  You need to return the area code from the PhoneNumber field.

  Solution: You run the following Transact-SQL statement:

  

  Does the solution meet the goal?

  A. Yes
  B. No
  B. No

  ---

  We need SELECT TOP 1 @areacode =.. to ensure that only one value is returned.

• Question 110:

  HOTSPOT

  You need to develop a Transact-SQL statement that meets the following requirements:

  The statement must return a custom error when there are problems updating a table.

  The error number must be value50555.

  The error severity level must be14.

  A Microsoft SQL Server alert must be triggered when the error condition occurs.

  Which Transact-SQL segment should you use for each requirement? To answer, select the appropriate Transact-SQL segments in the answer area.

  Hot Area:

  

  

  ---

  Box 1: TRY...CATCH

  The TRY...CATCH Transact-SQL construct implements error handling for Transact-SQL that is similar to the exception handling in the Microsoft Visual C# and Microsoft Visual C++ languages. A group of Transact-SQL statements can be enclosed in a TRY block. If an error occurs in the TRY block, control is passed to another group of statements that is enclosed in a CATCH block.

  Box 2: RAISERROR(50555, 14, 1 'The update failed.") WITH LOG

  We must use RAISERROR to be able to specify the required severity level of 14, and we should also use the LOG option, which Logs the error in the error log and the application log for the instance of the Microsoft SQL Server Database Engine, as this enable a MS MS SQL SERVER alert to be triggered.

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

  Incorrect Answers:

  Not THROW: THROW does not have a severity parameter.

  References:

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

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