Microsoft 70-761 Online Practice Questions and Exam Preparation

Microsoft 70-761 Online Questions & Answers

• Question 111:

  You have a database that tracks customer complaints.

  The database contains a table named Complaints that includes the following columns:

  

  You need to create a query that lists complaints about defective products. The report must include complaints where the exact phrase "defective product" occurs, as well as complaints where similar phrases occur.

  Which Transact-SQL statement should you run?

  A. SELECT ComplaintID, ComplaintTranscript FROM Complaints WHERE CONTAINS(CustomerTranscript, 'defective') AND CONTAINS(CustomerTranscript, 'product')
  B. SELECT ComplaintID, CustomerTranscript FROM Complaints WHERE SOUNDEX('defective') = SOUNDEX('product')
  C. SELECT ComplaintID, CustomerTranscript FROM Complaints WHERE FREETEXT(CustomerTranscript, 'defective product')
  D. SELECT ComplaintID, Customer Transcript FROM Complaints WHERE CustomerTranscript like '%defective product%'
  A. SELECT ComplaintID, ComplaintTranscript FROM Complaints WHERE CONTAINS(CustomerTranscript, 'defective') AND CONTAINS(CustomerTranscript, 'product')

  ---

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

• Question 112:

  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

  

  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:

  SELECT TOP 1 B.CustomerID, A.DeliveryLocation.STDistance(B.DeliveryLocation) AS Dist

  FROM Sales.Customers AS A CROSS JOIN Sales.Customers AS B WHERE A.CustomerID = @custID AND A.CustomerID <> B.CustomerID ORDER BY Dist

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

  A. Yes
  B. No
  B. No

• Question 113:

  You have a project management application. The application uses a Microsoft SQL Server database to store data. You are developing a software bug tracking add-on for the application.

  The add-on must meet the following requirements:

  

  Allow case sensitive searches for product.

  

  Filter search results based on exact text in the description.

  

  Support multibyte Unicode characters.

  You run the following Transact-SQL statement:

  

  Users connect to an instance of the bug tracking application that is hosted in New York City. Users in Seattle must be able to display the local date and time for any bugs that they create.

  You need to ensure that the DateCreated column displays correctly.

  Which Transact-SQL statement should you run?

  A. SELECT Id,Product, DateCreated AT TIME ZONE 'Pacific Standard Time' FROM Bug
  B. SELECT Id,Product, DATEADD(hh, -8, DateCreated) FROM Bug
  C. SELECT Id,Product, TODATETIMEOFFSET(DateCreated, -8) FROM Bug
  D. SELECT Id,Product, CAST(DateCreated AS DATETIMEOFFSET) FROM Bug
  C. SELECT Id,Product, TODATETIMEOFFSET(DateCreated, -8) FROM Bug

  ---

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

• Question 114:

  You need to create a database object that meets the following requirements:

  

  accepts a product identified as input

  

  calculates the total quantity of a specific product, including quantity on hand and quantity on order

  

  caches and reuses execution plan

  

  returns a value

  

  can be called from within a SELECT statement

  

  can be used in a JOIN clause What should you create?

  A. an extended stored procedure
  B. a user-defined scalar function
  C. a user-defined stored procedure that has an OUTPUT parameter
  D. a temporary table that has a columnstore index
  B. a user-defined scalar function

  ---

  User-defined scalar functions are execution plans that accept parameters, perform an action such as a complex calculation, and returns the result of that action as a value. The return value can either be a single scalar value or a result set.

  Furthermore the execution plan is cached and reusable.

  User-defined scalar functions can also be called from within a SELECT statement and can be used in a JOIN clause.

  Incorrect Answers:

  A: Using extended stored procedures is not recommended as they has been deprecated. CLR Integration should be used instead of extended stored procedures.

  C: Stored procedures cannot be used in a SELECT statement or in a JOIN clause.

  D: A temporary table is a result set and not a value.

  References:

  https://www.c-sharpcorner.com/UploadFile/996353/difference-between-stored-procedure-and-user-defined-functio/

• Question 115:

  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 contain the following columns:

  

  You need to determine the total number of customers who have only deposit accounts. 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

  ---

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

• Question 116:

  SIMULATION

  You have a database that contains the following tables.

  

  You need to create a query that lists all complaints from the Complaints table, and the name of the person handling the complaints if a person is assigned. The ComplaintID must be displayed first, followed by the person name. Construct the query using the following guidelines:

  

  Use two-part column names.

  

  Use one-part table names.

  

  Do not use aliases for column names or table names.

  

  Do not use Transact-SQL functions.

  

  Do not use implicit joins.

  

  Do not surround object names with square brackets.

  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.

  

  Use the Check Syntax button to verify your work. Any syntax or spelling errors will be reported by line and character position.

  Correct Answer. explanation
  explanation

  ---

  Please see explanation

  

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

• Question 117:

  HOTSPOT

  You run the following Transact-SQL statement:

  

  You need to ensure that you can insert data into the table.

  What are the characteristics of the data? To answer, select the appropriate options in the answer area.

  Hot Area:

  

  

  ---

  Box 1: custid

  IDENTITY indicates that the new column is an identity column. When a new row is added to the table, the Database Engine provides a unique, incremental value for the column. Identity columns are typically used with PRIMARY KEY

  constraints to serve as the unique row identifier for the table.

  Box2: postalcode

  postalcode is declared as NOT NULL, which means that a value must be inserted.

  Box 3: region

  Fax is also a correct answer. Both these two columns are declared as NULL, which means that data entry is optional.

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

• Question 118:

  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 are building a stored procedure that will be used by hundreds of users concurrently.

  You need to store rows that will be processed later by the stored procedure. The object that stores the rows must meet the following requirements:

  

  Be indexable

  

  Contain up-to-date statistics

  

  Be able to scale between 10 and 100,000 rows

  The solution must prevent users from accessing one another's data.

  Solution: You create a local temporary table in the stored procedure.

  Does this meet the goal?

  A. Yes
  B. No
  B. No

• Question 119:

  You are developing a mobile app to manage meetups. The app allows for users to view the 25 closest people with similar interests. You have a table that contains records for approximately two million people. You create the table by running the following Transact-SQL statement:

  

  You create the following table valued function to generate lists of people:

  

  You need to build a report that shows meetings with at least two people only. What should you use?

  A. OUTER APPLY
  B. CROSS APPLY
  C. PIVOT
  D. LEFT OUTER JOIN
  B. CROSS APPLY

  ---

  References: https://www.sqlshack.com/the-difference-between-cross-apply-and-outer-apply-in-sql-server/

• Question 120:

  HOTSPOT

  You have a database that contains the following tables: tblRoles, tblUsers, and tblUsersInRoles.

  The table tblRoles is defined as follows.

  

  You have a function named ufnGetRoleActiveUsers that was created by running the following Transact-SQL statement:

  

  You need to list all roles and their corresponding active users. The query must return the RoleId, RoleName, and UserName columns. If a role has no active users, a NULL value should be returned as the UserName for that role. How should you complete the Transact-SQL statement? To answer, select the appropriate Transact-SQL segments in the answer area.

  Hot Area: