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
WHEREclause can never contain an aggregate function likeAVG()directly - that is exactly whatHAVINGexists 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.
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.