🧩 Querying Using SQL

You already know how to fetch data with SELECT and filter it with WHERE. This chapter shows how to make results organised (sorted with ORDER BY) and summarised (with aggregate functions, and grouped into categories with GROUP BY and HAVING).

7.1 🌟 Introduction

When you run SELECT * FROM Students;, MySQL shows rows in whatever order they happen to be stored — usually the order they were inserted. But what if you want:

• Marks sorted from highest to lowest?
• Students grouped by stream, showing each stream's average?

graph LR
    RAW["📋 Unsorted, Scattered Data"]
    ORDER["🔢 ORDER BY\nSort the rows"]
    GROUP["📦 GROUP BY\nSummarise into categories"]
    REPORT["📊 Meaningful Report!"]

    RAW --> ORDER --> REPORT
    RAW --> GROUP --> REPORT

    style ORDER fill:#2196F3,color:#fff
    style GROUP fill:#9C27B0,color:#fff
    style REPORT fill:#4CAF50,color:#fff

Analogy: Imagine a teacher has 100 answer sheets in a random pile. ORDER BY is like arranging them by marks. GROUP BY is like sorting them into separate stacks by Section and then calculating each stack's average. Both make messy data USEFUL!

Our Sample Table — used in every example below:

Table: Students

7.2 🔢 Ordering Records in Result — ORDER BY Clause

7.2.1 Recalling SQL SELECT ORDER BY Clause 📶

ORDER BY sorts your query's result — either Ascending (A→Z, 0→9) or Descending (Z→A, 9→0).

-- order_by_basic.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

-- default_asc_demo.sql
SELECT Name, Marks FROM Students ORDER BY Marks;
-- Same as writing: ORDER BY Marks ASC;

7.2.2 Ordering Data on Multiple Columns 🎯

You can sort by MORE than one column — the second column is used to break TIES in the first column.

-- order_by_multiple.sql
SELECT Name, City, Marks FROM Students
ORDER BY City ASC, Marks DESC;

Output:

Name      City      Marks
Ishaan    Delhi     92     ← Delhi group, sorted by Marks (highest first)
Arjun     Delhi     85
Vihaan    Delhi     78
Zara      Mumbai    92     ← Mumbai group starts, sorted by Marks
Rohan     Mumbai    65
Ananya    Pune      88     ← Pune group

graph TD
    STEP1["Step 1: Sort by City\n(Delhi, Mumbai, Pune)"]
    STEP2["Step 2: WITHIN each city,\nsort by Marks DESC"]

    STEP1 --> STEP2

    style STEP1 fill:#2196F3,color:#fff
    style STEP2 fill:#4CAF50,color:#fff

How to read this: First, MySQL sorts everyone by City (alphabetically). THEN, for students who share the SAME city (like the 3 Delhi students), it sorts them by Marks — highest first!

7.2.3 Ordering Data on the Basis of an Expression 🧮

You can even sort by a CALCULATED value, not just an existing column!

-- order_by_expression.sql
SELECT Name, Marks, Marks * 0.95 AS AdjustedMarks
FROM Students
ORDER BY Marks * 0.95 DESC;

Output:

Name      Marks    AdjustedMarks
Zara      92       87.4
Ishaan    92       87.4
Ananya    88       83.6
Arjun     85       80.75
Vihaan    78       74.1
Rohan     65       61.75

You can even use the ALIAS name instead of repeating the whole expression: ORDER BY AdjustedMarks DESC — works exactly the same way, but cleaner!

7.3 📊 Aggregate Functions

Aggregate Functions work on a whole GROUP of rows and squeeze them down into ONE single summary value.

graph LR
    MANY["📊 Many Rows\n85, 92, 78, 88, 65, 92"]
    AGG["📐 Aggregate Function"]
    ONE["1️⃣ ONE Result\n(e.g., Average = 83.33)"]

    MANY --> AGG --> ONE

    style AGG fill:#FF9800,color:#fff
    style ONE fill:#4CAF50,color:#fff

The Five Core Aggregate Functions:

-- aggregate_functions_demo.sql
SELECT SUM(Marks)   FROM Students;    -- Output: 500
SELECT AVG(Marks)   FROM Students;    -- Output: 83.33
SELECT MAX(Marks)   FROM Students;    -- Output: 92
SELECT MIN(Marks)   FROM Students;    -- Output: 65
SELECT COUNT(*)     FROM Students;    -- Output: 6

COUNT(*) vs COUNT(column) — The Critical Difference!

-- count_star_vs_column.sql
SELECT COUNT(*)   FROM Students;   -- Counts ALL rows (even if some columns have NULL)
SELECT COUNT(City) FROM Students;  -- Counts only NON-NULL values in the City column

7.4 🧩 Types of SQL Functions

Two families of SQL functions — knowing the difference is important for exams.

graph TD
    F["SQL FUNCTIONS"]
    SINGLE["🔢 Single-Row\n(Ch 6: String/Numeric/Date)\n5 rows in → 5 results out"]
    MULTI["📊 Aggregate\n(This Chapter!)\n5 rows in → 1 result out"]

    F --> SINGLE
    F --> MULTI

    style SINGLE fill:#2196F3,color:#fff
    style MULTI fill:#9C27B0,color:#fff

-- single_row_vs_aggregate.sql

-- Single-row: gives 6 results (one per student)
SELECT UPPER(Name) FROM Students;

-- Aggregate: gives 1 result (total for everyone)
SELECT SUM(Marks) FROM Students;

7.5 📦 Grouping Result — GROUP BY

GROUP BY splits your data into CATEGORIES, so you can apply aggregate functions separately to each category instead of the whole table at once.

graph TD
    ALL["📋 All 6 Students"]
    GRP["GROUP BY Stream"]
    SCI["🔬 Science Group\nArjun, Vihaan, Ishaan"]
    COM["💼 Commerce Group\nZara, Rohan"]
    HUM["📚 Humanities Group\nAnanya"]

    ALL --> GRP
    GRP --> SCI
    GRP --> COM
    GRP --> HUM

    style GRP fill:#FF9800,color:#fff
    style SCI fill:#4CAF50,color:#fff
    style COM fill:#2196F3,color:#fff
    style HUM fill:#9C27B0,color:#fff

Basic GROUP BY:

-- group_by_basic.sql
SELECT Stream, AVG(Marks) FROM Students
GROUP BY Stream;

Output:

Stream        AVG(Marks)
Science       85.0
Commerce      78.5
Humanities    88.0

How the average was calculated:

Science:    (85 + 78 + 92) / 3 = 85.0
Commerce:   (92 + 65) / 2      = 78.5
Humanities: 88 / 1             = 88.0

7.5.1 Nested Groups — Grouping on Multiple Columns 🪆

You can group by MORE than one column — creating finer sub-groups (like a two-level filing cabinet!).

-- nested_groups.sql
SELECT City, Stream, COUNT(*) AS Total
FROM Students
GROUP BY City, Stream;

Output:

City      Stream        Total
Delhi     Science       3
Mumbai    Commerce      2
Pune      Humanities    1

graph TD
    ALL["📋 All Students"]
    C1["Delhi"]
    C2["Mumbai"]
    C3["Pune"]
    S1["Science: 3"]
    S2["Commerce: 2"]
    S3["Humanities: 1"]

    ALL --> C1 --> S1
    ALL --> C2 --> S2
    ALL --> C3 --> S3

    style C1 fill:#4CAF50,color:#fff
    style C2 fill:#2196F3,color:#fff
    style C3 fill:#9C27B0,color:#fff

First, MySQL groups by City. THEN, within each city, it further groups by Stream. Since every city in our table happens to have only ONE stream, each combination gets its own row!

7.5.2 Placing Conditions on Groups — HAVING Clause 🎯

WHERE filters individual ROWS before grouping. HAVING filters GROUPS after grouping. This is a key concept of this chapter.

-- having_demo.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 — its max mark is only 88, which fails the > 90 condition!)

graph LR
    RAW["All Rows"]
    W["WHERE\n(filters ROWS,\nbefore grouping)"]
    G["GROUP BY\n(creates groups)"]
    AGG["Aggregate Functions\nrun on each group"]
    H["HAVING\n(filters GROUPS,\nafter grouping)"]
    RESULT["Final Result"]

    RAW --> W --> G --> AGG --> H --> RESULT

    style W fill:#FF9800,color:#fff
    style H fill:#F44336,color:#fff

WHERE vs HAVING — The Ultimate Comparison:

Using WHERE and HAVING TOGETHER — The Full Combo!

-- combined_where_having.sql
-- "Show average marks per stream, only for Delhi students,
--  only showing streams where average is above 80"

SELECT Stream, AVG(Marks)
FROM Students
WHERE City = 'Delhi'
GROUP BY Stream
HAVING AVG(Marks) > 80
ORDER BY AVG(Marks) DESC;

Output:

Stream        AVG(Marks)
Science       85.0000

(WHERE keeps only the 3 Delhi students — all of them in Science — so one group is left, and its average 85 passes > 80. MySQL prints averages with 4 decimal places.)

7.5.3 Non-Group Expressions with GROUP BY ⚠️

A key rule of this chapter: When using GROUP BY, your SELECT list can ONLY contain:

1. Columns that ARE in the GROUP BY clause, OR
2. Aggregate functions

-- illegal_query.sql  ❌ THIS WILL CAUSE AN ERROR!
SELECT Name, Stream, AVG(Marks)
FROM Students
GROUP BY Stream;

Why is this wrong? Science has THREE students: Arjun, Vihaan, Ishaan. When MySQL groups them into ONE "Science" row, which Name should it display? Arjun's? Vihaan's? Ishaan's? It's impossible to know — so MySQL refuses to run this query! (MySQL 5.7 and newer refuse it by default; some older servers show any one Name instead, which is just as wrong.)

-- correct_query.sql  ✅ THIS WORKS FINE!
SELECT Stream, AVG(Marks)
FROM Students
GROUP BY Stream;

graph TD
    Q["SELECT Name, Stream, AVG(Marks)\nGROUP BY Stream;"]
    PROBLEM["❌ 'Name' is NOT in GROUP BY\nand NOT an aggregate function"]
    CONFUSE["MySQL: 'Which Name do I show\nfor the Science group?'"]
    ERROR["🚫 ERROR!"]

    Q --> PROBLEM --> CONFUSE --> ERROR

    style ERROR fill:#F44336,color:#fff

🧪 More Worked Examples — A Second Table

Practice on a different table too. This one has a NULL (bill 4 was an online order, so it has no salesman), which shows how aggregate functions treat NULLs.

Table: Sales

Example 1 — Three kinds of COUNT:

SELECT COUNT(*), COUNT(Salesman), COUNT(DISTINCT Salesman) FROM Sales;

Output:

COUNT(*)    COUNT(Salesman)    COUNT(DISTINCT Salesman)
6           5                  2

COUNT(*) counts all 6 rows. COUNT(Salesman) skips the NULL (5). COUNT(DISTINCT Salesman) counts different names only: Ravi and Meena (2).

Example 2 — Aggregate of an expression (total sales value):

SELECT SUM(Qty * Price) AS TotalSales FROM Sales;

Output:

TotalSales
1350

For each row MySQL first works out Qty * Price (150, 200, 360, 120, 160, 360), then adds them up.

Example 3 — Several aggregates for each group:

SELECT Category, COUNT(*), SUM(Qty), AVG(Price)
FROM Sales
GROUP BY Category
ORDER BY Category;

Output:

Category      COUNT(*)    SUM(Qty)    AVG(Price)
Fruit         3           42          31.6667
Stationery    3           35          21.0000

Example 4 — NULL makes its own group:

SELECT Salesman, SUM(Qty * Price) AS Sales
FROM Sales
GROUP BY Salesman
ORDER BY Salesman;

Output:

Salesman    Sales
NULL        120
Meena       360
Ravi        870

All rows with a NULL salesman are put together in one NULL group. In ascending order, NULL comes first.

Example 5 — HAVING with COUNT (salesmen with more than one bill):

SELECT Salesman, COUNT(*) AS Bills
FROM Sales
GROUP BY Salesman
HAVING COUNT(*) > 1
ORDER BY Salesman;

Output:

Salesman    Bills
Meena       2
Ravi        3

The NULL group has only 1 bill, so HAVING removes it.

Example 6 — WHERE first, then GROUP BY (only items priced above 10):

SELECT Category, SUM(Qty) AS TotalQty
FROM Sales
WHERE Price > 10
GROUP BY Category
ORDER BY Category;

Output:

Category      TotalQty
Fruit         18
Stationery    15

WHERE first throws away Banana (₹5) and Pencil (₹8). Only then are the groups made: Fruit = Apple 12 + Mango 6, Stationery = Pen 10 + Notebook 5.

Example 7 — Nested groups that really nest:

SELECT Category, Salesman, COUNT(*) AS Bills
FROM Sales
GROUP BY Category, Salesman
ORDER BY Category, Salesman;

Output:

Category      Salesman    Bills
Fruit         NULL        1
Fruit         Ravi        2
Stationery    Meena       2
Stationery    Ravi        1

Ravi sells in both categories, so he appears in two sub-groups.

Example 8 — Sort the groups by their total (using the alias):

SELECT Category, SUM(Qty * Price) AS Total
FROM Sales
GROUP BY Category
ORDER BY Total DESC;

Output:

Category      Total
Fruit         840
Stationery    510

Example 9 — MIN() and MAX() also work on text:

SELECT MIN(Item), MAX(Item) FROM Sales;

Output:

MIN(Item)    MAX(Item)
Apple        Pencil

For text, MIN and MAX use alphabetical order: Apple is first and Pencil is last (Pen comes before Pencil).

📐 Writing Order vs Running Order — SF-WG-HO!

graph LR
    S["1️⃣ SELECT\nColumns to show"]
    F["2️⃣ FROM\nWhich table"]
    W["3️⃣ WHERE\nFilter rows"]
    G["4️⃣ GROUP BY\nCreate groups"]
    H["5️⃣ HAVING\nFilter groups"]
    O["6️⃣ ORDER BY\nSort final result"]

    S --> F --> W --> G --> H --> O

    style W fill:#FF9800,color:#fff
    style H fill:#F44336,color:#fff
    style O fill:#4CAF50,color:#fff

Write the clauses in this order: SELECT → FROM → WHERE → GROUP BY → HAVING → ORDER BY

MySQL RUNS them in a different order:

FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY

⚠️ Common Errors and Misconceptions

Write: SELECT → FROM → WHERE → GROUP BY → HAVING → ORDER BY
Run:   FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY

🎯 Sample Board Exam Questions

Q1: Very Short Answer [1 mark each]

a) What is the default sorting order in ORDER BY? → Ascending (ASC)

b) Which clause is used to filter groups (not rows)? → HAVING

c) What does COUNT(*) return for a table with 10 rows (some with NULLs)? → 10 — it counts ALL rows regardless of NULL values

d) Name any two aggregate functions. → Any two of: SUM(), AVG(), MAX(), MIN(), COUNT()

e) Can HAVING be used without GROUP BY? (Conceptually) → It CAN be written, but it's designed to work together with GROUP BY — without it, the whole table is treated as ONE single group.

Q2: Short Answer [2 marks]

Q: Differentiate between WHERE and HAVING clause with an example.

WHERE filters individual rows BEFORE grouping and cannot use aggregate functions: SELECT * FROM Students WHERE City = 'Delhi';

HAVING filters entire groups AFTER GROUP BY and CAN use aggregate functions: SELECT Stream, AVG(Marks) FROM Students GROUP BY Stream HAVING AVG(Marks) > 80;

Q3: Query Writing [3 marks]

Q: Write SQL queries for the following:

a) Display all students sorted by Marks in descending order.

SELECT * FROM Students ORDER BY Marks DESC;

b) Display the number of students in each stream.

SELECT Stream, COUNT(*) FROM Students GROUP BY Stream;

c) Display streams having more than 2 students.

SELECT Stream, COUNT(*) FROM Students
GROUP BY Stream HAVING COUNT(*) > 2;

Q4: Output Based [3 marks]

Q: Based on the Students table, what is the output of the following query?

SELECT City, COUNT(*) AS Total FROM Students
GROUP BY City
HAVING COUNT(*) > 1;

Output:

City      Total
Delhi     3
Mumbai    2

*(Pune has only 1 student, so it's excluded by HAVING COUNT(*) > 1)*

Q5: Error Spotting [2 marks]

Q: Find the error in the following query and correct it:

SELECT Name, Stream, AVG(Marks) FROM Students GROUP BY Stream;

Error: Name is not in the GROUP BY clause and is not an aggregate function — MySQL cannot decide which Name to show for a group with multiple students.

Corrected:

SELECT Stream, AVG(Marks) FROM Students GROUP BY Stream;

✏️ Practice Problems

1. Write a query to display students sorted by Stream (A-Z), and within each stream, by Marks (highest first).

2. Write a query to find the total marks scored by all students combined.

3. Write a query to display each city along with the maximum marks scored by any student in that city.

4. Write a query to display streams where the average marks are greater than 80, sorted by average marks descending.

5. What is the difference between COUNT(*) and COUNT(City)? Would they give different results if some City values were NULL?

6. Write a query to display the number of students in each City-Stream combination.

7. Explain why SELECT RollNo, City, COUNT(*) FROM Students GROUP BY City; produces an error. Rewrite it correctly.

8. Write a query showing only cities having more than 1 student, along with their average marks.

9. Predict the output: SELECT Stream, MIN(Marks), MAX(Marks) FROM Students GROUP BY Stream;

10. Write one complete query combining WHERE, GROUP BY, HAVING, and ORDER BY — to show streams (excluding 'Humanities') having more than 1 student, sorted by count descending.