## 🗂️ Sample Table: `STUDENTS`
To keep things clear, we will use this single table for **all** examples.
| RollNo | Name | Stream | Marks | City |
| --- | --- | --- | --- | --- |
| 101 | Arjun | Science | 85 | Delhi |
| 102 | Zara | Commerce | 92 | Mumbai |
| 103 | Vihaan | Science | 78 | Delhi |
| 104 | Ananya | Humanities | 88 | Pune |
| 105 | Rohan | Commerce | 65 | Mumbai |
| 106 | Ishaan | Science | 92 | Delhi |
---
## 1. 🔢 Ordering Records (ORDER BY)
**Purpose:** The data in a table is not stored in any specific order. The `ORDER BY` clause allows you to sort the result of a query in either **Ascending (ASC)** or **Descending (DESC)** order.
* **Default:** Ascending order (A-Z, 0-9) if no keyword is specified.
### 7.2.1 Basic Ordering (Single Column)
**Scenario:** You want a list of students sorted by their `Marks` from highest to lowest.
* **Syntax:** `SELECT ... FROM ... ORDER BY column_name [ASC|DESC];`
**Query:**
```sql
SELECT Name, Marks FROM STUDENTS ORDER BY Marks DESC;
```
**Output:**
| Name | Marks |
| :--- | :--- |
| Zara | 92 |
| Ishaan | 92 |
| Ananya | 88 |
| Arjun | 85 |
| Vihaan | 78 |
| Rohan | 65 |
---
### 7.2.2 Ordering on Multiple Columns
**Scenario:** Sort by `City` alphabetically. If two students are from the same city, sort them by `Marks` (Highest first).
* **Logic:** The database sorts by the first column listed. If there is a "tie" (duplicate values), it breaks the tie using the second column.
**Query:**
```sql
SELECT Name, City, Marks FROM STUDENTS ORDER BY City ASC, Marks DESC;
```
**Output:**
| Name | City | Marks |
| :--- | :--- | :--- |
| **Ishaan** | **Delhi** | **92** |
| **Arjun** | **Delhi** | **85** |
| **Vihaan** | **Delhi** | **78** |
| Zara | Mumbai | 92 |
| Rohan | Mumbai | 65 |
| Ananya | Pune | 88 |
*(Notice how the 3 Delhi students are sorted internally by their marks)*
---
### 7.2.3 Ordering on Expressions
**Scenario:** You want to calculate `Marks` reduced by 5% and sort the result based on this *calculated* value.
**Query:**
```sql
SELECT Name, Marks, Marks*0.95 AS NewMarks
FROM STUDENTS
ORDER BY Marks*0.95 DESC;
```
*(Alternatively, you can use the alias: `ORDER BY NewMarks DESC`)*
**Output:**
| Name | Marks | NewMarks |
| :--- | :--- | :--- |
| Zara | 92 | 87.4 |
| Ishaan | 92 | 87.4 |
| Ananya | 88 | 83.6 |
| Arjun | 85 | 80.75 |
| ... | ... | ... |
---
## 2. 🧮 Aggregate Functions (Group Functions)
**Purpose:** These functions perform a calculation on a **set of values** (multiple rows) and return a **single value**.
**Important Rules:**
1. They ignore `NULL` values (except `COUNT(*)`).
2. Usually used with `GROUP BY`.
### List of Aggregate Functions
#### A. `SUM()`
* **Purpose:** Returns the total sum of a numeric column.
* **Query:** `SELECT SUM(Marks) FROM STUDENTS;`
* **Output:** `500`
#### B. `AVG()`
* **Purpose:** Returns the average value of a numeric column.
* **Query:** `SELECT AVG(Marks) FROM STUDENTS;`
* **Output:** `83.33` (approx)
#### C. `MAX()`
* **Purpose:** Returns the largest value. Works with Numbers, Dates, and Text (Z is larger than A).
* **Query:** `SELECT MAX(Marks) FROM STUDENTS;`
* **Output:** `92`
#### D. `MIN()`
* **Purpose:** Returns the smallest value.
* **Query:** `SELECT MIN(Marks) FROM STUDENTS;`
* **Output:** `65`
#### E. `COUNT()`
This comes in two flavors:
1. **`COUNT(*)`**: Counts **total rows** in the table (including NULLs).
* `SELECT COUNT(*) FROM STUDENTS;` -> Output: `6`
2. **`COUNT(column)`**: Counts **non-NULL values** in that specific column.
* `SELECT COUNT(City) FROM STUDENTS;` -> Output: `6` (Assuming no nulls).
---
## 3. 🧩 Types of SQL Functions
In CBSE, it is crucial to understand the distinction between these two types:
| Feature | Single Row Functions | Aggregate (Multiple Row) Functions |
| --- | --- | --- |
| **Input** | Takes one value (one row) at a time. | Takes a set of values (multiple rows) at a time. |
| **Output** | Returns one result per row. | Returns one result per group of rows. |
| **Examples** | `UCASE()`, `ROUND()`, `MID()`, `LEN()` | `SUM()`, `AVG()`, `COUNT()`, `MAX()` |
---
## 4. 📦 Grouping Result (GROUP BY)
**Purpose:** `GROUP BY` allows you to arrange identical data into groups. This is "super effective" when combined with Aggregate functions to get summary statistics.
### 7.5.1 Grouping on Columns
**Scenario:** Calculate the **average marks** for **each stream** (Science, Commerce, Humanities).
* **Syntax:** `SELECT column, AGG_FUNC(column) FROM table GROUP BY column;`
**Query:**
```sql
SELECT Stream, AVG(Marks) FROM STUDENTS GROUP BY Stream;
```
**Output:**
| Stream | AVG(Marks) |
| :--- | :--- |
| Science | 85.0 |
| Commerce | 78.5 |
| Humanities | 88.0 |
* *Science Average = (85+78+92)/3 = 85*
* *Commerce Average = (92+65)/2 = 78.5*
---
### 7.5.2 Nested Groups (Grouping on Multiple Columns)
**Scenario:** Count students grouped by `City`, and within each city, group by `Stream`.
**Query:**
```sql
SELECT City, Stream, COUNT(*)
FROM STUDENTS
GROUP BY City, Stream;
```
**Output:**
| City | Stream | COUNT(*) |
| :--- | :--- | :--- |
| Delhi | Science | 3 |
| Mumbai | Commerce | 2 |
| Pune | Humanities | 1 |
---
### 7.5.3 Conditions on Groups - HAVING Clause
**Crucial Concept:**
* **`WHERE` clause:** Filters **rows** (before grouping).
* **`HAVING` clause:** Filters **groups** (after grouping).
**Scenario:** Show the `Stream` and `Max(Marks)`, but **ONLY** for streams where the maximum mark is greater than 90.
**Query:**
```sql
SELECT Stream, MAX(Marks)
FROM STUDENTS
GROUP BY Stream
HAVING MAX(Marks) > 90;
```
**Output:**
| Stream | MAX(Marks) |
| :--- | :--- |
| Science | 92 |
| Commerce | 92 |
*(Humanities is hidden because its Max mark was 88, which is < 90)*
---
### 7.5.4 Non-Group Expressions with GROUP BY (Common Error!)
**Rule:** When using `GROUP BY`, the `SELECT` list can **only** contain:
1. Columns used in the `GROUP BY` clause.
2. Aggregate functions (SUM, AVG, etc.).
**❌ Incorrect Query:**
`SELECT Name, Stream, AVG(Marks) FROM STUDENTS GROUP BY Stream;`
*(Error: 'Name' is not in the GROUP BY clause. Which name should SQL show for the "Science" group? Arjun? Vihaan? It doesn't know!)*
**✅ Correct Query:**
`SELECT Stream, AVG(Marks) FROM STUDENTS GROUP BY Stream;`
---
### 🚀 Quick Cheat Sheet: The Order of Execution
When writing complex queries, remember this order (S-F-W-G-H-O):
1. **SELECT** (Columns)
2. **FROM** (Table)
3. **WHERE** (Row Filter)
4. **GROUP BY** (Grouping)
5. **HAVING** (Group Filter)
6. **ORDER BY** (Sorting)
**Example combining EVERYTHING:**
*"Show the average marks of streams, but only for students in 'Delhi', and only show streams where the average is above 80, sorted by that average."*
```sql
SELECT Stream, AVG(Marks)
FROM STUDENTS
WHERE City = 'Delhi'
GROUP BY Stream
HAVING AVG(Marks) > 80
ORDER BY AVG(Marks) DESC;
```
Back to List
Calculating...