Pinpoint 70-761 Practice Exam 2020

Exambible 70-761 Questions are updated and all 70-761 answers are verified by experts. Once you have completely prepared with our 70-761 exam prep kits you will be ready for the real 70-761 exam without a problem. We have Down to date Microsoft 70-761 dumps study guide. PASSED 70-761 First attempt! Here What I Did.

Check 70-761 free dumps before getting the full version:

NEW QUESTION 1
You are building a stored procedure named SP1 that calls a stored procedure named SP2.
SP2 calls another stored procedure named SP3 that returns a Recordset. The Recordset is stored in a
temporary table.
You need to ensure that SP2 returns a text value to SP1. What should you do?

  • A. Create a temporary table in SP2, and then insert the text value into the table.
  • B. Return the text value by using the ReturnValue when SP2 is called.
  • C. Add the txt value to an OUTPUT parameter of SP2.
  • D. Create a table variable in SP2, and then insert the text value into the table.

Answer: C

NEW QUESTION 2
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:
70-761 dumps exhibit
Does the solution meet the goal?

  • A. Yes
  • B. No

Answer: B

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

NEW QUESTION 3
You have the following subqueries: Subquery1, Subquery2, and Subquery3.
You need to replace the three subqueries with named result sets or temporary tables. The following requirements must be met:
70-761 dumps exhibit
Which replacement techniques should you use? To answer, select the appropriate options in the answer area.
70-761 dumps exhibit

  • A. Mastered
  • B. Not Mastered

Answer: A

Explanation:
Subquery1: common table expression (CTE)
A common table expression (CTE) can be thought of as a temporary result set that is defined within the execution scope of a single SELECT, INSERT, UPDATE, DELETE, or CREATE VIEW statement. A CTE is similar to a derived table in that it is not stored as an object and lasts only for the duration of the query. Unlike a derived table, a CTE can be self-referencing and can be referenced multiple times in the same query.
Subquery2: global temporary table
Global temporary tables are visible to any user and any connection after they are created, and are deleted when all users that are referencing the table disconnect from the instance of SQL Server.
Subquery3: local temporary table
Local temporary tables are visible only to their creators during the same connection to an instance of SQL Server as when the tables were first created or referenced. Local temporary tables are deleted after the user disconnects from the instance of SQL Server.
References:
https://technet.microsoft.com/en-us/library/ms190766(v=sql.105).aspx https://technet.microsoft.com/en-us/library/ms186986.aspx

NEW QUESTION 4
You have a database that contains the following tables:
70-761 dumps exhibit
A delivery person enters an incorrect value for the CustomerID column in the Invoices table and enters the following text in the ConfirmedReceivedBy column: “Package signed for by the owner Tim.”
You need to find the records in the Invoices table that contain the word Tim in the CustomerName field. How should you complete the Transact-SQL statement? To answer, drag the appropriate Transact-SQL
segments to the correct locations. Each Transact-SQL segment 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.
NOTE: Each correct selection is worth one point.
70-761 dumps exhibit

  • A. Mastered
  • B. Not Mastered

Answer: A

Explanation:
Box 1: SELECT CustomerID FROM Sales.Invoices
Box 2: INNER JOIN Sales.Customers.CustomerID = Sales.Invoices.CustomerID Box 3: WHERE CustomerName LIKE '%tim%'
Box 4: WHERE ConfirmedReceiveBy IN (SELECT CustomerName FROM Sales.Customers)

NEW QUESTION 5
You need to create a table named Sales that meets the following requirements:
70-761 dumps exhibit
Which Transact-SQL statement should you run?
70-761 dumps exhibit

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

Answer: D

Explanation:
datetime2 Defines a date that is combined with a time of day that is based on 24-hour clock. datetime2 can be considered as an extension of the existing datetime type that has a larger date range, a larger default fractional precision, and optional user-specified precision.

NEW QUESTION 6
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.
Multiple processes use the data from a table named Sales and place it in other databases across the organization. Some of the processes are not completely aware of the data types in the Sales table. This leads to data type conversion errors.
You need to implement a method that returns a NULL value id data conversion fails instead of throwing an error.
What should you implement?

  • A. the COALESCE function
  • B. a view
  • C. a table-valued function
  • D. the TRY_PARSE function
  • E. a stored procedure
  • F. the ISNULL function
  • G. a scalar function
  • H. the TRY_CONVERT function

Answer: H

Explanation:
TRY_CONVERT returns a value cast to the specified data type if the cast succeeds; otherwise, returns null. References: https://docs.microsoft.com/en-us/sql/t-sql/functions/try-convert-transact-sql

NEW QUESTION 7
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
70-761 dumps exhibit
Application.Cities
70-761 dumps exhibit
Sales.CustomerCategories
70-761 dumps exhibit
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:
70-761 dumps exhibit
Does the solution meet the goal?

  • A. Yes
  • B. No

Answer: B

Explanation:
The variable max, in the line DECLARE @areaCode nvarchar(max), is not defined.

NEW QUESTION 8
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:
70-761 dumps exhibit
You need to return normalized data for all customers that were added in the year 2014. Which Transact-SQL statement should you run?
70-761 dumps exhibit

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

Answer: G

Explanation:
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 = 1000 ORDER BY ValidFrom;
References: https://msdn.microsoft.com/en-us/library/dn935015.aspx

NEW QUESTION 9
You need to create an indexed view that requires logic statements to manipulate the data that the view displays.
Which two database objects should you use? Each correct answer presents a complete solution.

  • A. a user-defined table-valued function
  • B. a CRL function
  • C. a stored procedure
  • D. a user-defined scalar function

Answer: AC

NEW QUESTION 10
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 create a table named Products by running the following Transact-SQL statement:
70-761 dumps exhibit
You have the following stored procedure:
70-761 dumps exhibit
You need to modify the stored procedure to meet the following new requirements:
- Insert product records as a single unit of work.
- Return error number 51000 when a product fails to insert into the database.
- If a product record insert operation fails, the product information must not be permanently written to the database.
Solution: You run the following Transact-SQL statement:
70-761 dumps exhibit
Does the solution meet the goal?

  • A. Yes
  • B. No

Answer: B

Explanation:
With X_ABORT ON the INSERT INTO statement and the transaction will be rolled back when an error is raised, it would then not be possible to ROLLBACK it again in the IF XACT_STATE() <> 0 ROLLACK TRANSACTION statement.
Note: A transaction is correctly defined for the INSERT INTO ..VALUES statement, and if there is an error in the transaction it will be caught ant he transaction will be rolled back, finally an error 51000 will be raised.
Note: When SET XACT_ABORT is ON, if a Transact-SQL statement raises a run-time error, the entire transaction is terminated and rolled back.
XACT_STATE is a scalar function that reports the user transaction state of a current running request. XACT_STATE indicates whether the request has an active user transaction, and whether the transaction is capable of being committed.
The states of XACT_STATE are:
0 There is no active user transaction for the current request.
1 The current request has an active user transaction. The request can perform any actions, including writing data and committing the transaction.
2 The current request has an active user transaction, but an error has occurred that has caused the transaction to be classified as an committable transaction.
References:
https://msdn.microsoft.com/en-us/library/ms188792.aspx https://msdn.microsoft.com/en-us/library/ms189797.aspx

NEW QUESTION 11
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.
You query a database that includes two tables: Project and Task. The Project table includes the following columns:
70-761 dumps exhibit
70-761 dumps exhibit
When running an operation, you updated a column named EndTime for several records in the Project table, but updates to the corresponding task records in the Task table failed.
You need to synchronize the value of the EndTime column in the Task table with the value of the EndTime
column in the project table. The solution must meet the following requirements:
* If the EndTime column has a value, make no changes to the record.
* If the value of the EndTime column is null and the corresponding project record is marked as completed, update the record with the project finish time.
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.
70-761 dumps exhibit

  • A. Mastered
  • B. Not Mastered

Answer: A

Explanation:
Box 1: UPDATE T SET T.EndTime = P.EndTime
We are updating the EndTime column in the Task table. Box 2: FROM Task AS T
Where are updating the task table.
Box 3:INNER JOIN Project AS P on T.ProjectID = P.ProjectID
We join with the Project table (on the ProjectID columnID column). Box 4: WHERE P.EndTime is NOT NULL AND T.EndTime is NULL
We select the columns in the Task Table where the EndTime column in the Project table has a value (NOT NULL), but where it is NULL in the Task Table.
References: https://msdn.microsoft.com/en-us/library/ms177523.aspx

NEW QUESTION 12
You have a database that includes the tables shown in the exhibit. (Click the exhibit button.)
70-761 dumps exhibit
You need to create a list of all customers, the order ID for the last order that the customer placed, and the date that the order was placed. For customers who have not placed orders, you must substitute a zero for the order ID and 01/01/1990 for the date.
Which Transact-SQL statement should you run?
70-761 dumps exhibit

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

Answer: A

Explanation:
ISNULL Syntax: ISNULL ( check_expression , replacement_value ) author:"Luxemburg, Rosa"
The ISNULL function replaces NULL with the specified replacement value. The value of check_expression is returned if it is not NULL; otherwise, replacement_value is returned after it is implicitly converted to the type of check_expression.
References: https://msdn.microsoft.com/en-us/library/ms184325.aspx

NEW QUESTION 13
You need to create a database object that meets the following requirements:
accepts a product identifies 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 table-valued function
  • C. a user-defined stored procedure that has an OUTPUT parameter
  • D. a memory-optimized table that has updated statistics

Answer: B

NEW QUESTION 14
You have a database that contains the following tables.
70-761 dumps exhibit
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.
70-761 dumps exhibit
70-761 dumps exhibit
70-761 dumps exhibit
Use the Check Syntax button to verify your work. Any syntax or spelling errors will be reported by line and character position.

  • A. Mastered
  • B. Not Mastered

Answer: A

Explanation:
SELECT
Complaints.ComplaintID, Persons.Name FROM
Complaints LEFT OUTER JOIN Contacts ON Complaints.ComplaintID = Contacts.ComplaintID
LEFT OUTER JOIN Persons ON Contacts.PersonID = Persons.PersonID

NEW QUESTION 15
You have a database containing the following tables: Servers
70-761 dumps exhibit
Errors
70-761 dumps exhibit
You have a user-defined, scalar function named IPLookup that takes a DNS name as a parameter and returns
the IP address of the server. You have an additional user-defined, scalar function named DNSLookup, that takes an IP address as a parameter and returns a DNS name.
You create a view named vwErrors by running the following Transact-SQL statement:
70-761 dumps exhibit
You need to insert data by using the view.
How should you complete the Transact-SQL statement? To answer, drag the appropriate Transact-SQL segments to the correct location. 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.
70-761 dumps exhibit

  • A. Mastered
  • B. Not Mastered

Answer: A

Explanation:
References: https://docs.microsoft.com/en-us/sql/t-sql/queries/output-clause-transact-sql

NEW QUESTION 16
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.
You query a database that includes two tables: Project and Task. The Project table includes the following columns:
70-761 dumps exhibit
70-761 dumps exhibit
You need to identify the owner of each task by using the following rules:
- Return each task’s owner if the task has an owner.
- If a task has no owner, but is associated with a project that has an owner, return the project’s owner.
- Return the value -1 for all other cases.
How should you complete the Transact-SQL statement? To answer, select the appropriate Transact-SQL segments in the answer area.
70-761 dumps exhibit

  • A. Mastered
  • B. Not Mastered

Answer: A

Explanation:
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.
Box 2: T.UserID, p.UserID, -1
- Return each task’s owner if the task has an owner.
- If a task has no owner, but is associated with a project that has an owner, return the project’s owner.
- Return the value -1 for all other cases. Box 3: LEFT JOIN
The LEFT JOIN keyword returns all rows from the right table (table2), with the matching rows in the left table (table1). The result is NULL in the left side when there is no match. Here the right side could be NULL as the projectID of the task could be NULL.
References:
https://msdn.microsoft.com/en-us/library/ms190349.aspx
http://www.w3schools.com/Sql/sql_join_right.asp

NEW QUESTION 17
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 on this series.
You have a database that tracks orders and deliveries for customers in North America. System versioning is enabled for all tables. The database contains the Sales.Customers, Application.Cities, and Sales.CustomerCategories tables.
Details for the Sales.Customers table are shown in the following table:
70-761 dumps exhibit
Details for the Application.Cities table are shown in the following table:
70-761 dumps exhibit
Details for the Sales.CustomerCategories table are shown in the following table:
70-761 dumps exhibit
The marketing department is performing an analysis of how discount affect credit limits. They need to know the average credit limit per standard discount percentage for customers whose standard discount percentage is between zero and four.
You need to create a query that returns the data for the analysis.
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.
70-761 dumps exhibit

  • A. Mastered
  • B. Not Mastered

Answer: A

Explanation:
Box 1: 0, 1, 2, 3, 4
Pivot example:
-- Pivot table with one row and five columns
SELECT 'AverageCost' AS Cost_Sorted_By_Production_Days, [0], [1], [2], [3], [4]
FROM
(SELECT DaysToManufacture, StandardCost FROM Production.Product) AS SourceTable PIVOT
(
AVG(StandardCost)
FOR DaysToManufacture IN ([0], [1], [2], [3], [4])
) AS PivotTable; Box 2: [CreditLimit]
Box 3: PIVOT
You can use the PIVOT and UNPIVOT relational operators to change a table-valued expression into another table. PIVOT rotates a table-valued expression by turning the unique values from one column in the expression into multiple columns in the output, and performs aggregations where they are required on any remaining column values that are wanted in the final output.
Box 4: 0, 1, 2, 3, 4
The IN clause determines whether a specified value matches any value in a subquery or a list. Syntax: test_expression [ NOT ] IN ( subquery | expression [ ,...n ] )
Where expression[ ,... n ]
is a list of expressions to test for a match. All expressions must be of the same type as test_expression. References: https://technet.microsoft.com/en-us/library/ms177410(v=sql.105).aspx

NEW QUESTION 18
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:
70-761 dumps exhibit
The tables include the following records: Customer_CRMSystem
70-761 dumps exhibit
Customer_HRSystem
70-761 dumps exhibit
Records that contain null values for CustomerCode can be uniquely identified by CustomerName. You need to display a list of customers that do not appear in the Customer_HRSystem table. Which Transact-SQL statement should you run?
70-761 dumps exhibit
70-761 dumps exhibit

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

Answer: D

Explanation:
EXCEPT returns distinct rows from the left input query that aren’t output by the right input query. References: https://msdn.microsoft.com/en-us/library/ms188055.aspx

NEW QUESTION 19
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 that was created by running the following Transact-SQL statement:
70-761 dumps exhibit
The Products table includes the data shown in the following table:
70-761 dumps exhibit
TotalUnitPrice is calculated by using the following formula: TotalUnitPrice = UnitPrice * (UnitsInStock + UnitsOnOrder)
You need to ensure that the value returned for TotalUnitPrice for ProductB is equal to 600.00. Solution: You run the following Transact-SQL statement:
70-761 dumps exhibit
Does the solution meet the goal?

  • A. Yes
  • B. No

Answer: A

Explanation:
COALESCE evaluates the arguments in order and returns the current value of the first expression that initially does not evaluate to NULL.
References: https://docs.microsoft.com/en-us/sql/t-sql/language-elements/coalesce-transact-sql

NEW QUESTION 20
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 the value 50555.
The error severity level must be 14.
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.
70-761 dumps exhibit

  • A. Mastered
  • B. Not Mastered

Answer: A

Explanation:
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.
Note: RAISERROR syntax:
RAISERROR( { msg_id | msg_str | @local_variable }
{ ,severity ,state }
[ ,argument [ ,...n ] ] )
[ WITH option [ ,...n ] ]
The LOG option logs the error in the error log and the application log for the instance of the Microsoft SQL Server Database Engine.
References:
https://msdn.microsoft.com/en-us/library/ms178592.aspx

NEW QUESTION 21
......

100% Valid and Newest Version 70-761 Questions & Answers shared by Certifytools, Get Full Dumps HERE: https://www.certifytools.com/70-761-exam.html (New 186 Q&As)