Exam 70-761 topic 1 question 116 discussion

In my opinion the correct answer is to use INTERSECT since we only want to see the distinct customers which are in both tables. or do I misunderstand?

INTERSECT is correct, try it yourself: CREATE TABLE Customer_CRMSystem ( CustomerID INT NOT NULL PRIMARY KEY NONCLUSTERED, CustomerCode char(4) NULL, CustomerName varchar(50) NOT Null ) CREATE TABLE Customer_HRSystem ( CustomerID INT NOT NULL PRIMARY KEY NONCLUSTERED, CustomerCode char(4) NULL, CustomerName varchar(50) NOT Null ) SELECT c.CustomerCode, c.CustomerName, h.CustomerCode, h.Customername FROM Customer_CRMSystem c LEFT JOIN Customer_HRSystem h ON c.CustomerCode = h.CustomerCode AND c.CustomerName = h.CustomerName SELECT CustomerCode, CustomerName FROM Customer_CRMSystem INTERSECT SELECT CustomerCode, CustomerName FROM Customer_HRSystem

I'd say intersect has been invented for this to give the distinct values across multiple tables.

INTERSECT is the only option that returns all 4 matching records

you need customer that appears IN BOTH TABLE if in one table customer not appear YOU DONT NEED HIM. Correct answer is intercept

The correct is one with INTERSECT .only this gives distinct entities that are presented in both tables.

Hello, The question is asking for DISTINCT customers that appear in both tables, and records that contain NULL values for customerCode are uniquely identified by CustomerName. Just looking at the tables Jane, Roya and Yossi are the three distinct customers that appear in both. INTERSECT does give distinct cutomers.

Hello, The question is asking for DISTINCT customers that appear in both tables, and records that contain NULL values for customer are uniquely identified by CustomerName. Just looking at the tables Jane, Roya and Yossi are the three distinct customers that appear in both. INTERSECT does give distinct customers.

I understood using UNION. DROP TABLE IF EXISTS Customer_CRMSystem; CREATE TABLE Customer_CRMSystem (CustomerID tinyint, CustomerCode char(4), CustomerName varchar(50)); INSERT INTO Customer_CRMSystem VALUES ( 1, 'CUS1', 'Roya') ,(2, 'CUS9', 'Yossi') ,(3, 'CUS4', 'Jack') ,(4, NULL, 'Jane') ,(5, NULL, 'Francisco'); DROP TABLE IF EXISTS Customer_HRSystem; CREATE TABLE Customer_HRSystem (CustomerID tinyint, CustomerCode char(4), CustomerName varchar(50)); INSERT INTO Customer_HRSystem VALUES ( 1, 'CUS1', 'Roya') ,(2, 'CUS2', 'Jose') ,(3, 'CUS9', 'Yossi') ,(4, NULL, 'Jane'); SELECT CustomerCode, CustomerName FROM Customer_CRMSystem UNION SELECT CustomerCode, CustomerName FROM Customer_HRSystem;

"You need to display distinct customers that appear in both tables." INTERSECT is the right answer for this as the 3 customers (Jane, Roya, Yossie) appeared in both tables. UNION will return the all distinct customers for each table combined. (All 6 customers)

the question is too vague, declare @CRMSystem table ( CustomerID INT, CustomerCode char(4) NULL, CustomerName varchar(50) NOT Null ) declare @HRSystem table ( CustomerID INT NOT NULL PRIMARY KEY NONCLUSTERED, CustomerCode char(4) NULL, CustomerName varchar(50) NOT Null ) INSERT INTO @CRMSystem(CustomerID, CustomerCode, CustomerName ) VALUES (1, 'cus1', 'Roya') , (2, 'cus9', 'Yossi') , (3, 'cus4', 'Jack') , (4, null, 'Jane') , (5, null, 'Francisco') INSERT INTO @HRSystem(CustomerID, CustomerCode, CustomerName ) VALUES (1, 'cus1', 'Roya') , (2, 'cus2', 'Jose') , (3, 'cus9', 'Yossi') , (4, null, 'Jane') SELECT c.CustomerCode, c.CustomerName, h.CustomerCode, h.Customername FROM @CRMSystem c full outer JOIN @HRSystem h ON c.CustomerCode = h.CustomerCode AND c.CustomerName = h.CustomerName SELECT CustomerCode, CustomerName FROM @CRMSystem INTERSECT SELECT CustomerCode, CustomerName FROM @HRSystem

Correct answer is INTERSECT (B). Tested

INTERSECT will return values that's distinct from both queries however FULL OUTER JOIN will return customerID=5 which is not in the HR system with NULL so the answer is B.

INTERESCT is the correct answer not sure why this site has mostly incorrect answers. I've tested this and intersect returns distinct customers that appear in both tables :/

"distinct customers that appear in both tables" can have 2 meanings: 1. distinct customers that appear in both tables 2. distinct customers for each table In this case, I think they asked "distinct customers for each table"..

I think B