📊 Databases

SQL Functions, Grouping & Joins

SQL फंक्शन, ग्रुपिंग और जॉइन

⏱ 3 hr3 topicsInteractive
🎯 By the end: You can summarise data with aggregate functions, group and filter groups with GROUP BY/HAVING, and combine two tables with joins.

Plain SELECT gives you rows; this chapter lets you summarise them (totals, averages), group them (per class, per city), and combine data from two tables. Each idea has a live SQL trainer so you can see real results.

1Aggregate functions

Aggregate functions work on a whole column of values and return a single summary value:

FunctionReturns
COUNT()Number of rows/values
SUM()Total of a numeric column
AVG()Average
MAX() / MIN()Largest / smallest value

Try them on the student table — edit and Run:

Aggregate functions▶ runs real SQL
🗃️ student(roll, name, class, marks, city) — 5 rows
query.sql
Result
Click Run to execute.
Key points
  • Aggregate functions summarise a column into one value: COUNT, SUM, AVG, MAX, MIN.
  • COUNT(*) counts rows; AVG/SUM work on numeric columns.
  • Use AS to give the result column a readable name (an alias).

2GROUP BY and HAVING

GROUP BY splits rows into groups and applies an aggregate to each group — e.g. the average marks per class. HAVING then filters those groups (like WHERE, but for groups).

SELECT class, AVG(marks)
FROM student
GROUP BY class
HAVING AVG(marks) > 80;
WHERE vs HAVING: WHERE filters individual rows before grouping; HAVING filters the groups after aggregation. Use HAVING when your condition involves an aggregate like AVG() or COUNT().

Run it and change the grouping column or the HAVING condition:

GROUP BY + HAVING▶ runs real SQL
🗃️ student(roll, name, class, marks, city) — grouped per class
query.sql
Result
Click Run to execute.
Key points
  • GROUP BY groups rows and applies an aggregate to each group (e.g. AVG per class).
  • HAVING filters groups after aggregation; WHERE filters rows before grouping.
  • Use HAVING (not WHERE) when the condition uses an aggregate like AVG() or COUNT().

3Joins: combining two tables

A join combines rows from two tables. Three kinds in the syllabus:

  • Cartesian product (CROSS JOIN) — every row of A paired with every row of B (often too many rows; rarely what you want alone).
  • Equi-join — a Cartesian product filtered with a WHERE that matches a common column (e.g. WHERE s.class = t.class). This is the useful one.
  • Natural join — automatically joins on the columns that share a name, keeping just one copy of them.

The trainer has a student table and a teacher table sharing a class column. Run the equi-join, then try removing the WHERE to see the Cartesian product:

Equi-join: student + teacher▶ runs real SQL
🗃️ student(roll, name, class, marks)  ·  teacher(class, teacher) — joined on class
query.sql
Result
Click Run to execute.
Key points
  • Cartesian product (CROSS JOIN) pairs every row of A with every row of B.
  • Equi-join = Cartesian product filtered by a matching condition (WHERE a.col = b.col) — the common case.
  • Natural join auto-matches columns with the same name and keeps one copy.

★ Practical: summarise and combine

Using the trainers above:

  1. Find the highest and lowest marks in the student table with MAX and MIN.
  2. Show the number of students in each city (GROUP BY city, COUNT).
  3. Show only the cities that have more than one student (HAVING COUNT(*) > 1).
  4. Run the equi-join, then delete the WHERE line and observe the Cartesian product's extra rows.

Ready for the chapter test?

Answer 5 questions. Score 60% or more to mark this chapter complete.

Start the test →

💡 Log in to save your progress and earn the certificate.

← Previous
SQL: DDL, DML & Queries
Next →
Python ↔ SQL Connectivity