8. Querying & Aggregate Functions

Ensuring You Are in the Right Database

Before writing any query, confirm which database is active. Run:

USE VoidX_Academy;

You can also see the active database in the dropdown at the top-left of the SSMS toolbar. It should show VoidX_Academy.

The SELECT Statement — Reading Data

All data retrieval in SQL begins with SELECT. The basic structure has three parts: which columns you want, which table to look in, and optionally which rows to include.

To retrieve every column from every row in the Student table:

SELECT * FROM Student;

The asterisk means all columns. It is useful for exploration, but in production you should always name the specific columns you need. This reduces the data transferred and makes your code clearer:

SELECT FirstName, LastName, Email
FROM Student;

Filtering Rows with WHERE

You rarely want every row. Use WHERE to return only rows matching a condition.

Find all students in Department 1:

SELECT FirstName, LastName
FROM Student
WHERE DeptID = 1;

Combine multiple conditions with AND. Find students in Department 1 who also have a GPA above 3.0:

SELECT FirstName, LastName, GPA
FROM Student
WHERE DeptID = 1 AND GPA > 3.0;

Use OR when any one of several conditions should match. Find students in either Department 1 or Department 2:

SELECT FirstName, LastName, DeptID
FROM Student
WHERE DeptID = 1 OR DeptID = 2;

When checking the same column against several values, IN is cleaner than multiple ORs:

SELECT FirstName, LastName, DeptID
FROM Student
WHERE DeptID IN (1, 2);

To search for text patterns, use LIKE. The percent sign % is a wildcard meaning any sequence of characters. Find all students whose last name starts with A:

SELECT FirstName, LastName
FROM Student
WHERE LastName LIKE 'A%';

Sorting Results with ORDER BY

Without ORDER BY, SQL Server returns rows in no guaranteed order. Use ORDER BY to control sorting. ASC means ascending (smallest to largest, A to Z). DESC means descending.

Sort students alphabetically by last name:

SELECT FirstName, LastName
FROM Student
ORDER BY LastName ASC;

Sort by GPA, highest to lowest:

SELECT FirstName, LastName, GPA
FROM Student
ORDER BY GPA DESC;

Sort by department first, then by last name within each department:

SELECT FirstName, LastName, DeptID
FROM Student
ORDER BY DeptID ASC, LastName ASC;

Limiting Results with TOP

SQL Server uses TOP to limit how many rows are returned. Place it immediately after SELECT, before the column list. Get the five students with the highest GPAs:

SELECT TOP 5 FirstName, LastName, GPA
FROM Student
ORDER BY GPA DESC;

Note: MySQL and PostgreSQL use LIMIT 5 at the end of the query instead. SQL Server's TOP goes at the beginning. This is one of the key syntax differences between DBMS products.

Aggregate Functions — Calculating Across Rows

Aggregate functions perform a calculation across a group of rows and return a single result. They compress many rows into one number.

Count every student in the table:

SELECT COUNT(*) AS TotalStudents
FROM Student;

The AS keyword gives your result column a readable name called an alias. Without it, the column header would show as a raw expression. Always alias aggregate results.

Find the highest, lowest, and average GPA across all students:

SELECT
  MAX(GPA) AS HighestGPA,
  MIN(GPA) AS LowestGPA,
  AVG(GPA) AS AverageGPA
FROM Student;

COUNT(*) counts all rows including those where columns are NULL. COUNT(column) counts only rows where that specific column is not NULL:

SELECT COUNT(GPA) AS StudentsWithGPA
FROM Student;

This returns only the number of students who have had a GPA assigned — students where GPA is NULL are not counted.

GROUP BY — Breaking Aggregates Down by Category

A single aggregate across the whole table is useful, but usually you want the number broken down by category. How many students are in each department? What is the average GPA per department?

GROUP BY splits the rows into groups first, then applies the aggregate function to each group separately.

Count students per department:

SELECT DeptID, COUNT(*) AS StudentCount
FROM Student
GROUP BY DeptID;

Average GPA per department, sorted highest to lowest:

SELECT DeptID, AVG(GPA) AS AverageGPA
FROM Student
GROUP BY DeptID
ORDER BY AverageGPA DESC;

Critical rule: Any column in your SELECT that is not inside an aggregate function must appear in the GROUP BY clause. If you include FirstName in SELECT but not in GROUP BY, SQL Server returns an error. Only the grouping column and aggregated columns belong in the SELECT of a GROUP BY query.

HAVING — Filtering Groups After Aggregation

WHERE filters individual rows before grouping. But what if you want to filter the groups themselves — for example, show only departments with more than two students?

The count does not exist until after GROUP BY runs, so WHERE cannot access it. Use HAVING instead — it filters after grouping is complete:

SELECT DeptID, COUNT(*) AS StudentCount
FROM Student
GROUP BY DeptID
HAVING COUNT(*) > 2;

The order of execution in any query is: FROM reads the table, WHERE filters rows, GROUP BY groups them, HAVING filters groups, SELECT prepares the output, ORDER BY sorts it. Understanding this order explains why HAVING and WHERE are separate clauses — they run at different points in the process.

Handling NULL Values

NULL represents the absence of a value. It is not zero, it is not an empty string — it is nothing. You cannot compare NULL using = or !=. You must use IS NULL or IS NOT NULL.

Find students who have not been assigned a GPA yet:

SELECT FirstName, LastName
FROM Student
WHERE GPA IS NULL;

Find students who do have a GPA recorded:

SELECT FirstName, LastName, GPA
FROM Student
WHERE GPA IS NOT NULL;