SQL Query Filtering, Sorting, and Aggregation

SQL Query Filtering, Sorting, and Aggregation – DP-900 Complete Guide

Why SQL Query Filtering, Sorting, and Aggregation Matter

SQL is the universal language for interacting with relational databases. Whether you are working with Azure SQL Database, Azure SQL Managed Instance, or Azure Database for PostgreSQL/MySQL, understanding how to filter, sort, and aggregate data is fundamental. On the DP-900: Microsoft Azure Data Fundamentals exam, you will encounter questions that test your ability to recognize correct SQL syntax and understand the purpose of key clauses. Mastering these concepts not only helps you pass the exam but also builds the foundation for every data-related role in the cloud.

What Are Filtering, Sorting, and Aggregation?

1. Filtering (WHERE Clause)
Filtering allows you to retrieve only the rows that meet specific conditions. Instead of returning every row in a table, you use the WHERE clause to narrow results.

Example:
SELECT FirstName, LastName, City FROM Customers WHERE City = 'Seattle';

This query returns only customers located in Seattle. Common comparison operators used with WHERE include:
- = (equal to)
- <> or != (not equal to)
- >, <, >=, <= (greater than, less than, etc.)
- BETWEEN (range of values)
- LIKE (pattern matching with wildcards such as % and _)
- IN (matches any value in a list)
- IS NULL / IS NOT NULL (checks for null values)

You can combine multiple conditions using AND, OR, and NOT logical operators.

Example with multiple conditions:
SELECT * FROM Orders WHERE OrderDate BETWEEN '2024-01-01' AND '2024-06-30' AND Status = 'Shipped';

2. Sorting (ORDER BY Clause)
Sorting organizes the result set in a specific order. By default, SQL does not guarantee any particular order of rows unless you explicitly use ORDER BY.

Example:
SELECT ProductName, Price FROM Products ORDER BY Price ASC;

- ASC (ascending) – smallest to largest, A to Z (this is the default)
- DESC (descending) – largest to smallest, Z to A

You can sort by multiple columns:
SELECT FirstName, LastName, City FROM Customers ORDER BY City ASC, LastName DESC;

This sorts first by City in ascending order, then within each city by LastName in descending order.

3. Aggregation (Aggregate Functions and GROUP BY)
Aggregation lets you perform calculations across multiple rows and return a single summarized value. The most common aggregate functions are:

- COUNT() – returns the number of rows
- SUM() – returns the total of a numeric column
- AVG() – returns the average value
- MIN() – returns the smallest value
- MAX() – returns the largest value

Example:
SELECT COUNT(*) AS TotalOrders FROM Orders;

When you want to aggregate data by categories or groups, use the GROUP BY clause:

Example:
SELECT City, COUNT(*) AS CustomerCount FROM Customers GROUP BY City;

This returns the number of customers in each city.

Filtering Groups with HAVING
The HAVING clause filters the results after aggregation, unlike WHERE which filters before aggregation. This is a critical distinction for the exam.

Example:
SELECT City, COUNT(*) AS CustomerCount FROM Customers GROUP BY City HAVING COUNT(*) > 10;

This returns only cities that have more than 10 customers.

How It All Works Together – The Logical Order of SQL Clauses

Understanding the order in which SQL processes clauses is essential:

1. FROM – identifies the table(s)
2. WHERE – filters individual rows
3. GROUP BY – groups filtered rows
4. HAVING – filters groups
5. SELECT – determines which columns/expressions to return
6. ORDER BY – sorts the final result set

While you write SELECT first in the query, the database engine processes FROM and WHERE before SELECT. This explains why you cannot use a column alias defined in SELECT inside a WHERE clause.

Key Differences to Remember

WHERE vs. HAVING:
- WHERE filters rows before grouping.
- HAVING filters groups after aggregation.
- WHERE cannot contain aggregate functions; HAVING can.

COUNT(*) vs. COUNT(column):
- COUNT(*) counts all rows, including those with NULL values.
- COUNT(column) counts only non-NULL values in that column.

Real-World Azure Context

On Azure, these SQL concepts apply across multiple services:
- Azure SQL Database – fully managed relational database using T-SQL
- Azure SQL Managed Instance – near 100% SQL Server compatibility
- Azure Database for PostgreSQL / MySQL – uses standard SQL with minor dialect differences
- Azure Synapse Analytics (SQL pools) – uses T-SQL for analytical queries at scale

Regardless of the service, the core SQL filtering, sorting, and aggregation syntax remains consistent.

---

Exam Tips: Answering Questions on SQL Query Filtering, Sorting, and Aggregation

Tip 1: Know the Clause Order
Expect questions that present SQL queries and ask you to identify errors or choose the correct clause order. Remember: FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY.

Tip 2: Distinguish WHERE from HAVING
This is one of the most commonly tested concepts. If a question asks you to filter based on an aggregate result (e.g., show only groups with a count greater than 5), the answer is HAVING, not WHERE.

Tip 3: Recognize Aggregate Functions
Be able to identify COUNT, SUM, AVG, MIN, and MAX and know that they operate on groups of rows. If GROUP BY is missing when selecting both a regular column and an aggregate, the query is likely incorrect.

Tip 4: Default Sort Order
If a query uses ORDER BY without specifying ASC or DESC, the default is ASC (ascending). Exam questions may test whether you know this default behavior.

Tip 5: Watch for NULL Handling
Questions may test whether you understand that NULL values require IS NULL or IS NOT NULL rather than = NULL. Also remember that most aggregate functions ignore NULLs (except COUNT(*)).

Tip 6: Read the Entire Query Before Answering
Some questions provide a complete SQL statement and ask what the output will be. Trace through the query logically: first apply WHERE, then GROUP BY, then HAVING, then SELECT, then ORDER BY.

Tip 7: Understand Aliases
Column aliases (defined with AS) can be used in ORDER BY but typically not in WHERE or HAVING in standard SQL. This nuance occasionally appears in exam questions.

Tip 8: Practice with Simple Scenarios
The DP-900 exam tests foundational knowledge. You do not need to write complex joins or subqueries for this section. Focus on single-table queries that use SELECT, WHERE, ORDER BY, GROUP BY, HAVING, and aggregate functions.

Tip 9: Eliminate Wrong Answers
If you see an answer option that places HAVING before GROUP BY, or uses an aggregate function inside a WHERE clause, you can immediately eliminate it.

Tip 10: Link SQL Concepts to Azure Services
Some questions may ask which Azure service supports standard SQL queries. Remember that Azure SQL Database, Azure SQL Managed Instance, and the dedicated SQL pool in Azure Synapse Analytics all use T-SQL, while Azure Database for PostgreSQL and MySQL use their respective SQL dialects. All support filtering, sorting, and aggregation.

Summary
Filtering (WHERE), sorting (ORDER BY), and aggregation (GROUP BY with aggregate functions and HAVING) are core SQL skills tested on the DP-900 exam. Understand the logical processing order of SQL clauses, the difference between WHERE and HAVING, and the behavior of aggregate functions. With these concepts firmly in place, you will be well-prepared to answer any related exam question confidently.