70-761 Querying Data with Transact-SQL - Practice Questions

Question 13

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 following records exist in the tables:

Customer_CRMSystem

Customer_HRSystem

Records that contain null values for CustomerCode can be uniquely identified by CustomerName.

You need to display distinct customers that appear in both tables.

Which Transact-SQL statement should you run?

Select an option, then click Submit answer.

○
Option A
○
Option B
○
Option C
○
Option D
○
Option E
○
Option F
○
Option G
○
Option H

Reference / correct answer:

Option B

You need to display distinct customers that appear in both tables.

INTERSECT returns distinct rows that are output by both the left and right input queries operator.

Incorrect Answers:

A: Inner joins return rows only when there is at least one row from both tables that matches the join condition. Inner joins eliminate the rows that do not match with a row from the other table.

D: EXCEPT returns distinct rows from the left input query that aren’t output by the right input query.

E: UNION specifies that multiple result sets are to be combined and returned as a single result set, but this will not work here as the CustomerID column values do not match.

F: UNION ALL incorporates all rows into the results. This includes duplicates. If not specified, duplicate rows are removed.

G: A cross join would produce the Cartesian product of the two tables.

H: To retain the nonmatching information by including nonmatching rows in the results of a join, use a full outer join. SQL Server provides the full outer join operator, FULL OUTER JOIN, which includes all rows from both tables, regardless of whether or not the other table has a matching value.

References:

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

Question 14

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 create a list of all unique customers that appear in either table.

Which Transact-SQL statement should you run?

Select an option, then click Submit answer.

○
Option A
○
Option B
○
Option C
○
Option D
○
Option E
○
Option F
○
Option G
○
Option H

70-761 – Querying Data with Transact-SQL