Microsoft 70-761 Online Practice Questions and Exam Preparation

Microsoft 70-761 Online Questions & Answers

• Question 121:

  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 display customers who appear in both tables and have a proper CustomerCode.

  Which Transact-SQL statement should you run?

  

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

  ---

  When there are null values in the columns of the tables being joined, the null values do not match each other. The presence of null values in a column from one of the tables being joined can be returned only by using an outer join (unless the WHERE clause excludes null values).

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

• Question 122:

  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
  B. Option B

• Question 123:

  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 table named Products that stores information about products your company sells. The table has a column named ListPrice that stores retail pricing information for products.

  Some products are used only internally by the company. Records for these products are maintained in the Products table for inventory purposes. The price for each of these products is $0.00. Customers are not permitted to order these

  products.

  You need to increase the list price for products that cost less than $100 by 10 percent. You must only increase pricing for products that customers are permitted to order.

  Solution: You run the following Transact-SQL statement:

  

  Does the solution meet the goal?

  A. Yes
  B. No
  B. No

  ---

  Mathematical equation will only return 10 % of the value.

• Question 124:

  HOTSPOT

  You must create a report that returns course identifiers and the average evaluation score for each course. The result set must include only one score for each employee for each course.

  How should you complete the Transact-SQL statement? To answer, select the appropriate Transact-SQL segments in the answer area.

  NOTE: Each correct selection is worth one point.

  Hot Area:

  

  You are developing a training management application. You run the following Transact-SQL statement:

  

  

• Question 125:

  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 are developing a report that displays customer information. The report must contain a grand total column.

  You need to write a query that returns the data for the report.

  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
  E. Option E

  ---

  Calculate aggregate column through AVG function and GROUP BY clause.

• Question 126:

  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 audit all customer data.

  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
  B. Option B

  ---

  The FOR SYSTEM_TIME ALL clause returns all the row versions from both the Temporal and History table.

  Note: A system-versioned temporal table defined through is a new type of user table in SQL Server 2016, here defined on the last line WITH (SYSTEM_VERSIONING = ON..., is designed to keep a full history of data changes and allow easy

  point in time analysis.

  To query temporal data, the SELECT statement FROM

  clause has a new clause FOR SYSTEM_TIME with five temporal-specific sub-clauses to query data across the current and history tables.

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

  Question 127:

  SIMULATION

  You have a database that contains the following tables.

  

  You need to create a query that lists the highest-performing salespersons based on the current year-to-date sales period. The query must meet the following requirements:

  Return the LastName and SalesYTD for the three salespersons with the highest year-to-date sales values.

  Exclude salespersons that have no value for TerritoryID.

  Construct the query using the following guidelines:

  

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

  

  Use two-part column names.

  

  Do not surround object names with square brackets.

  

  Do not use implicit joins.

  

  Use only single quotes for literal text.

  

  Use aliases only if required.

  

  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 top 3 lastname,salesYTD 2 FROM Person AS p INNER JOIN SalesPerson AS s 3 ON p.PersonID = s.SalesPersonID 4 WHERE territoryid is null 5 order by salesytd dsec

  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

  ---

  Correct Answer: Please see explanation

  1 SELECT top 3 lastname,salesYTD

  2 FROM Person AS p INNER JOIN SalesPerson AS s

  3 ON p.PersonID = s.SalesPersonID

  4 WHERE territoryid is not null

  5 order by salesytd desc

  Note:

  On line 4 add a not before null.

  On line 5 change dsec to desc.

  Question 128:

  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 table named Products that stores information about products your company sells. The table has a column named ListPrice that stores retail pricing information for products.

  Some products are used only internally by the company. Records for these products are maintained in the Products table for inventory purposes. The price for each of these products is $0.00. Customers are not permitted to order these

  products.

  You need to increase the list price for products that cost less than $100 by 10 percent. You must only increase pricing for products that customers are permitted to order. Solution: You run the following Transact-SQL statement:

  

  Does the solution meet the goal?

  A. Yes
  B. No

  B. No

  ---

  Products with a price of $0.00 would also be increased.

  Question 129:

  You have a database named MyDb. You run the following Transact-SQL statements:

  

  A value of 1 in the IsActive column indicates that a user is active.

  You need to create a count for active users in each role. If a role has no active users, you must display a zero as the active users count.

  Which Transact-SQL statement should you run?

  

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

  A. Option A

  Question 130:

  You have a table that was created by running the following Transact-SQL statement:

  

  You need to query the Courses table and return the result set as JSON. The output from the query must resemble the following format:

  

  A. SELECT CourseID AS [Course ID], Course as Name FROM Courses FOR JSON PATH('Courses')
  B. SELECT CourseID AS 'Course ID', Course AS Name FROM Courses FOR JSON ROOT('Courses')
  C. SELECT CourseID AS [Course ID], Course AS Name FROM Courses FOR JSON AUTO, ROOT('Courses')
  D. SELECT CourseID AS 'Course ID', Course AS Name FROM Courses FOR JSON AUTO, INCLUDE_NULL_VALUES('Courses')

  D. SELECT CourseID AS 'Course ID', Course AS Name FROM Courses FOR JSON AUTO, INCLUDE_NULL_VALUES('Courses')

  ---

  References: https://docs.microsoft.com/en-us/sql/relational-databases/json/include-null-values-in-json-include-null-values-option?view=sql-server-2017

  • 1
  • 2
  • ...
  • 12
  • 13
  • 14
  • 15
  • ...
  • 22
  • 23

  Related Exams:

  • 62-193

    Technology Literacy for Educators

  • 70-243

    Administering and Deploying System Center 2012 Configuration Manager

  • 70-355

    Universal Windows Platform – App Data, Services, and Coding Patterns

  • 77-420

    Excel 2013

  • 77-427

    Excel 2013 Expert Part One

  • 77-725

    Word 2016 Core Document Creation, Collaboration and Communication

  • 77-726

    Word 2016 Expert Creating Documents for Effective Communication

  • 77-727

    Excel 2016 Core Data Analysis, Manipulation, and Presentation

  • 77-728

    Excel 2016 Expert: Interpreting Data for Insights

  • 77-731

    Outlook 2016 Core Communication, Collaboration and Email Skills

  Tips on How to Prepare for the Exams

  Nowadays, the certification exams become more and more important and required by more and more enterprises when applying for a job. But how to prepare for the exam effectively? How to prepare for the exam in a short time with less efforts? How to get a ideal result and how to find the most reliable resources? Here on Vcedump.com, you will find all the answers. Vcedump.com provide not only Microsoft exam questions, answers and explanations but also complete assistance on your exam preparation and certification application. If you are confused on your 70-761 exam preparations and Microsoft certification application, do not hesitate to visit our Vcedump.com to find your solutions here.