http://https://www.lead1pass.com/Microsoft/70-761-practice-exam-dumps.html (160 Q&As Dumps, 30%OFF Special Discount: 30free )

NEW QUESTION NO: 15
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:

You have the following stored procedure:

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:

Does the solution meet the goal?
A. Yes
B. No
Answer: B
Explanation/Reference:
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() <> O 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 caughtant 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 thatreports 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 hasoccurred that has caused the

transaction to be classified as an uncommittable transaction.
References:
https://msdn.microsoft.com/en-us/library/ms188792.aspx
https://msdn.microsoft.com/en-us/library/ms189797.aspx

NEW QUESTION NO: 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 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 discover an application bug that impacts customer data for records created on or after January 1,
2014. In order to fix the data impacted by the bug, application programmers require a report that contains customer data as it existed on December 31, 2013.
You need to provide the query for the report.
Which Transact-SQL statement should you use?
A:

B:

C:

D:

A. Option A
B. Option B
C. Option C
D. Option D
Answer: D
Explanation/Reference:
Explanation:
The datetime datetype defines a date that is combined with a time of day with fractional seconds that is based on a 24-hour clock.
The DATEFROMPARTS function returns a date value for the specified year, month, and day.
Incorrect Answers:
A: ValidFrom should be less (<) than @sdate AND ValidTo should be greater (>) than @edate.
B: We should add a day with DATEADD, not subtract one day.
C: We cannot compare a date to an exact datetime.
References: https://msdn.microsoft.com/en-us/library/ms187819.aspx

NEW QUESTION NO: 17
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 Customer by running the following Transact-SQL statement:

You must insert the following data into the Customer table:

You need to ensure that both records are inserted or neither record is inserted.
Solution: You run the following Transact-SQL statement:

Does the solution meet the goal?
A. Yes
B. No
Answer: A
Explanation/Reference:
Explanation:
With the INSERT INTO..VALUES statement we can insert both values with just one statement. This ensures that both records or neither is inserted.
References:https://msdn.microsoft.com/en-us/library/ms174335.aspx

NEW QUESTION NO: 18
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:

B:

C:

D:

A. Option C
B. Option B
C. Option D
D. Option A
Answer: A

NEW QUESTION NO: 19
DRAG DROP
You have a database that contains the following tables:

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.
Select and Place:

Answer: 

Explanation/Reference:
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 NO: 20
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 is denormalized. Users make frequent changes to data in a primary table.
You need to ensure that users cannot change the tables directly, and that changes made to the primary table also update any related tables.
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: B
Explanation/Reference:
Explanation:
Using an Indexed View would allow you to keep your base data in properly normalized tables and maintain data-integrity while giving you the denormalized "view" of that data.
References: http://stackoverflow.com/questions/4789091/updating-redundant-denormalized-data- automatically-in-sql-server

NEW QUESTION NO: 21
DRAG DROP
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.
Select and Place:

Answer: 

Explanation/Reference:
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

NEW QUESTION NO: 22
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.
A database has two tables as shown in the following database diagram:

You need to list all provinces that have at least two large cities. A large city is defined as having a population of at least one million residents. The query must return the following columns:
tblProvince.ProvinceId

tblProvince.ProvinceName

a derived column named LargeCityCount that presents the total count of large cities for the province

Solution: You run the following Transact-SQL statement:

Does the solution meet the goal?
A. Yes
B. No
Answer: A
Explanation/Reference:
Explanation:
The requirement to list all provinces that have at least two large cities is meet by the WHERE CitySummary.LargeCityCount >=2 clause.
CROSS APPLY willwork fine here.
Note:
The APPLY operator allows you to invoke a table-valued function for each row returned by an outer table expression of a query. The table-valued function acts as the right input and the outer table expression acts as the left input. The right input is evaluated for each row from the left input and the rows produced are combined for the final output. The list of columns produced by the APPLY operator is the set of columns in the left input followed by the list of columns returned by the right input.
There are two forms of APPLY: CROSS APPLY and OUTER APPLY. CROSS APPLY returns only rows from the outer table that produce a result set from the table-valued function. OUTER APPLY returns both rows that produce a result set, and rows that do not, with NULL values in the columns produced by the table-valued function.
References: https://technet.microsoft.com/en-us/library/ms175156(v=sql.105).aspx

NEW QUESTION NO: 23
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 run a query to find the total number of customers who have both deposit and loan accounts.
Which Transact-SQL statement should you run?
A. SELECT COUNT(*)
FROM (SELECT AcctNo
FROM tblDepositAcct
INTERSECT
SELECT AcctNo
FROM tblLoanAcct) R
B. SELECT COUNT(*)
FROM (SELECT CustNo
FROM tblDepositAcct
UNION
SELECT CustNo
FROM tblLoanAcct) R
C. SELECT COUNT(*)
FROM (SELECT CustNo
FROM tblDepositAcct
UNION ALL
SELECT CustNo
FROM tblLoanAcct) R
D. SELECT COUNT (DISTINCT D.CustNo)
FROM tblDepositAcctD, tblLoanAcct L
WHERE D.CustNo = L.CustNo
E. SELECT COUNT(DISTINCT L.CustNo)
FROM tblDepositAcct D
RIGHT JOIN tblLoanAcct L ON D.CustNo = L.CustNo
WHERE D.CustNo IS NULL
F. SELECT COUNT(*)
FROM (SELECT CustNo
FROM tblDepositAcct
EXCEPT
SELECT CustNo
FROM tblLoanAcct) R
G. SELECT COUNT (DISTINCT COALESCE(D.CustNo, L.CustNo))
FROM tblDepositAcct D
FULL JOIN tblLoanAcct L ON D.CustNo = L.CustNo
WHERE D.CustNo IS NULL OR L.CustNo IS NULL
H. SELECT COUNT(*)
FROM tblDepositAcct D
FULL JOIN tblLoanAcct L ON D.CustNo = L.CustNo
Answer: A
Explanation/Reference:
Explanation:
The SQL INTERSECT operator is used to return the results of 2 or more SELECT statements. However, it only returns the rows selected by all queries or data sets. If a record exists in one query and not in the other, it will be omitted from the INTERSECT results.
References: https://www.techonthenet.com/sql/intersect.php

NEW QUESTION NO: 24
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
Answer: B
Explanation/Reference:
Explanation:
The function should return nvarchar(10) and not a TABLE.
References: https://sqlstudies.com/2014/08/06/schemabinding-what-why/

NEW QUESTION NO: 25
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.
A database has two tables as shown in the following database diagram:

You need to list all provinces that have at least two large cities. A large city is defined as having a population of at least one million residents. The query must return the following columns:
tblProvince.ProvinceId

tblProvince.ProvinceName

a derived column named LargeCityCount that presents the total count of large cities for the province

Solution: You run the following Transact-SQL statement:

Does the solution meet the goal?
A. Yes
B. No
Answer: B
Explanation/Reference:
Explanation:
We need to list all provinces that have at least two large cities. There is no reference to this in the code.