DP-600 - SQL in Microsoft Fabric

SELECT and FROM

-- Basic SELECT
SELECT CustomerID, CustomerName, Region
FROM dbo.Customers
WHERE Region = 'North'
ORDER BY CustomerName;

-- Select all columns
SELECT * FROM dbo.Products;

-- Column aliases
SELECT SalesAmount AS Revenue, YEAR(OrderDate) AS OrderYear
FROM dbo.FactSales;

SQL execution order: FROM -> WHERE -> GROUP BY -> HAVING -> SELECT -> ORDER BY. Understanding this order is critical - you cannot reference a SELECT alias in WHERE (evaluated earlier).

WHERE, GROUP BY, HAVING, ORDER BY

-- GROUP BY with aggregation
SELECT ProductCategory, SUM(SalesAmount) AS TotalSales, COUNT(*) AS OrderCount
FROM dbo.FactSales
GROUP BY ProductCategory
HAVING SUM(SalesAmount) > 100000
ORDER BY TotalSales DESC;

-- HAVING vs WHERE
-- WHERE filters individual rows BEFORE aggregation
-- HAVING filters groups AFTER aggregation

String Functions (T-SQL)

Date and Time Functions (T-SQL)

-- Examples
SELECT DATEADD(MONTH, -1, GETDATE()) AS OneMonthAgo;
SELECT DATEDIFF(DAY, '2024-01-01', GETDATE()) AS DaysSinceNewYear;
SELECT FORMAT(GETDATE(), 'yyyy-MM-dd') AS ISODate;

SQL JOINs

-- LEFT JOIN example: all customers, even those with no orders
SELECT c.CustomerName, o.OrderID
FROM dbo.Customers c
LEFT JOIN dbo.Orders o ON c.CustomerID = o.CustomerID;

Window Functions

-- Running total
SELECT OrderDate, Amount,
  SUM(Amount) OVER (ORDER BY OrderDate ROWS UNBOUNDED PRECEDING) AS RunningTotal
FROM dbo.Sales;

-- Rank customers by sales
SELECT CustomerID, TotalSales,
  RANK() OVER (ORDER BY TotalSales DESC) AS SalesRank,
  ROW_NUMBER() OVER (ORDER BY TotalSales DESC) AS RowNum
FROM (SELECT CustomerID, SUM(Amount) AS TotalSales FROM dbo.Sales GROUP BY CustomerID) sub;

-- Partition by category
SELECT ProductCategory, ProductName, Amount,
  SUM(Amount) OVER (PARTITION BY ProductCategory) AS CategoryTotal
FROM dbo.Sales;

Window functions (OVER clause) compute values across a set of rows related to the current row without collapsing the result like GROUP BY. Use PARTITION BY to define window groups and ORDER BY to define ordering within the window.

DDL: CREATE, ALTER, DROP

-- Create table
CREATE TABLE dbo.Products (
    ProductID INT NOT NULL,
    ProductName NVARCHAR(200) NOT NULL,
    Price DECIMAL(10, 2),
    CategoryID INT
);

-- Add column
ALTER TABLE dbo.Products ADD Discount DECIMAL(5, 2) DEFAULT 0;

-- Drop table
DROP TABLE dbo.Products;

In Fabric Data Warehouse, DML operations (INSERT, UPDATE, DELETE, MERGE) are fully supported. The Lakehouse SQL endpoint is read-only and does not support DML.

Views, Functions, Stored Procedures

-- Create a view
CREATE VIEW dbo.vw_SalesSummary AS
SELECT ProductCategory, SUM(Amount) AS TotalSales
FROM dbo.FactSales
GROUP BY ProductCategory;

-- Inline table-valued function
CREATE FUNCTION dbo.fn_SalesByRegion(@Region NVARCHAR(50))
RETURNS TABLE AS
RETURN (
    SELECT * FROM dbo.FactSales WHERE Region = @Region
);

-- Stored procedure
CREATE PROCEDURE dbo.usp_GetTopCustomers @TopN INT AS
BEGIN
    SELECT TOP(@TopN) CustomerID, SUM(Amount) AS TotalSales
    FROM dbo.FactSales
    GROUP BY CustomerID
    ORDER BY TotalSales DESC;
END;

Views and functions are read-only SQL objects. Stored procedures can contain DML and are useful for orchestrated data loads in Fabric Warehouse. Views can be referenced by Power BI Direct Lake as long as the underlying tables are Delta tables.

Handling NULLs