🧩 Querying Using SQL

You already know how to fetch data with SELECT and filter it with WHERE. Now let's make our results **organised** (sorted the way we want) and **summarised** (grouped into meaningful categories) — the exact skills real analysts use every single day! 📊✨ > [!TIP] > **How to use these notes:** Focus especially on **ORDER BY with multiple columns**, **GROUP BY + HAVING**, and the **Golden Rule of GROUP BY** — these three topics appear in almost every board exam! Type every query yourself. 🎯 --- ## 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? ```mermaid 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** | 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 | --- ## 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)**. ```sql -- 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 ``` | Keyword | Meaning | Is it Default? | | :--- | :--- | :--- | | `ASC` | Smallest → Largest (A→Z) | ✅ Yes — used automatically if you write nothing | | `DESC` | Largest → Smallest (Z→A) | ❌ No — must be typed explicitly | ```sql -- default_asc_demo.sql SELECT Name, Marks FROM Students ORDER BY Marks; -- Same as writing: ORDER BY Marks ASC; ``` > [!IMPORTANT] > **Board Exam Tip** > "What is the default sorting order in SQL if ASC/DESC is not mentioned?" — **1-mark** question! > Answer: **Ascending order (ASC)** is the default. --- ### 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. ```sql -- 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 ``` ```mermaid 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! > [!IMPORTANT] > **Board Exam Tip** > "Write a query to display students sorted by City, and within each city by Marks in descending order." — **2-mark** question, extremely common! > Answer: `SELECT * FROM Students ORDER BY City ASC, Marks DESC;` --- ### 7.2.3 Ordering Data on the Basis of an Expression 🧮 You can even sort by a CALCULATED value, not just an existing column! ```sql -- 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! > [!NOTE] > **Sorting doesn't need a "real" column! 🧠** > MySQL can sort based on ANY calculation — even one that doesn't exist as a stored column. This is very useful for reports like "sort by discounted price" without needing to permanently store that discounted price anywhere! --- ## 7.3 📊 Aggregate Functions **Aggregate Functions** work on a whole GROUP of rows and squeeze them down into ONE single summary value. ```mermaid 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:** ::: grid ::: card ➕ | SUM() | Adds up all values | `SUM(Marks)` → 500 ::: card 📊 | AVG() | Finds the average | `AVG(Marks)` → 83.33 ::: card 📈 | MAX() | Finds the largest value | `MAX(Marks)` → 92 ::: card 📉 | MIN() | Finds the smallest value | `MIN(Marks)` → 65 ::: card 🔢 | COUNT() | Counts rows/values | `COUNT(*)` → 6 ::: ```sql -- 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!** ```sql -- 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 ``` | Function | What It Counts | | :--- | :--- | | `COUNT(*)` | Every row in the table, including rows with NULL values | | `COUNT(column)` | Only the rows where THAT SPECIFIC column is NOT NULL | > [!WARNING] > **Important Rule!** > All aggregate functions **IGNORE NULL values** during their calculation — except `COUNT(*)`, which counts every row regardless of NULLs! > [!IMPORTANT] > **Board Exam Tip** > "What is the difference between COUNT(*) and COUNT(column_name)?" — **2-mark** question, asked EVERY year! > Answer: `COUNT(*)` counts the total number of rows, including those with NULL values. `COUNT(column_name)` counts only rows where that specific column has a non-NULL value. --- ## 7.4 🧩 Types of SQL Functions Two families of SQL functions — knowing the difference is a favourite exam question! | Feature | Single-Row Functions | Aggregate (Multi-Row) Functions | | :--- | :--- | :--- | | **Input** | ONE row at a time | A GROUP of rows at once | | **Output** | ONE result PER row | ONE result for the WHOLE group | | **Examples** | `UPPER()`, `ROUND()`, `SUBSTR()` | `SUM()`, `AVG()`, `COUNT()`, `MAX()`, `MIN()` | ```mermaid 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 ``` ```sql -- 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; ``` > [!IMPORTANT] > **Board Exam Tip** > "Classify the following as Single-Row or Aggregate functions: UPPER(), SUM(), ROUND(), COUNT(), SUBSTR()." — **2-mark** question! > **Single-Row:** UPPER(), ROUND(), SUBSTR() | **Aggregate:** SUM(), COUNT() --- ## 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. ```mermaid 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:** ```sql -- 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 ``` > [!IMPORTANT] > **Board Exam Tip** > "Write a query to display the average marks for each stream." — **2-mark** question, asked constantly! > Answer: `SELECT Stream, AVG(Marks) FROM Students GROUP BY Stream;` --- ### 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!). ```sql -- 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 ``` ```mermaid 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 one of the MOST tested concepts in the entire syllabus! ```sql -- 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!)* ```mermaid 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:** | Feature | WHERE | HAVING | | :--- | :--- | :--- | | **Filters** | Individual ROWS | GROUPS (after GROUP BY) | | **Runs** | BEFORE grouping | AFTER grouping | | **Can use aggregate functions?** | ❌ No! (`WHERE AVG(Marks) > 80` is ILLEGAL) | ✅ Yes! (`HAVING AVG(Marks) > 80` is correct) | **Using WHERE and HAVING TOGETHER — The Full Combo!** ```sql -- 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; ``` > [!WARNING] > **The #1 WHERE/HAVING Mistake!** > Writing `WHERE AVG(Marks) > 80` is a SYNTAX ERROR! Aggregate functions like AVG(), SUM(), COUNT() can NEVER be used inside a WHERE clause — you MUST use HAVING for any condition involving them! > [!IMPORTANT] > **Board Exam Tip** > "Differentiate between WHERE and HAVING clause." — **2-mark** question, asked EVERY single year without fail! > Answer: **WHERE** filters individual rows BEFORE grouping and cannot use aggregate functions. **HAVING** filters entire GROUPS AFTER GROUP BY is applied, and CAN use aggregate functions. --- ### 7.5.3 Non-Group Expressions with GROUP BY ⚠️ **THE most important rule in this whole chapter!** When using GROUP BY, your SELECT list can ONLY contain: 1. Columns that ARE in the GROUP BY clause, OR 2. Aggregate functions ```sql -- 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!** ```sql -- correct_query.sql ✅ THIS WORKS FINE! SELECT Stream, AVG(Marks) FROM Students GROUP BY Stream; ``` ```mermaid 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 ``` > [!WARNING] > **The Golden Rule of GROUP BY!** > Every column you SELECT must be EITHER (a) inside the GROUP BY clause, OR (b) wrapped inside an aggregate function like SUM(), AVG(), COUNT(). Breaking this rule is the #1 GROUP BY mistake students make in exams! > [!IMPORTANT] > **Board Exam Tip** > "Explain why the following query gives an error: `SELECT Name, Stream, COUNT(*) FROM Students GROUP BY Stream;`" — **2-mark** question! > Answer: `Name` is not included in the GROUP BY clause and is not wrapped in an aggregate function. Since each Stream group contains multiple different Names, MySQL cannot determine a single Name to display for that group — this causes an error. --- ## 📐 The Complete Order of Execution — SF-WG-HO! ```mermaid 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 ``` **Memorise this order: SELECT → FROM → WHERE → GROUP BY → HAVING → ORDER BY** > [!TIP] > **Memory Trick: "S-F-W-G-H-O" 🎵** > **S**illy **F**rogs **W**alk **G**iant **H**orses **O**utside > (Or make up your own funny sentence — whatever helps you remember the ORDER!) --- ## ⚠️ Common Errors and Misconceptions | Mistake | What Goes Wrong | Correct Understanding | | :--- | :--- | :--- | | ❌ `WHERE AVG(Marks) > 80` | Syntax Error! | ✅ Use `HAVING AVG(Marks) > 80` instead | | ❌ `SELECT Name, Stream, COUNT(*) ... GROUP BY Stream` | Error — Name not in GROUP BY | ✅ Only include GROUP BY columns or aggregate functions in SELECT | | ❌ Thinking ORDER BY sorts BEFORE the query runs | It's the LAST step (see SF-WG-HO) | ✅ ORDER BY always happens at the very END | | ❌ Forgetting default sort is ASC | Assumes no keyword = random order | ✅ No keyword = Ascending (ASC) by default | | ❌ `COUNT(*)` and `COUNT(column)` always give same answer | Different if NULLs exist! | ✅ COUNT(*) counts all rows; COUNT(column) skips NULLs in that column | | ❌ HAVING used without GROUP BY | Rarely meaningful alone | ✅ HAVING is designed to work WITH GROUP BY | --- ## 🔑 Quick Revision — Exam Ready! **ORDER BY — One-Line Summary:** - Default = ASC (ascending) - Multiple columns: `ORDER BY col1, col2 DESC` — col1 sorts first, col2 breaks ties - Can sort by a calculated expression too, not just plain columns **Aggregate Functions — Quick Table:** | Function | Purpose | | :--- | :--- | | `SUM()` | Total | | `AVG()` | Average | | `MAX()` | Largest | | `MIN()` | Smallest | | `COUNT(*)` | All rows | | `COUNT(col)` | Non-NULL values in that column | **GROUP BY — One-Line Summary:** - Groups rows sharing the same value(s) - Multiple columns = nested/finer groups - **HAVING** filters GROUPS (after); **WHERE** filters ROWS (before) - **Golden Rule:** Every SELECT column must be in GROUP BY OR wrapped in an aggregate function **The Execution Order:** ``` SELECT → FROM → WHERE → GROUP BY → HAVING → 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. ```sql SELECT * FROM Students ORDER BY Marks DESC; ``` b) Display the number of students in each stream. ```sql SELECT Stream, COUNT(*) FROM Students GROUP BY Stream; ``` c) Display streams having more than 2 students. ```sql 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?** ```sql 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:** ```sql 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:** ```sql 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.