This post is a part of the DP-600: Implementing Analytics Solutions Using Microsoft Fabric Exam Prep Hub; and this topic falls under these sections: 
Prepare data 
    --> Query and analyze data 
        --> Select, Filter, and Aggregate Data Using SQL

Working with SQL to select, filter, and aggregate data is a core skill for analytics engineers using Microsoft Fabric. Whether querying data in a warehouse, lakehouse SQL analytics endpoint, or semantic model via DirectQuery, SQL enables precise data retrieval and summarization for reporting, dashboards, and analytics solutions.

For DP-600, you should understand how to construct SQL queries that perform:

• Selecting specific data columns
• Filtering rows based on conditions
• Aggregating values with grouping and summary functions

SQL Data Selection

Selecting data refers to using the SELECT clause to choose which columns or expressions to return.

Example:

SELECT
    CustomerID,
    OrderDate,
    SalesAmount
FROM Sales;

• Use * to return all columns:
  SELECT * FROM Sales;
• Use expressions to compute derived values: SELECT OrderDate, SalesAmount, SalesAmount * 1.1 AS AdjustedRevenue FROM Sales;

Exam Tip: Be purposeful in selecting only needed columns to improve performance.

SQL Data Filtering

Filtering data determines which rows are returned based on conditions using the WHERE clause.

Basic Filtering:

SELECT *
FROM Sales
WHERE OrderDate >= '2025-01-01';

Combined Conditions:

• AND: WHERE Country = 'USA' AND SalesAmount > 1000
• OR: WHERE Region = 'East' OR Region = 'West'

Null and Missing Value Filters:

WHERE SalesAmount IS NOT NULL

Exam Tip: Understand how WHERE filters reduce dataset size before aggregation.

SQL Aggregation

Aggregation summarizes grouped rows using functions like SUM, COUNT, AVG, MIN, and MAX.

Basic Aggregation:

SELECT
    SUM(SalesAmount) AS TotalSales
FROM Sales;

Grouped Aggregation:

SELECT
    Country,
    SUM(SalesAmount) AS TotalSales,
    COUNT(*) AS OrderCount
FROM Sales
GROUP BY Country;

Filtering After Aggregation:

Use HAVING instead of WHERE to filter aggregated results:

SELECT
    Country,
    SUM(SalesAmount) AS TotalSales
FROM Sales
GROUP BY Country
HAVING SUM(SalesAmount) > 100000;

Exam Tip:

• Use WHERE for row-level filters before grouping.
• Use HAVING to filter group-level aggregates.

Combining Select, Filter, and Aggregate

A complete SQL query often blends all three:

SELECT
    ProductCategory,
    COUNT(*) AS Orders,
    SUM(SalesAmount) AS TotalSales,
    AVG(SalesAmount) AS AvgSale
FROM Sales
WHERE OrderDate BETWEEN '2025-01-01' AND '2025-12-31'
GROUP BY ProductCategory
ORDER BY TotalSales DESC;

This example:

• Selects specific columns and expressions
• Filters by date range
• Aggregates by product category
• Orders results by summary metric

SQL in Different Fabric Workloads

Performance and Pushdown

When using SQL in Fabric:

• Engines push filters and aggregations down to the data source for performance.
• Select only needed columns early to limit data movement.
• Avoid SELECT * in production queries unless necessary.

Key SQL Concepts for the Exam

Understanding how these work together is essential for creating analytics-ready datasets.

Common Exam Scenarios

You may be asked to:

• Write SQL to filter data based on conditions
• Summarize data across groups
• Decide whether to use WHERE or HAVING
• Identify the correct SQL pattern for a reporting requirement

Example exam prompt:

“Which SQL query correctly returns the total sales per region, only for regions with more than 1,000 orders?”

Understanding aggregate filters (HAVING) and groupings will be key.

Final Exam Tips

If a question mentions:

• “Return summary metrics”
• “Only include rows that meet conditions”
• “Group results by category”

…you’re looking at combining SELECT, WHERE, and GROUP BY in SQL.

• WHERE filters rows before aggregation
• HAVING filters after aggregation
• GROUP BY is required for per-group metrics
• Use aggregate functions intentionally
• Performance matters — avoid unnecessary columns

Practice Questions:

Here are 10 questions to test and help solidify your learning and knowledge. As you review these and other questions in your preparation, make sure to …

• Identifying and understand why an option is correct (or incorrect) — not just which one
• Look for and understand the usage scenario of keywords in exam questions to guide you
• Expect scenario-based questions rather than direct definitions

1. Which SQL clause is used to filter rows before aggregation occurs?

A. HAVING
B. GROUP BY
C. WHERE
D. ORDER BY

✅ Correct Answer: C

Explanation:
The WHERE clause filters individual rows before any aggregation or grouping takes place. HAVING filters results after aggregation.

2. You need to calculate total sales per product category. Which clause is required?

A. WHERE
B. GROUP BY
C. ORDER BY
D. HAVING

✅ Correct Answer: B

Explanation:
GROUP BY groups rows so aggregate functions (such as SUM) can be calculated per category.

3. Which function returns the number of rows in each group?

A. SUM()
B. COUNT()
C. AVG()
D. MAX()

✅ Correct Answer: B

Explanation:
COUNT() counts the number of rows in a group. It is commonly used to count records or transactions.

4. Which query correctly filters aggregated results?

A.

WHERE SUM(SalesAmount) > 10000

B.

HAVING SUM(SalesAmount) > 10000

C.

GROUP BY SUM(SalesAmount) > 10000

D.

ORDER BY SUM(SalesAmount) > 10000

✅ Correct Answer: B

Explanation:
HAVING is used to filter aggregated values. WHERE cannot reference aggregate functions.

5. Which SQL statement returns the total number of orders?

A.

SELECT COUNT(*) FROM Orders;

B.

SELECT SUM(*) FROM Orders;

C.

SELECT TOTAL(Orders) FROM Orders;

D.

SELECT COUNT(Orders) FROM Orders;

✅ Correct Answer: A

Explanation:
COUNT(*) counts all rows in a table, making it the correct way to return total order count.

6. Which clause is used to sort aggregated query results?

A. GROUP BY
B. WHERE
C. ORDER BY
D. HAVING

✅ Correct Answer: C

Explanation:
ORDER BY sorts the final result set, including aggregated columns.

7. What happens if a column in the SELECT statement is not included in the GROUP BY clause or an aggregate function?

A. The query runs but returns incorrect results
B. SQL automatically groups it
C. The query fails
D. The column is ignored

✅ Correct Answer: C

Explanation:
In SQL, any column in SELECT must either be aggregated or included in GROUP BY.

8. Which query returns average sales amount per country?

A.

SELECT Country, AVG(SalesAmount)
FROM Sales;

B.

SELECT Country, AVG(SalesAmount)
FROM Sales
GROUP BY Country;

C.

SELECT Country, SUM(SalesAmount)
GROUP BY Country;

D.

SELECT AVG(SalesAmount)
FROM Sales
GROUP BY Country;

✅ Correct Answer: B

Explanation:
Grouping by Country allows AVG(SalesAmount) to be calculated per country.

9. Which filter removes rows with NULL values in a column?

A.

WHERE SalesAmount = NULL

B.

WHERE SalesAmount <> NULL

C.

WHERE SalesAmount IS NOT NULL

D.

WHERE NOT NULL SalesAmount

✅ Correct Answer: C

Explanation:
SQL uses IS NULL and IS NOT NULL to check for null values.

10. Which SQL pattern is most efficient for analytics queries in Microsoft Fabric?

A. Selecting all columns and filtering later
B. Using SELECT * for simplicity
C. Filtering early and selecting only needed columns
D. Aggregating without grouping

✅ Correct Answer: C

Explanation:
Filtering early and selecting only required columns improves performance by reducing data movement—an important Fabric best practice.