Aggregate Functions · Database Management System

Aggregate Functions

CSC-265 · Semester IV · Database Management System

beginner25 minAvailable

SQLin-browser engine · runs locallyaggregates

Ctrl Enter to run16 lines

Write SQL, then press Run. Result tables, affected-row counts and error messages appear here.

Lab brief

Objective

Use COUNT, SUM, AVG, MIN and MAX with GROUP BY and HAVING to summarize data, and explain how WHERE differs from HAVING.

Theory & concepts

Aggregate functions collapse many rows into one value: COUNT counts, SUM totals, AVG averages, MIN and MAX find extremes. GROUP BY partitions rows into groups so aggregates apply per group. HAVING filters groups after aggregation, while WHERE filters rows before grouping.

Rendering diagram…

Aggregating groups and filtering with WHERE versus HAVING

SELECT Major, COUNT(*) AS n, AVG(GPA) AS avg_gpa
FROM STUDENT
WHERE Age >= 18          -- filter rows first
GROUP BY Major
HAVING COUNT(*) > 1      -- filter groups after
ORDER BY avg_gpa DESC;

Prerequisites

Lesson: Aggregate functions and GROUP BY

Instructions

1Load the starter STUDENT and ENROLLS tables.
2Run a whole-table aggregate: COUNT, AVG, MIN, MAX over GPA.
3Group students by major and summarize each group.
4Use HAVING to keep only groups meeting a condition, and compare with WHERE.
5Answer the practice questions about NULL handling and COUNT(*).

Tasks

Table-wide summary
Compute count, average and max GPA for the whole table.
Per-major summary
Group by major and report student count and average GPA per group.
HAVING filter
Keep only majors with more than one student and explain the result.
NULL experiment
Compare COUNT(*) with COUNT(col) on a column containing NULLs.

Self-check

All aggregate results are numerically correct.
GROUP BY results are correctly formed per group.
The student explains the WHERE vs HAVING distinction with the query's behavior.
The student correctly interprets NULL-sensitive aggregate behavior.

Environment

SQL consolelive resultsstarter schemapractice questions