Free Microsoft 70-761 Exam Dumps Questions & Answers

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:

The Task table includes the following columns:

Users report performance issues when they run the following query:

You need to improve query performance and limit results to projects that specify an end date.
How should you complete the Transact-SQL statement? To answer, select the appropriate Transact-SQL segments in the answer area.

Correct Answer:

Explanation

Wildcard character %: Any string of zero or more characters.
For example: If the LIKE '5%' symbol is specified, the Database Engine searches for the number 5 followed by any string of zero or more characters.
References: https://docs.microsoft.com/en-us/sql/t-sql/language-elements/like-transact-sql

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

You need to determine the total number of customers who have only loan accounts.
Which Transact-SQL statement should you run?

• A.  SELECT COUNT(DISTINCT L.CustNo)FROM tblDepositAcct DRIGHT JOIN tblLoanAcct L ON D.CustNo = L.CustNoWHERE D.CustNo IS NULL
• B.  SELECT COUNT(*)FROM (SELECT CustNoFROM tblDepositAcctUNION ALLSELECT CustNoFROM tblLoanAcct) R
• C.  SELECT COUNT(*)FROM (SELECT CustNoFROM tblDepositAcctUNIONSELECT CustNoFROM tblLoanAcct) R
• D.  SELECT COUNT(*)FROM tblDepositAcct DFULL JOIN tblLoanAcct L ON D.CustNo = L.CustNo
• E.  SELECT COUNT(*)FROM (SELECT CustNoFROM tblDepositAcctEXCEPTSELECT CustNoFROM tblLoanAcct) R
• F.  SELECT COUNT (DISTINCT COALESCE(D.CustNo, L.CustNo))FROM tblDepositAcct DFULL JOIN tblLoanAcct L ON D.CustNo = L.CustNoWHERE D.CustNo IS NULL OR L.CustNo IS NULL
• G.  SELECT COUNT(*)FROM (SELECT AcctNoFROM tblDepositAcctINTERSECTSELECT AcctNoFROM tblLoanAcct) R
• H.  SELECT COUNT (DISTINCT D.CustNo)FROM tblDepositAcct D, tblLoanAcct LWHERE D.CustNo = L.CustNo

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 B
• B.  Option G
• C.  Option D
• D.  Option C
• E.  Option H
• F.  Option A
• G.  Option F
• H.  Option E

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:

Details for the Application.Cities table are shown in the following table:

Details for the Sales.CustomerCategories table are shown in the following table:

You are preparing a promotional mailing. The mailing must only be sent to customers in good standing that live in medium and large cities.
You need to write a query that returns all customers that are not on credit hold who live in cities with a population greater than 10,000.
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.

Correct Answer:

Explanation

Box 1: IN (
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 subquery
Is a subquery that has a result set of one column. This column must have the same data type as test_expression.
Box 2: WHERE
Box 3: AND [IsOnCreditHold] = 0
Box 4: )
References: https://msdn.microsoft.com/en-us/library/ms177682.aspx

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 table variable in SP2, and then insert the text value into the table.
• B.  Add the txt value to an OUTPUT parameter of SP2.
• C.  Create a temporary table in SP2, and then insert the text value into the table.
• D.  Return the text value by using the ReturnValue when SP2 is called.