home
0%
Progress
StudentPortal › Class XII - CS › Unit 3
CBSE Code 083 | Unit 3: Database Management & Python Connectivity

02. SQL Joins, GROUP BY & HAVING

Cartesian Product, Equi-Join, Natural Join, MIN/MAX/SUM/AVG/COUNT, and filtering with HAVING.

Unit 3: Database Management & Python Connectivity

Chapter 2: SQL Joins, GROUP BY & HAVING

1. Aggregate (Summary) Functions

Aggregate functions perform a calculation across a set of rows and return a single summary value.

Function Purpose
COUNT() Counts the number of rows (or non-NULL values in a column)
SUM() Adds up all numeric values in a column
AVG() Calculates the average of numeric values
MIN() Finds the smallest value
MAX() Finds the largest value

Consider a table Marks(RollNo, Subject, Score):

SELECT COUNT(*) FROM Marks;              -- total number of rows
SELECT AVG(Score) FROM Marks;             -- average score across all rows
SELECT MAX(Score), MIN(Score) FROM Marks; -- highest and lowest score

2. GROUP BY

GROUP BY arranges identical data into groups, so an aggregate function can be applied per group instead of across the whole table.

-- Average score obtained in EACH subject
SELECT Subject, AVG(Score) FROM Marks GROUP BY Subject;
Subject AVG(Score)
CS 82.5
Maths 76.0
Physics 88.0

3. HAVING vs WHERE

Both filter rows, but at different stages:

WHERE HAVING
Applies to Individual rows, before grouping Groups, after GROUP BY has been applied
Can use aggregate functions? No Yes
-- WHERE filters rows first, then groups what remains
SELECT Subject, AVG(Score) FROM Marks
WHERE Score > 0
GROUP BY Subject;

-- HAVING filters the already-formed groups
SELECT Subject, AVG(Score) FROM Marks
GROUP BY Subject
HAVING AVG(Score) > 80;      -- only show subjects averaging above 80

A WHERE clause can never contain an aggregate function like AVG() directly - that is exactly what HAVING exists for.


4. Combining Data from Multiple Tables

Cartesian Product

When two tables are listed together in FROM with no condition, SQL combines every row of the first table with every row of the second table. If Table A has 3 rows and Table B has 4 rows, the Cartesian Product has 3 x 4 = 12 rows - most of which are meaningless.

CARTESIAN PRODUCT -> EQUI-JOIN (matched cells only) Dept.DeptId -> D1 D2 D3 Emp.DeptId D1 (Aman) D2 (Bina) D1 (Chirag) Green cells = WHERE Emp.DeptId = Dept.DeptId (the Equi-Join result)

Equi-Join

An Equi-Join filters the Cartesian product using an equality (=) condition on a common column, keeping only the meaningful, matched combinations.

-- Emp(EmpId, Name, DeptId)   and   Dept(DeptId, DeptName)
SELECT Emp.Name, Dept.DeptName
FROM Emp, Dept
WHERE Emp.DeptId = Dept.DeptId;

Natural Join

A Natural Join automatically matches and joins tables based on all columns that share the same name in both tables - you don't need to write the join condition explicitly, as long as the common column names match.

SELECT Name, DeptName FROM Emp NATURAL JOIN Dept;

5. Board Exam Focus Questions

Q1. Write a query to display the number of employees and the maximum salary in each department, from Emp(EmpId, Name, DeptId, Salary), but only for departments having more than 2 employees.

SELECT DeptId, COUNT(*), MAX(Salary)
FROM Emp
GROUP BY DeptId
HAVING COUNT(*) > 2;

Q2. Why can't the condition AVG(Salary) > 50000 be written inside a WHERE clause? Answer: WHERE filters individual rows before any grouping/aggregation takes place, so at that stage an aggregate value like AVG(Salary) for a group doesn't exist yet. HAVING runs after GROUP BY has formed the groups, which is when aggregate functions can be evaluated and filtered.

Q3. Table Emp has 5 rows and table Dept has 3 rows. How many rows will SELECT * FROM Emp, Dept; (with no WHERE clause) return? Answer: 5 x 3 = 15 rows - this is the Cartesian Product, which pairs every row of Emp with every row of Dept.

Entity Details

Create New Item

help

Submit Technical Query

Have a question or run into an issue? Describe it below, upload an optional screenshot, and our engineering team will answer it!

image Attach image (optional)

Submit Feedback

build Free Developer Utility Free Tool
gavel

Privacy & Legal Disclaimer

1. Client-Side Browser Processing

All utility tools on DeepEngineerHub (including Image to PDF, Text Formatters, JSON Converters, and Encryptors) execute 100% locally within your client browser using WebAssembly and JavaScript. No uploaded images, text, or documents are transmitted, collected, or stored on remote servers.

2. Limitation of Liability ("As-Is" Provision)

Tools and services are provided free of charge for convenience and educational purposes "as-is" without warranties of any kind. DeepEngineerHub shall not be held liable for any data loss, formatting inconsistencies, or indirect damages resulting from tool usage.

3. Open Source & Third-Party Software

Certain utilities utilize open-source client libraries (such as jsPDF, Mermaid.js, Pyodide) licensed under MIT, Apache, or BSD open licenses. All intellectual property remains with their respective copyright holders.