You have a Fabric workspace that contains a warehouse named Warehouse1. Warehouse1 contains the following tables and columns: Table name | Column name | Data type ---|---|--- Employee | EmployeeID |…

This question tests your ability to construct a T-SQL SELECT statement in Microsoft Fabric Warehouse that combines LEFT JOIN, type casting, GROUP BY, and HAVING to denormalize tables and aggregate data with a filter on group size.

Design and implement data ingestion and transformation

Question

You have a Fabric workspace that contains a warehouse named Warehouse1. Warehouse1 contains the following tables and columns:
Table nameColumn nameData type
EmployeeEmployeeIDInt
EmployeeEmployeeNameVarchar(128)
EmployeeEmployeePositionVarchar(64)
ContractEmployeeIDInt
ContractContractTypeVarchar(64)
ContractStartDateDatetime2
ContractEndDateDatetime2
You need to denormalize the tables and include the ContractType and StartDate columns in the Employee table. The solution must meet the following requirements:

Ensure that the StartDate column is of the date data type.
Ensure that all the rows from the Employee table are preserved and include any matching rows from the Contract table.
Ensure that the result set displays the total number of employees per contract type for all the contract types that have more than two employees.
How should you complete the statement? To answer, select the appropriate options in the answer area.

Table name	Column name	Data type
Employee	EmployeeID	Int
Employee	EmployeeName	Varchar(128)
Employee	EmployeePosition	Varchar(64)
Contract	EmployeeID	Int
Contract	ContractType	Varchar(64)
Contract	StartDate	Datetime2
Contract	EndDate	Datetime2
You need to denormalize the tables and include the ContractType and StartDate columns in the Employee table. The solution must meet the following requirements:

Explanation

Approach. The correct statement uses a LEFT JOIN between Employee and Contract on EmployeeID - this satisfies the requirement to preserve ALL Employee rows while including any matching Contract rows. StartDate must be wrapped in CAST(StartDate AS DATE) to convert it from DATETIME2 to the DATE data type. To count employees per contract type, the query must use GROUP BY ContractType and SELECT COUNT(EmployeeID) AS TotalEmployees (or similar alias). Finally, to restrict results to contract types with MORE than two employees, a HAVING COUNT(EmployeeID) > 2 clause is required - WHERE cannot be used here because it filters rows before aggregation, not after. The assembled structure is: SELECT c.ContractType, COUNT(e.EmployeeID) AS TotalEmployees FROM Employee e LEFT JOIN Contract c ON e.EmployeeID = c.EmployeeID GROUP BY c.ContractType HAVING COUNT(e.EmployeeID) > 2.

Concept tested. T-SQL query construction in Microsoft Fabric Warehouse - specifically: LEFT JOIN semantics (all rows from left table preserved), CAST for data type conversion (DATETIME2 → DATE), GROUP BY for aggregation, and HAVING for post-aggregation filtering versus WHERE for pre-aggregation filtering.

Reference. Microsoft Learn - Query data in Microsoft Fabric Warehouse; T-SQL HAVING clause documentation; Microsoft Fabric Data Warehouse overview

Topics

#SQL Queries#Data Transformation#Data Denormalization#Data Aggregation

Community Discussion

No community discussion yet for this question.

Full DP-700 Practice