You know how to **query and filter** data ā now let's level up! This chapter answers the BIG questions: **How do you summarise data by groups? How do you combine data from multiple tables?** Master this chapter and you'll write SQL like a real data analyst! š
> [!TIP]
> **How to use these notes:** This chapter has two power sections ā **GROUP BY** (summarising groups of rows) and **Joins** (combining data from multiple tables). Board Exam Tips appear throughout ā don't skip them! Focus especially on **Section 12.3.2 (HAVING clause)** and **Section 12.4.3 (Equi-Join)** ā examiner favourites every year!
---
## 12.1 š Introduction
In Chapter 11 we learnt to query a **single table**. But real-world databases almost always need answers like:
- *"How many employees are in each department?"*
- *"What is the average salary per city?"*
- *"Show me each employee's name along with their department name from a separate table."*
These questions need two new powerful tools:
```mermaid
graph LR
G["š GROUP BY\nSummarise rows\ninto groups"]
J["š Joins\nCombine data from\nmultiple tables"]
POWER["šŖ Real-World\nSQL Power!"]
G --> POWER
J --> POWER
style G fill:#2196F3,color:#fff
style J fill:#9C27B0,color:#fff
style POWER fill:#4CAF50,color:#fff
```
> Think of it this way: Chapter 11 was like **reading individual student report cards**. Chapter 13 lets you **summarise the whole class** (GROUP BY) and **combine the report card with the attendance register** (Joins)!
---
## 12.2 āļø Types of SQL Functions
Before diving into GROUP BY, it helps to understand the two broad categories of SQL functions:
::: grid
::: card š¢ | Single-Row Functions | Operate on ONE row at a time and return one result per row | UPPER(), ROUND(), YEAR(), LENGTH() ā we covered these in Ch 11
::: card š | Multi-Row (Aggregate) Functions | Operate on a GROUP of rows and return ONE summary result | COUNT(), SUM(), AVG(), MAX(), MIN() ā the stars of this chapter!
:::
```mermaid
graph TD
F["SQL FUNCTIONS"]
F1["š¢ Single-Row Functions\nOne input row ā One output row\nOutput has same number of rows as input"]
F2["š Aggregate Functions\nMany input rows ā One output value\nCollapses many rows into a summary"]
F --> F1
F --> F2
F1 --> EX1["UPPER(Name)\nUPPER('priya') ā 'PRIYA'\nfor EVERY row"]
F2 --> EX2["AVG(Salary)\nAll salaries ā one average\nfor the WHOLE group"]
style F fill:#FF9800,color:#fff
style F1 fill:#2196F3,color:#fff
style F2 fill:#9C27B0,color:#fff
```
**Aggregate Functions ā Quick Reference:**
| Function | Purpose | Ignores NULL? |
| :--- | :--- | :--- |
| `COUNT(*)` | Count all rows | No (counts everything) |
| `COUNT(col)` | Count non-NULL values | Yes |
| `SUM(col)` | Total of all values | Yes |
| `AVG(col)` | Average of all values | Yes |
| `MAX(col)` | Largest value | Yes |
| `MIN(col)` | Smallest value | Yes |
> [!IMPORTANT]
> **Board Exam Tip**
> "What is the difference between single-row and aggregate functions?" ā **2-mark** question.
> Answer: **Single-row functions** process one row at a time and return one result per row (same number of output rows as input rows). **Aggregate functions** process a group of rows and return a single summary value for the entire group (e.g., SUM, AVG, COUNT).
---
## 12.3 š Grouping Result ā GROUP BY
Without GROUP BY, aggregate functions summarise the **entire table** into one row. With GROUP BY, they summarise **each group separately** ā giving you one result row per group!
**The Problem Without GROUP BY:**
```sql
-- without_group_by.sql
SELECT COUNT(*) FROM Employee;
-- Output: 6 (total employees ā just ONE number, not very useful!)
```
**The Solution With GROUP BY:**
```sql
-- with_group_by.sql
SELECT Dept, COUNT(*) AS TotalEmployees
FROM Employee
GROUP BY Dept;
```
**Output:**
| Dept | TotalEmployees |
| :--- | :--- |
| Sales | 2 |
| IT | 2 |
| HR | 2 |
```mermaid
graph TD
ALL["š All 6 Employee Rows"]
GRP["GROUP BY Dept"]
G1["š¢ Sales Group\nPriya, Anjali\nCOUNT = 2"]
G2["š» IT Group\nArjun, Rohan\nCOUNT = 2"]
G3["š„ HR Group\nSita, Vikram\nCOUNT = 2"]
ALL --> GRP
GRP --> G1
GRP --> G2
GRP --> G3
style GRP fill:#FF9800,color:#fff
style G1 fill:#4CAF50,color:#fff
style G2 fill:#2196F3,color:#fff
style G3 fill:#9C27B0,color:#fff
```
**Our Sample Employee Table (used throughout this chapter):**
| EmpId | Name | Dept | Salary | DOJ | City |
| :--- | :--- | :--- | :--- | :--- | :--- |
| 101 | Priya Sharma | Sales | 45000 | 2022-03-15 | Delhi |
| 102 | Arjun Kumar | IT | 62000 | 2021-07-01 | Mumbai |
| 103 | Sita Roy | HR | 38000 | 2023-01-20 | Delhi |
| 104 | Rohan Das | IT | 71000 | 2020-11-05 | Bangalore |
| 105 | Anjali Gupta | Sales | NULL | 2023-06-10 | Mumbai |
| 106 | Vikram Singh | HR | 41000 | 2022-09-25 | Delhi |
**More GROUP BY Examples:**
```sql
-- group_by_examples.sql
-- Average salary per department:
SELECT Dept, AVG(Salary) AS AvgSalary
FROM Employee
GROUP BY Dept;
```
| Dept | AvgSalary |
| :--- | :--- |
| Sales | 45000.00 |
| IT | 66500.00 |
| HR | 39500.00 |
> Notice: AVG(Salary) for Sales is 45000, not NULL-affected ā because AVG ignores NULL. Only Priya's 45000 counts for Sales (Anjali is NULL).
```sql
-- Count employees per city:
SELECT City, COUNT(*) AS EmpCount
FROM Employee
GROUP BY City;
```
| City | EmpCount |
| :--- | :--- |
| Delhi | 3 |
| Mumbai | 2 |
| Bangalore | 1 |
**General GROUP BY Syntax:**
```sql
SELECT column, aggregate_function(column)
FROM table_name
[WHERE condition]
GROUP BY column
[HAVING condition]
[ORDER BY column];
```
> [!WARNING]
> **The Golden Rule of GROUP BY!**
> Any column in the SELECT list that is **NOT** inside an aggregate function **MUST** appear in the GROUP BY clause. Writing `SELECT Dept, Name, COUNT(*) FROM Employee GROUP BY Dept;` is an error ā Name is not aggregated and not in GROUP BY. Either add Name to GROUP BY or remove it from SELECT!
> [!IMPORTANT]
> **Board Exam Tip**
> "Write a query to display the number of employees in each department." ā **2-mark** question asked every year!
> Answer: `SELECT Dept, COUNT(*) AS TotalEmployees FROM Employee GROUP BY Dept;`
---
### 12.3.1 Nested Groups ā Grouping on Multiple Columns šŖ
You can GROUP BY **more than one column** to create finer sub-groups ā like grouping first by Department, then by City within each department.
**Real-World Need:** *"How many employees are in each city, within each department?"*
```sql
-- nested_groups.sql
SELECT Dept, City, COUNT(*) AS EmpCount
FROM Employee
GROUP BY Dept, City;
```
**Output:**
| Dept | City | EmpCount |
| :--- | :--- | :--- |
| Sales | Delhi | 1 |
| Sales | Mumbai | 1 |
| IT | Mumbai | 1 |
| IT | Bangalore | 1 |
| HR | Delhi | 2 |
```mermaid
graph TD
ALL["š All 6 Employees"]
D1["š¼ Sales Dept"]
D2["š» IT Dept"]
D3["š„ HR Dept"]
S1["Delhi ā 1 emp\n(Priya)"]
S2["Mumbai ā 1 emp\n(Anjali)"]
I1["Mumbai ā 1 emp\n(Arjun)"]
I2["Bangalore ā 1 emp\n(Rohan)"]
H1["Delhi ā 2 emps\n(Sita, Vikram)"]
ALL --> D1
ALL --> D2
ALL --> D3
D1 --> S1
D1 --> S2
D2 --> I1
D2 --> I2
D3 --> H1
style D1 fill:#4CAF50,color:#fff
style D2 fill:#2196F3,color:#fff
style D3 fill:#9C27B0,color:#fff
```
**Another Example ā Total salary per department per city:**
```sql
-- nested_group_salary.sql
SELECT Dept, City, SUM(Salary) AS TotalSalary
FROM Employee
GROUP BY Dept, City;
```
> [!NOTE]
> **Order in GROUP BY matters for display! š§ **
> `GROUP BY Dept, City` creates groups of (Dept, City) combinations. The order in GROUP BY determines how rows are sub-divided ā first by Dept, then within each Dept by City. This is exactly like an Excel pivot table!
> [!IMPORTANT]
> **Board Exam Tip**
> "Write a query to display total salary for each department in each city." ā **3-mark** question!
> Answer: `SELECT Dept, City, SUM(Salary) AS TotalSalary FROM Employee GROUP BY Dept, City;`
---
### 12.3.2 Placing Conditions on Groups ā HAVING Clause šÆ
**WHERE** filters **individual rows** before grouping. **HAVING** filters **groups** after grouping. You use HAVING when you want to show only groups that meet a certain condition.
**Real-World Need:** *"Show only departments where the average salary is more than ā¹50,000."*
```sql
-- having_clause.sql
SELECT Dept, AVG(Salary) AS AvgSalary
FROM Employee
GROUP BY Dept
HAVING AVG(Salary) > 50000;
```
**Output (only IT qualifies):**
| Dept | AvgSalary |
| :--- | :--- |
| IT | 66500.00 |
```mermaid
graph LR
DATA["All Rows"]
WHERE_STEP["WHERE\n(filters individual rows\nbefore grouping)"]
GROUP_STEP["GROUP BY\n(creates groups)"]
AGG_STEP["Aggregate\nFunctions\n(SUM, AVG etc.)"]
HAVING_STEP["HAVING\n(filters groups\nafter grouping)"]
RESULT["Final Result"]
DATA --> WHERE_STEP
WHERE_STEP --> GROUP_STEP
GROUP_STEP --> AGG_STEP
AGG_STEP --> HAVING_STEP
HAVING_STEP --> RESULT
style WHERE_STEP fill:#FF9800,color:#fff
style HAVING_STEP fill:#F44336,color:#fff
style GROUP_STEP fill:#2196F3,color:#fff
```
**WHERE vs HAVING ā The Critical Difference:**
| Feature | WHERE | HAVING |
| :--- | :--- | :--- |
| **What it filters** | Individual rows | Groups (after GROUP BY) |
| **When it runs** | BEFORE grouping | AFTER grouping |
| **Can use aggregates?** | ā No (`WHERE AVG(Salary) > 5000` is illegal) | ā
Yes (`HAVING AVG(Salary) > 5000` is correct) |
| **Used with** | Any SELECT statement | Only with GROUP BY |
**Using WHERE and HAVING Together:**
```sql
-- where_and_having.sql
-- Show departments where average salary > 40000,
-- but only considering employees who joined before 2023
SELECT Dept, AVG(Salary) AS AvgSalary
FROM Employee
WHERE YEAR(DOJ) < 2023
GROUP BY Dept
HAVING AVG(Salary) > 40000;
```
**Step-by-step:**
1. WHERE first removes Sita Roy (2023) and Anjali Gupta (2023) ā 4 rows remain
2. GROUP BY groups the remaining 4 rows by Dept
3. HAVING keeps only groups with AVG Salary > 40000
> [!IMPORTANT]
> **Board Exam Tip**
> "Differentiate between WHERE and HAVING clause." ā **2-mark** question, asked almost every year!
> Answer: **WHERE** filters individual rows **before** grouping and cannot use aggregate functions. **HAVING** filters groups **after** GROUP BY is applied and CAN use aggregate functions. You cannot replace one with the other ā they serve different purposes.
**More HAVING Examples:**
```sql
-- more_having_examples.sql
-- Departments with more than 1 employee:
SELECT Dept, COUNT(*) AS Total
FROM Employee
GROUP BY Dept
HAVING COUNT(*) > 1;
-- Cities where maximum salary exceeds 60000:
SELECT City, MAX(Salary) AS MaxSal
FROM Employee
GROUP BY City
HAVING MAX(Salary) > 60000;
```
---
### 12.3.3 Non-Group Expressions with GROUP BY š
When using GROUP BY, the SELECT list must follow strict rules ā any column that is **not** in an aggregate function must be in the GROUP BY clause. This avoids ambiguity about which value to show.
**Legal ā column in GROUP BY:**
```sql
-- legal_group_by.sql
SELECT Dept, COUNT(*) AS Total
FROM Employee
GROUP BY Dept;
-- ā
Legal: Dept is in GROUP BY
```
**Illegal ā column NOT in GROUP BY:**
```sql
-- illegal_group_by.sql
SELECT Dept, Name, COUNT(*) AS Total
FROM Employee
GROUP BY Dept;
-- ā ILLEGAL: Name is not in GROUP BY and not aggregated!
-- MySQL error: 'Name' is not in GROUP BY clause
```
**Why is it illegal?** If Sales has Priya and Anjali, which Name should MySQL show in the output row for Sales? It's ambiguous ā MySQL has no way to know!
**The fix ā add Name to GROUP BY (creates finer groups):**
```sql
-- fix_non_group.sql
SELECT Dept, Name, COUNT(*) AS Total
FROM Employee
GROUP BY Dept, Name;
```
> [!NOTE]
> **MySQL's ONLY_FULL_GROUP_BY Mode! š§ **
> Older versions of MySQL would sometimes allow non-group expressions and return an arbitrary value (any row from the group). Modern MySQL (from version 5.7+) runs in ONLY_FULL_GROUP_BY mode by default, which correctly rejects these ambiguous queries with an error. Always follow the rule: every non-aggregated SELECT column must be in GROUP BY!
---
## 12.4 š Joins
So far, all queries used a **single table**. But real databases split data across multiple tables ā a Join combines rows from two (or more) tables based on a related column between them.
> **Real-World Analogy:** Imagine your school keeps student names in one register and their marks in another. To see "Priya's marks", someone needs to **join** the two registers using the Roll Number as the common link ā that's exactly what SQL Joins do!
**Our Two Sample Tables for Join Examples:**
**Table: Employee**
| EmpId | Name | DeptId | Salary | City |
| :--- | :--- | :--- | :--- | :--- |
| 101 | Priya Sharma | D01 | 45000 | Delhi |
| 102 | Arjun Kumar | D02 | 62000 | Mumbai |
| 103 | Sita Roy | D03 | 38000 | Delhi |
| 104 | Rohan Das | D02 | 71000 | Bangalore |
| 105 | Anjali Gupta | D01 | NULL | Mumbai |
| 106 | Vikram Singh | D03 | 41000 | Delhi |
**Table: Department**
| DeptId | DeptName | Location |
| :--- | :--- | :--- |
| D01 | Sales | New Delhi |
| D02 | IT | Mumbai |
| D03 | HR | Kolkata |
| D04 | Finance | Chennai |
---
### 12.4.1 Cartesian Product š
A **Cartesian Product** (also called a Cross Join) combines **every row of the first table with every row of the second table** ā producing all possible combinations.
```sql
-- cartesian_product.sql
SELECT * FROM Employee, Department;
-- OR equivalently:
SELECT * FROM Employee CROSS JOIN Department;
```
**Result:** 6 rows Ć 4 rows = **24 rows** ā every employee paired with every department!
```mermaid
graph LR
E["Employee\n6 rows"]
D["Department\n4 rows"]
CP["Cartesian Product\n6 Ć 4 = 24 rows\n(Every combination!)"]
E --> CP
D --> CP
style CP fill:#F44336,color:#fff
```
**Sample Output (showing just a few rows):**
| EmpId | Name | DeptId | DeptId | DeptName |
| :--- | :--- | :--- | :--- | :--- |
| 101 | Priya | D01 | D01 | Sales |
| 101 | Priya | D01 | D02 | IT |
| 101 | Priya | D01 | D03 | HR |
| 101 | Priya | D01 | D04 | Finance |
| 102 | Arjun | D02 | D01 | Sales |
| ... | ... | ... | ... | ... |
> [!WARNING]
> **Cartesian Product is almost always a mistake!**
> In most real queries, you don't want every possible combination ā you want **matching** rows. A Cartesian Product of two large tables produces a massive, meaningless result. It's mainly shown to explain WHY you need a proper Join condition. Always add a WHERE or JOIN condition to link tables meaningfully!
> [!IMPORTANT]
> **Board Exam Tip**
> "What is a Cartesian Product in SQL?" ā **2-mark** question.
> Answer: A **Cartesian Product** results when two tables are joined without a joining condition. It produces every possible combination of rows from both tables. If Table A has m rows and Table B has n rows, the Cartesian Product has m Ć n rows.
---
### 12.4.2 Table Aliases š·ļø
When joining tables, column names from different tables may conflict (both tables might have a `DeptId` column). **Table Aliases** give tables short, temporary names to:
1. Distinguish columns from different tables
2. Make long queries shorter and more readable
```sql
-- table_aliases.sql
SELECT E.Name, E.Salary, D.DeptName
FROM Employee E, Department D
WHERE E.DeptId = D.DeptId;
-- 'E' is an alias for Employee, 'D' is an alias for Department
```
**Syntax Rules:**
```
FROM Employee E -- 'E' is the alias (no AS needed for table aliases)
FROM Employee AS E -- Also valid with AS
```
| Without Alias | With Alias |
| :--- | :--- |
| `Employee.Name` | `E.Name` |
| `Department.DeptName` | `D.DeptName` |
| `Employee.DeptId = Department.DeptId` | `E.DeptId = D.DeptId` |
> [!NOTE]
> **Table Aliases save your sanity! š§ **
> When joining 3+ tables, writing full table names every time makes queries unreadable. Short aliases like E, D, S instantly tell you which table a column comes from. It's a best practice even when not strictly necessary!
---
### 12.4.3 Equi-Join and Natural Join š¤
#### Equi-Join (The Most Important Join!)
An **Equi-Join** links two tables using an equality condition (`=`) on a common column ā typically a Foreign Key in one table matching the Primary Key in another.
```sql
-- equi_join.sql
SELECT E.EmpId, E.Name, E.Salary, D.DeptName
FROM Employee E, Department D
WHERE E.DeptId = D.DeptId;
```
**Output (only rows where DeptId matches in both tables):**
| EmpId | Name | Salary | DeptName |
| :--- | :--- | :--- | :--- |
| 101 | Priya Sharma | 45000 | Sales |
| 102 | Arjun Kumar | 62000 | IT |
| 103 | Sita Roy | 38000 | HR |
| 104 | Rohan Das | 71000 | IT |
| 105 | Anjali Gupta | NULL | Sales |
| 106 | Vikram Singh | 41000 | HR |
```mermaid
graph LR
E["Employee Table\n(has DeptId FK)"]
D["Department Table\n(has DeptId PK)"]
MATCH["WHERE E.DeptId = D.DeptId\nš Matching rows joined!"]
RESULT["š Combined Result\nName + Salary + DeptName\nin one query!"]
E --> MATCH
D --> MATCH
MATCH --> RESULT
style MATCH fill:#4CAF50,color:#fff
style RESULT fill:#2196F3,color:#fff
```
**What happened to Department 'Finance' (D04)?** Notice it's not in the output! Because no employee has DeptId = D04, there's no matching row ā so Finance doesn't appear. This is a key property of Equi-Join (which is an **Inner Join**).
**Alternative Modern Syntax (JOIN ... ON):**
```sql
-- modern_join_syntax.sql
SELECT E.EmpId, E.Name, E.Salary, D.DeptName
FROM Employee E
JOIN Department D ON E.DeptId = D.DeptId;
-- Produces exactly the same result ā cleaner, modern syntax
```
> [!NOTE]
> **Which syntax to use in CBSE exams? š§ **
> Both the old comma-separated syntax (`FROM Employee E, Department D WHERE E.DeptId = D.DeptId`) and the modern JOIN ON syntax are correct. The textbook mostly uses the comma-separated form. Use whichever you're most comfortable with ā both are accepted in answers!
---
#### Natural Join
A **Natural Join** automatically joins tables on **all columns with the same name and data type** ā no explicit join condition needed.
```sql
-- natural_join.sql
SELECT * FROM Employee NATURAL JOIN Department;
-- MySQL automatically joins on DeptId (the common column name)
```
**Output:** Same as Equi-Join above ā MySQL finds `DeptId` as the common column name and joins on it automatically.
**Equi-Join vs Natural Join ā Quick Comparison:**
| Feature | Equi-Join | Natural Join |
| :--- | :--- | :--- |
| **Join condition** | Explicitly written by programmer | Automatically found by MySQL |
| **Basis** | Equality on specified columns | ALL columns with same name |
| **Duplicate column in output?** | Yes ā DeptId appears twice | No ā common column appears once |
| **Control** | High (you choose which column) | Low (MySQL decides) |
| **Risk** | Safer ā explicit | Can cause surprises if unexpected columns share names |
> [!IMPORTANT]
> **Board Exam Tip**
> "Differentiate between Equi-Join and Natural Join." ā **2-mark** question asked frequently!
> Answer: **Equi-Join** links tables using an explicit equality condition written by the programmer (WHERE E.DeptId = D.DeptId). **Natural Join** automatically links tables on all columns that share the same name and data type ā no condition needs to be written. Natural Join also eliminates duplicate columns from the result.
---
### 12.4.4 Additional Search Conditions in Joins šÆ
Real queries often need **both a Join condition AND a filter condition** (using WHERE) at the same time. You simply add more conditions using AND.
**Real-World Need:** *"Show employees who work in IT department, with their department location."*
```sql
-- additional_search_conditions.sql
SELECT E.Name, E.Salary, D.DeptName, D.Location
FROM Employee E, Department D
WHERE E.DeptId = D.DeptId
AND D.DeptName = 'IT';
```
**Output:**
| Name | Salary | DeptName | Location |
| :--- | :--- | :--- | :--- |
| Arjun Kumar | 62000 | IT | Mumbai |
| Rohan Das | 71000 | IT | Mumbai |
**The Logic:**
```
WHERE E.DeptId = D.DeptId ā Join condition (links the tables)
AND D.DeptName = 'IT' ā Search condition (filters the result)
```
**More Examples of Combined Conditions:**
```sql
-- combined_join_conditions.sql
-- Show all employees earning above 50000 with their department name:
SELECT E.Name, E.Salary, D.DeptName
FROM Employee E, Department D
WHERE E.DeptId = D.DeptId
AND E.Salary > 50000;
-- Show Delhi employees with their department location:
SELECT E.Name, E.City, D.DeptName, D.Location
FROM Employee E, Department D
WHERE E.DeptId = D.DeptId
AND E.City = 'Delhi';
```
**Output of last query:**
| Name | City | DeptName | Location |
| :--- | :--- | :--- | :--- |
| Priya Sharma | Delhi | Sales | New Delhi |
| Sita Roy | Delhi | HR | Kolkata |
| Vikram Singh | Delhi | HR | Kolkata |
> [!TIP]
> **Join + Filter = Real SQL Power! šµ**
> In real applications, you almost always combine a Join condition with one or more filter conditions. The Join links the tables; the filter narrows the result. Think of them as: Join = which rows fit together, Filter = which of those combined rows I actually want!
> [!IMPORTANT]
> **Board Exam Tip**
> "Write a query to display the name, salary, and department name of all employees working in Delhi." ā **3-mark** question, the most common Join query in CBSE papers!
> Answer:
> ```sql
> SELECT E.Name, E.Salary, D.DeptName
> FROM Employee E, Department D
> WHERE E.DeptId = D.DeptId
> AND E.City = 'Delhi';
> ```
---
## š Full SELECT Statement Order ā The Complete Picture
Now that we have all clauses, here is the complete ORDER in which they must appear in a SELECT statement:
```sql
SELECT column/aggregate/expression -- WHAT to show
FROM table(s) -- WHERE data comes from
WHERE row_condition -- WHICH rows to include (before grouping)
GROUP BY column(s) -- HOW to group the rows
HAVING group_condition -- WHICH groups to include (after grouping)
ORDER BY column [ASC|DESC] -- HOW to sort the final result
```
> [!IMPORTANT]
> **Board Exam Tip ā Clause Order!**
> A very common 1-mark question: "In what order do SQL clauses appear?"
> Remember this as: **SF WG HO** ā **S**ELECT ā **F**ROM ā **W**HERE ā **G**ROUP BY ā **H**AVING ā **O**RDER BY
**The Processing Order (different from writing order!):**
1. FROM ā identify the table(s)
2. WHERE ā filter individual rows
3. GROUP BY ā group the remaining rows
4. HAVING ā filter the groups
5. SELECT ā compute and display the output columns
6. ORDER BY ā sort the final result
---
## ā ļø Common Errors and Misconceptions
| Misconception | Correct Fact |
| :--- | :--- |
| ā WHERE and HAVING do the same thing | ā
WHERE filters rows BEFORE grouping; HAVING filters groups AFTER grouping. You cannot use aggregate functions in WHERE! |
| ā You can SELECT any column with GROUP BY | ā
Every non-aggregated column in SELECT must appear in GROUP BY. Otherwise MySQL throws an error |
| ā Cartesian Product is a useful join | ā
Cartesian Product (joining without condition) produces meaningless results; always use a proper Join condition |
| ā Natural Join always gives the same result as Equi-Join | ā
Natural Join joins on ALL shared column names (which could be more than intended). Equi-Join gives you explicit control over which columns to join on |
| ā Table aliases permanently rename the table | ā
Table aliases are temporary ā they only exist for the duration of that query. The actual table name in the database is unchanged |
| ā Joins always show rows from both tables | ā
An Equi-Join (inner join) only shows rows where the join condition is satisfied in BOTH tables ā unmatched rows (like Finance with no employees) are excluded |
| ā HAVING can be used without GROUP BY | ā
HAVING is meaningful only when used with GROUP BY. Without GROUP BY, the entire table is treated as one group |
---
## š Quick Revision ā Exam Ready!
**GROUP BY ā One-Line Summary:**
- **GROUP BY col** ā Groups rows with the same value into one summary row
- **Multiple columns in GROUP BY** ā Creates finer sub-groups (nested grouping)
- **HAVING** ā Filters groups (like WHERE, but for groups); can use aggregate functions
- **WHERE vs HAVING** ā WHERE = before grouping, rows; HAVING = after grouping, groups
**Joins ā One-Line Summary:**
- **Cartesian Product** ā Every row Ć every row; no condition; m Ć n rows ā almost always wrong!
- **Table Alias** ā Short temporary name for a table (E for Employee, D for Department)
- **Equi-Join** ā Links tables using equality on a common column (FK = PK); most common join
- **Natural Join** ā Automatically joins on all columns with the same name; no condition needed
**Complete SELECT Clause Order:**
```
SELECT ā FROM ā WHERE ā GROUP BY ā HAVING ā ORDER BY
```
**Key Formulas:**
- Cartesian Product rows = rows in Table A Ć rows in Table B
- GROUP BY always used with aggregate functions
- HAVING always used with GROUP BY
- Equi-Join condition: child.FK = parent.PK
---
## šÆ Sample Board Exam Questions
### Q1: Very Short Answer [1 mark each]
a) Which clause is used to filter groups in SQL?
**ā HAVING**
b) What is a Cartesian Product of a 5-row table and a 3-row table?
**ā 5 Ć 3 = 15 rows**
c) Which type of Join automatically joins on all columns with the same name?
**ā Natural Join**
d) What does `GROUP BY Dept` do in a SELECT statement?
**ā It groups all rows with the same department into a single group so aggregate functions can summarise each group separately.**
e) Can you use an aggregate function like AVG() in a WHERE clause?
**ā No ā aggregate functions cannot be used in WHERE. Use HAVING instead.**
---
### Q2: Short Answer [2 marks]
**Q: Differentiate between WHERE and HAVING clause with an example.**
**WHERE** filters **individual rows** before grouping. It cannot use aggregate functions.
`SELECT Dept, AVG(Salary) FROM Employee WHERE City = 'Delhi' GROUP BY Dept;` ā WHERE here removes non-Delhi rows before grouping.
**HAVING** filters **groups** after GROUP BY. It can use aggregate functions.
`SELECT Dept, AVG(Salary) FROM Employee GROUP BY Dept HAVING AVG(Salary) > 50000;` ā HAVING here removes groups whose average salary is ā¤ 50000.
---
### Q3: Query Writing [3 marks]
**Q: Write SQL queries based on the Employee and Department tables:**
a) Display each department name and the number of employees in it.
```sql
SELECT D.DeptName, COUNT(*) AS TotalEmployees
FROM Employee E, Department D
WHERE E.DeptId = D.DeptId
GROUP BY D.DeptName;
```
b) Display departments where total salary paid exceeds ā¹80,000.
```sql
SELECT Dept, SUM(Salary) AS TotalSalary
FROM Employee
GROUP BY Dept
HAVING SUM(Salary) > 80000;
```
c) Display the name, salary, and department name of employees earning more than ā¹50,000.
```sql
SELECT E.Name, E.Salary, D.DeptName
FROM Employee E, Department D
WHERE E.DeptId = D.DeptId
AND E.Salary > 50000;
```
---
### Q4: Output Based Question [3 marks]
**Q: Based on the Employee table, what is the output of the following query?**
```sql
SELECT City, COUNT(*) AS Total, MAX(Salary) AS MaxSal
FROM Employee
GROUP BY City
HAVING COUNT(*) > 1;
```
**Output:**
| City | Total | MaxSal |
| :--- | :--- | :--- |
| Delhi | 3 | 45000 |
| Mumbai | 2 | 62000 |
*(Bangalore has only 1 employee so it is filtered out by HAVING COUNT(*) > 1)*
---
### Q5: Short Answer [2 marks]
**Q: What is a Cartesian Product? Why should it be avoided in practice?**
A **Cartesian Product** is produced when two tables are combined in a FROM clause without any Join condition ā every row of the first table is paired with every row of the second table. If Employee has 6 rows and Department has 4 rows, the Cartesian Product has 24 rows. It should be avoided because it produces meaningless data (an employee paired with every department, even irrelevant ones) and can generate enormously large result sets that slow down the database.
---
## āļø Practice Problems
1. Write a query to display the average salary for each city, but only for cities where more than one employee works.
2. Write a query to find the total salary spent on each department. Display results in descending order of total salary.
3. Write a query to display the name and department name of all employees whose city is 'Mumbai', using an Equi-Join.
4. A table Student(RollNo, Name, Class, Marks) and Section(Class, SectionName, Teacher) are given. Write a query to display student names, their marks, and the teacher's name for all students scoring above 80.
5. Explain why `SELECT Dept, Name, COUNT(*) FROM Employee GROUP BY Dept;` produces an error. How would you fix it?
6. Write a query to display each department name along with the highest and lowest salary in that department.
7. Without running the query, predict how many rows `SELECT * FROM Employee, Department;` will return if Employee has 6 rows and Department has 4 rows.
8. Rewrite this Equi-Join using a Natural Join: `SELECT E.Name, D.DeptName FROM Employee E, Department D WHERE E.DeptId = D.DeptId;`
9. Write a query to display department names that have more than one employee earning above ā¹40,000.
10. What is the difference between `GROUP BY Dept` and `GROUP BY Dept, City`? Explain with a real-world example using the Employee table.
Back to List
Calculating...
UNIT 3 : CH 12
Jul 06, 2026
š Grouping Records, Joins in SQL
Found this helpful?