BenchMark Quizzes by MDFT Pro

Choose the Correct Join Type

MDFT Academy operates a Fabric warehouse that contains the following data.

Table name	Primary key	Foreign key
Student	StudentID	SalesRegion.SalesRegionlD
StudentAddress	StudentID, AddressID	Student.StudentlD, Address.AddressID
Address	AddressID	None
SalesRegion	SalesRegionID	None

The data has the following characteristics:

Each Student is assigned a unique StudentID value.
Each Student is associated to a single SalesRegion value.
Each Student is associated to a single StudentAddress value.
The Student table contains 5 million rows.
All foreign key values are non-null.

You need to create a view to denormalize the data into a Student dimension that contains one row per distinct StudentID value. The solution must minimize query processing time and resources.

You create the following T-SQL statement:

CREATE VIEW DimStudent AS
SELECT
C.StudentID,
C. StudentName,
SR. SalesRegion,
A.Address,
A.PostalCode,
A.City,
A.Country
FROM Student C 
_____________ SalesRegion SR ON C.Sa1esRegionID = SR.SalesRegionID
INNER JOIN StudentAddress CA ON CA.StudentID = C.StudentID
INNER JOIN Address A ON A.AddressID = CA.AddressID

How should you complete the statement?

Choose the correct answer from the options below.

Explanations for each answer:

Cross Join is incorrect. A cross join would produce a Cartesian product of all rows from both tables, resulting in 5 million rows multiplied by the number of sales regions. This would create an enormous result set and is completely inappropriate for joining Students with their sales regions.
Inner Join is correct.
Left Outer Join is incorrect. A left outer join would return all Students even if they don't have a matching sales region. Since we know all foreign keys are non-null and each Student is assigned to a single sales region, this join type is unnecessary.
Right Outer Join is incorrect. A right outer join would return all sales regions even if they don't have matching Students. This is not what we want since we're creating a Student dimension and only need Students with valid sales regions.

Exam Preparation Quiz

Choose the Correct Join Type