DP-600試験無料問題集「Microsoft Implementing Analytics Solutions Using Microsoft Fabric 認定」

Explanation:

When creating a shortcut to an Azure Data Lake Storage Gen2 account in a lakehouse, you should use the abfss (Azure Blob File System Secure) connection string and the dfs (Data Lake File System) endpoint. The abfss is used for secure access to Azure Data Lake Storage, and the dfs endpoint indicates that the Data Lake Storage Gen2 capabilities are to be used.

解説: (GoShiken メンバーにのみ表示されます)

Explanation:

Comprehensive Detailed Explanation
Step 1: Requirement
We need to compare the average miles per trip for:
Statutory holidays (when IsHoliday = 1)
Non-statutory holidays (when IsHoliday = 0)
Step 2: Formula for average miles per trip
Average miles per trip = total miles ÷ number of trips
Total miles # SUM(t.tripDistance)
Number of trips # COUNT(t.tripID)
So the calculation is:
(SUM(t.tripDistance) / COUNT(t.tripID)) AS MilesPerTrip
Step 3: Grouping
We need a comparison by holiday status.
So we must group the results by:
GROUP BY d.IsHoliday
This ensures we get two rows: one for IsHoliday = 1 and one for IsHoliday = 0.
Step 4: Final Query
SELECT
d.IsHoliday,
(SUM(t.tripDistance) / COUNT(t.tripID)) AS MilesPerTrip
FROM DimDate d
INNER JOIN Trips t ON d.DateID = t.DateID
GROUP BY d.IsHoliday;
Why This is Correct
The formula ensures average miles per trip.
Grouping ensures comparison between holidays vs non-holidays.
Efficient aggregation, minimal computation.
References
Aggregate functions in T-SQL
GROUP BY clause

解説: (GoShiken メンバーにのみ表示されます)

解説: (GoShiken メンバーにのみ表示されます)

解説: (GoShiken メンバーにのみ表示されます)

Explanation:

Comprehensive Detailed Explanation
We are tasked with writing a T-SQL query to denormalize the tables Employee and Contract in a Fabric warehouse.
Requirements Breakdown
Include attributes from matching rows in the Contract table
This means we must join Employee and Contract on EmployeeID.
Ensure that all rows from the Employee table are preserved
This requires a LEFT OUTER JOIN from Employee to Contract. If an employee has no contract, we still want the employee record included.
Return the total number of employees per contract type for all contract types that have more than two employees This means we need to:
Group by ContractType
Count distinct EmployeeID
Filter groups where that count > 2.
To filter aggregated results, we use HAVING, not WHERE.
Completed Query
WITH result AS (
SELECT
e.EmployeeID,
e.EmployeeName,
e.EmployeePosition,
c.ContractType,
c.StartDate
FROM Employee AS e
LEFT OUTER JOIN Contract AS c
ON c.EmployeeID = e.EmployeeID
)
SELECT
ContractType,
COUNT(DISTINCT EmployeeID) AS TotalEmployees
FROM result
GROUP BY ContractType
HAVING COUNT(DISTINCT EmployeeID) > 2;
Why This is Correct
LEFT OUTER JOIN ensures all employees are included, regardless of contracts.
HAVING allows filtering aggregated groups by employee counts.
The query returns ContractType, StartDate (as requested in denormalization), and aggregated employee counts.
References
T-SQL JOINs in Microsoft Fabric
Aggregate functions and HAVING clause
Denormalization best practices in Fabric warehouses

Explanation: