Advanced SQL (Functions & Grouping)

Introduction

Some questions aren't about individual rows at all — "how many students are there?", "what's the average score?" — they're about the whole table (or group) at once. Aggregate functions compute a single summary value across many rows.

Concept

• COUNT(*) — the number of rows.
• SUM(column) — the total of a numeric column.
• AVG(column) — the average of a numeric column.
• MAX(column) / MIN(column) — the largest / smallest value in a column.

SELECT COUNT(*), AVG(Marks), MAX(Marks) FROM STUDENT;

Explanation

COUNT() counts every row, including ones with NULL values in some column — but SUM(), AVG(), MAX(), and MIN() all ignore NULL values in their specific column when computing the result. Recall Amit's row from Topic 68 (AdmNo 102), inserted without a Marks value — his Marks is NULL. COUNT() still counts his row, but AVG(Marks) skips it entirely rather than treating it as 0 — with only Ravi's real mark to average, AVG(Marks) is not pulled down at all by Amit's missing value.

Note: NULL is not treated as zero anywhere in SQL, including inside aggregate functions — AVG(Marks) divides by the number of NON-NULL marks, not by COUNT(*). Amit's NULL row above is the concrete proof: it counts, but it doesn't drag the average down.

Summary

COUNT, SUM, AVG, MAX, and MIN each collapse a column of many rows into one summary value. COUNT(*) counts all rows including NULLs; the others quietly skip NULL values in their calculation rather than treating them as zero.

1. SUM() and AVG()

These work only on Numeric columns. They ignore NULL values automatically.

2. MAX() and MIN()

These find the highest and lowest values. Unlike SUM, they work on Numbers, Text, and Dates.

• Numbers: Highest/Lowest value.
• Text: Z-A (Max) or A-Z (Min) alphabetically.
• Date: Latest Date (Max) or Oldest Date (Min).

3. The COUNT() Function

/* ==================================================
   SYSTEM SETUP
   ================================================== */
DROP TABLE IF EXISTS STUDENT;
CREATE TABLE STUDENT (Name VARCHAR(20), Marks INT);
INSERT INTO STUDENT VALUES ('Ravi', 90);
INSERT INTO STUDENT VALUES ('Amit', 80);
INSERT INTO STUDENT VALUES ('Neha', 95);
INSERT INTO STUDENT VALUES ('Simran', 75);
INSERT INTO STUDENT VALUES ('Arun', NULL);

/* ==================================================
   YOUR TASK
   Display: COUNT(*), AVG(Marks), MAX(Marks)
   ================================================== */

-- SELECT ... FROM ...

Introduction

Aggregate functions summarize a whole table into one value — but often you want a separate summary per category: total sales per city, average marks per class. That's exactly what GROUP BY does.

Concept

SELECT City, COUNT(*) FROM STUDENT
GROUP BY City
HAVING COUNT(*) > 1;

GROUP BY City splits the table into one group per distinct city value, and the aggregate function (COUNT()) is computed separately within each group. HAVING then filters groups*, based on the aggregated result — keeping only the cities with more than 1 student, in this example.

Explanation

WHERE and HAVING look similar but filter at different stages: WHERE filters individual rows before grouping happens, while HAVING filters entire groups after aggregation. That's also why HAVING can reference COUNT(*) and other aggregates directly, while WHERE cannot — the actual execution order is WHERE → GROUP BY → HAVING.

Note: GROUP BY can be used with no aggregate function at all — in that case, it simply behaves like DISTINCT, returning one row per unique value instead of a calculated summary.

Summary

GROUP BY column splits a table into groups, one per distinct value, so aggregate functions compute per-group instead of table-wide. HAVING filters those groups after aggregation — SQL's actual execution order is WHERE first, then GROUP BY, then HAVING.

Filtering Groups (HAVING)

What if we only want to see cities that have more than 1 student?
We cannot use WHERE because WHERE filters individual rows. We need HAVING to filter groups.

Filtering Groups (HAVING)

What if we only want to see cities that have more than 1 student?
We cannot use WHERE because WHERE filters individual rows. We need HAVING to filter groups.

/* ==================================================
   SYSTEM SETUP
   ================================================== */
DROP TABLE IF EXISTS STUDENT;
CREATE TABLE STUDENT (Name VARCHAR(20), City VARCHAR(20));
INSERT INTO STUDENT VALUES ('Ravi', 'Delhi');
INSERT INTO STUDENT VALUES ('Amit', 'Pune');
INSERT INTO STUDENT VALUES ('Neha', 'Delhi');
INSERT INTO STUDENT VALUES ('Simran', 'Mumbai');
INSERT INTO STUDENT VALUES ('Arun', 'Delhi');
INSERT INTO STUDENT VALUES ('Tina', 'Pune');

/* ==================================================
   YOUR TASK
   1. Group by City
   2. Show Count(*)
   3. Only show cities where Count > 1
   ================================================== */

-- SELECT City, COUNT(*) FROM STUDENT ...

Introduction

Real databases split related data across multiple tables — a Students table and a separate Marks table, linked by a common column. A Join is how a single query combines rows from two tables into one result.

Concept

• Cartesian Product — pairs every row of one table with every row of another, with no matching condition at all.
• Equi-Join — joins two tables using an equality condition on a common column. The common column appears twice in the result — once from each table.
• Natural Join — like an Equi-Join, but automatically matches columns with the exact same name and data type in both tables, and shows the common column only once in the result.

Worked Example

STUDENT already has two rows (AdmNo 101 and 102). Here's a second table, RESULT, linked to it by AdmNo:

CREATE TABLE RESULT (
    AdmNo INT,
    Grade CHAR(1)
);

INSERT INTO RESULT VALUES (101, 'A');
INSERT INTO RESULT VALUES (102, 'B');

Now the three kinds of join, side by side:

-- Cartesian Product: no condition, every STUDENT row paired with every RESULT row
SELECT * FROM STUDENT, RESULT;

-- Equi-Join: only rows where AdmNo actually matches
SELECT * FROM STUDENT, RESULT WHERE STUDENT.AdmNo = RESULT.AdmNo;

-- Natural Join: same result as the Equi-Join, matched automatically
SELECT * FROM STUDENT NATURAL JOIN RESULT;

STUDENT has 2 rows and RESULT has 2 rows, so the Cartesian Product returns 2 × 2 = 4 rows — including nonsense pairings like Ravi (AdmNo 101) shown next to Amit's grade (AdmNo 102). The Equi-Join's WHERE condition cuts that down to only the 2 rows where AdmNo genuinely matches on both sides, and because it's written as SELECT *, AdmNo shows up twice in each result row — once from STUDENT, once from RESULT. The Natural Join returns the same 2 correct rows, but AdmNo appears only once, since it matched STUDENT.AdmNo and RESULT.AdmNo automatically without being told to.

Explanation

The Cartesian Product is almost never what you actually want in a real query — it's the "what happens if you forget the join condition" result, useful mainly to understand why a proper join condition matters: without one, SQL has no way to know which rows from each table actually belong together.

Note: Equi-Join shows the shared column twice (once per table); Natural Join shows it once, automatically — that single detail is the exam-tested distinction between the two, and you can see it directly in the Worked Example above.

Summary

A Cartesian Product pairs every row with every row (no condition, rarely useful on its own). An Equi-Join matches rows on an equality condition, showing the shared column twice. A Natural Join does the same automatically by matching identically-named columns, showing the shared column only once.