Advanced SQL Practice: Aggregate Functions, GROUP BY, HAVING, and Nested Subqueries

The Story — Five Questions That Break Beginners

This is a practice-first tutorial: a tiny dataset you can verify by hand, short theory bursts with animated diagrams, then eight graded problems with full solutions — covering aggregate functions, GROUP BY, HAVING, and nested queries from scalar subqueries to correlated EXISTS.

The Playground — Two Tables, Verifiable by Hand

Every problem uses this dataset. It is deliberately small enough that you can compute every answer on paper first — the fastest way to catch a wrong query.

Warm-Up — The Five Aggregate Functions (and the NULL Rule)

An aggregate function collapses many rows into a single value: COUNT, SUM, AVG, MIN, MAX.

Run the warm-up on EMPLOYEES and check every number by hand:

SELECT COUNT(*)          AS total_rows,      -- 7
       COUNT(Commission) AS with_commission, -- 4  (NULLs skipped!)
       SUM(Salary)       AS payroll,         -- 450000
       AVG(Salary)       AS avg_salary,      -- 64285.71
       AVG(Commission)   AS avg_comm,        -- 4000  (16000/4, NOT 16000/7)
       MIN(Salary)       AS lowest,          -- 45000
       MAX(Salary)       AS highest          -- 90000
FROM   EMPLOYEES;

GROUP BY — From One Summary to One Summary Per Group

GROUP BY partitions rows into buckets that share a value, then runs the aggregates once per bucket.

SELECT DeptID, COUNT(*) AS headcount, SUM(Salary) AS payroll
FROM   EMPLOYEES
GROUP BY DeptID;

WHERE vs HAVING — The Pipeline That Explains Everything

The most-asked interview question on this topic has a one-diagram answer. SQL evaluates a query in a fixed logical order — and WHERE runs before groups exist, while HAVING runs after.

Practice Set A — Aggregates, GROUP BY, HAVING

Solve each on paper against the Section 02 data before opening the solution.

-- P2: department name, headcount, average salary
SELECT d.DeptName, COUNT(*) AS headcount, AVG(e.Salary) AS avg_salary
FROM   EMPLOYEES e
JOIN   DEPARTMENTS d ON d.DeptID = e.DeptID
GROUP BY d.DeptName
ORDER BY avg_salary DESC;
-- Engineering 3 76666.67 | Sales 2 60000 | HR 1 45000
-- Veer (NULL dept) drops out via the inner JOIN; Marketing has no rows to join.

-- P3: WHERE prunes rows first, HAVING filters the finished groups
SELECT DeptID, COUNT(*) AS big_earners
FROM   EMPLOYEES
WHERE  Salary >= 50000
GROUP BY DeptID
HAVING COUNT(*) > 1;
-- D1 (Aarav 50000 + Diya 70000) and D2 (all three).
-- Sara (45000) was removed by WHERE before grouping, so D3 never had a chance.

Subqueries — When One Query Isn't Enough

A subquery (nested query) is a complete SELECT inside another statement. The classic need: "Who earns more than the company average?" You cannot write WHERE Salary > AVG(Salary) — the pipeline says aggregates don't exist at WHERE time. So you compute the average in an inner query first:

-- Who earns more than the company average?
SELECT Name, Salary
FROM   EMPLOYEES
WHERE  Salary > (SELECT AVG(Salary) FROM EMPLOYEES);
-- Diya 70000, Kabir 80000, Mira 90000   (average is 64285.71)

Subqueries can sit in three places — each with a different shape of result:

Correlated Subqueries & EXISTS — The Inner Loop

A correlated subquery references the outer row, so conceptually it re-runs for each candidate row. It answers per-row questions like "does this employee earn more than the average of their own department?"

-- Employees earning more than their OWN department's average
SELECT e.Name, e.Salary, e.DeptID
FROM   EMPLOYEES e
WHERE  e.Salary > (SELECT AVG(e2.Salary)
                   FROM   EMPLOYEES e2
                   WHERE  e2.DeptID = e.DeptID);
-- Diya (70000 > 60000), Kabir (80000 > 76666.67), Mira (90000 > 76666.67)
-- Sara equals her average; Veer's NULL DeptID makes the comparison NULL → excluded.

-- EXISTS: departments that employ at least one person earning over 75000
SELECT d.DeptName
FROM   DEPARTMENTS d
WHERE  EXISTS (SELECT 1 FROM EMPLOYEES e
               WHERE e.DeptID = d.DeptID AND e.Salary > 75000);
-- Engineering.  EXISTS stops at the first match — it checks existence, not values.

Practice Set B — Nested Queries (Including the Famous Trap)

-- P4: departments beating the company average
SELECT DeptID, AVG(Salary) AS dept_avg
FROM   EMPLOYEES
GROUP BY DeptID
HAVING AVG(Salary) > (SELECT AVG(Salary) FROM EMPLOYEES);

-- P5: departments having at least one employee (subquery style)
SELECT DeptName FROM DEPARTMENTS
WHERE  DeptID IN (SELECT DeptID FROM EMPLOYEES);

-- P6: average of per-department payrolls (derived table needs an alias!)
SELECT AVG(dept_total) AS avg_dept_payroll
FROM (
    SELECT DeptID, SUM(Salary) AS dept_total
    FROM   EMPLOYEES
    WHERE  DeptID IS NOT NULL
    GROUP BY DeptID
) AS t;                          -- 131666.67

-- P7 (broken): returns NOTHING because the inner list contains a NULL
SELECT DeptName FROM DEPARTMENTS
WHERE  DeptID NOT IN (SELECT DeptID FROM EMPLOYEES);   -- 0 rows!

-- P7 (correct): NOT EXISTS is NULL-safe
SELECT d.DeptName FROM DEPARTMENTS d
WHERE  NOT EXISTS (SELECT 1 FROM EMPLOYEES e
                   WHERE e.DeptID = d.DeptID);       -- Marketing

-- P8: scalar subquery in the SELECT list
SELECT Name, Salary,
       Salary - (SELECT AVG(Salary) FROM EMPLOYEES) AS diff
FROM   EMPLOYEES;

Common Mistakes — The Six That Cost Marks and Outages

Academic vs Industry Perspective

Golden Rules