70-761

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 in 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.

View Answer

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 have a database that stored information about servers and application errors.

The database contains the following tables.

Servers

Errors

You need to return all error log messages and the server where the error occurs most often.

Which Transact-SQL statement should you run?

A)

B)

C)

D)

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

View Answer

Answer: A

Explanation:

Explanation Incorrect Answers:

C: This would get all the ServerIDs, and not all the error logs

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 table named Products that contains information about the products that your company sells. The table contains many columns that do not always contain values.

You need to implement an ANSI standard method to convert the NULL values in the query output to the phrase “Not Applicable”.

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

View Answer

Answer: A

Explanation:

COALESCE evaluates the arguments in order and returns the current value of the first expression that initially doesn’t evaluate to NULL.

Incorrect Answers:

F: ISNULL is not a ANSI standard function. The COALESCE function is preferred.

References: https://docs.microsoft.com/en-us/sql/t-sql/language-elements/coalesce-transact-sql?view=sql-server-2017

SIMULATION

You have a database that contains the following tables.

You need to create a query that returns each complaint, the names of the employees handling the complaint, and the notes on each interaction. The Complaint field must be displayed first, followed by the employee’s name and the notes. Complaints must be returned even if no interaction has occurred.

Construct the query using the following guidelines:

– Use two-part column names.

– Use one-part table names.

– Use the first letter of the table name as its alias.

– 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.

View Answer

Answer: 1 SELECT c.Complaint, e.Name, i.Notes

2 FROM Complaints c

3 LEFT JOIN Interactions i ON c.ComplaintID = i.ComplaintID

4 LEFT JOIN Employees e ON i.EmployeeID = E.EmployeeID

Complaints must be returned even if no interaction has occurred, so we must use the LEFT JOIN, instead of just JOIN.

Note: You can drop the OUTER in LEFT OUTER JOIN, as has been done here on line 3 and line 4.

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

View Answer

Answer: B

Explanation:

As there are two separate INSERT INTO statements we cannot ensure that both or neither records are inserted.

SIMULATION

You have a database that contains the following tables.

You need to create a query that lists the lowest-performing salespersons based on the current year-to-date sales period.

The query must meet the following requirements:

– Return a column named Fullname that includes the salesperson FirstName, a space, and then LastName.

– Include the current year-to-date sales for each salesperson.

– Display only data for the three salespersons with the lowest year-to-year 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.

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

View Answer

Answer:

On ordering: ASC | DESC Specifies that the values in the specified column should be sorted in ascending or descending order. ASC sorts from the lowest value to highest value. DESC sorts from highest value to lowest value. ASC is the default sort order. Null values are treated as the lowest possible values.

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

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 .


B .

C .

D .

E .

View Answer

Answer: A

Explanation:

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

You have a database that includes the tables shown in the exhibit. (Click the exhibit button.)

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?

A)

B)

C)

D)

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

View Answer

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

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

View Answer

Answer: A

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

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 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 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 . DECLARE @sdate DATETIME, @edate DATETIME
SET @sdate = DATEFROMPARTS (2013, 12, 31)
SET @edate = DATEADD (d, 1, @sdate)
SELECT * FROM Sales.Customers FOR SYSTEM_TIME ALL
WHERE ValidFrom > @sdate AND ValidTo < @edate
B . DECLARE @sdate DATETIME, @edate DATETIME
SET @sdate = DATEFROMPARTS (2013, 12, 31)
SET @edate = DATEADD (d, -1, @sdate)
SELECT * FROM Sales.Customers
FOR SYSTEM_TIME BETWEEN @sdate AND @edate
C . DECLARE @date DATE
SET @date = DATEFROMPARTS (2013, 12, 31)
SELECT * FROM Sales.Customers
FOR SYSTEM_TIME ALL AS OF @date
D . DECLARE @date DATE
SET @date = DATEFROMPARTS (2013, 12, 31)
SELECT * FROM [Sales].[Customers] FOR SYSTEM_TIME ALL
WHERE @date BETWEEN ValidFrom AND ValidTo
E . DECLARE @date DATE
SET @date = DATEFROMPARTS (2013, 12, 31)
SELECT * FROM [Sales].[Customers] FOR SYSTEM_TIME ALL
WHERE SYSDATETIME = @date

View Answer

Answer: C

Explanation:

To perform any type of time-based analysis, use the new FOR SYSTEM_TIME clause with four temporal-specific sub-clauses to query data across the current and history tables.

Use the AS OF sub-clause when you need to reconstruct state of data as it was at any specific time in the past.

References: https://docs.microsoft.com/en-us/sql/relational-databases/tables/querying-data-in-a-system-versioned­temporal-table?view=sql-server-2017