Advanced Querying

Introduction

A SELECT query so far returned rows in whatever order the table happened to store them. This topic covers controlling that order explicitly.

Concept

SELECT columns FROM table ORDER BY column [ASC | DESC];

Sorts the result by column, ascending (ASC, the default) or descending (DESC). Multiple columns can be listed, sorting by the first, then breaking ties using the next.

Worked Example

SELECT Name, Marks FROM STUDENT ORDER BY Marks DESC;
SELECT Stream, Name FROM STUDENT ORDER BY Stream ASC, Name ASC;

The first query returns every student, highest Marks first. The second sorts by Stream alphabetically first, and — for students sharing the same Stream — sorts those by Name alphabetically as a tiebreaker, exactly like Pandas' sort_values() from earlier in this course.

Note: ORDER BY always runs after WHERE has already filtered the rows — sorting applies to whichever rows made it through the filter, never to the whole table regardless of WHERE.

Explanation

Listing a second sort column only matters when the first column has ties — sorting by Stream alone leaves same-Stream students in an unpredictable relative order; adding , Name ASC makes that order fully deterministic instead.

Summary

ORDER BY column ASC|DESC sorts a query's result — ASC (default) or DESC, with multiple columns breaking ties in the order they're listed. Sorting applies after WHERE has already filtered the rows.

/* ==================================================
   SYSTEM SETUP (Unsorted Data)
   ================================================== */
DROP TABLE IF EXISTS RESULT;
CREATE TABLE RESULT (Name VARCHAR(20), Marks INT);
INSERT INTO RESULT VALUES ('Zoya', 85);
INSERT INTO RESULT VALUES ('Amit', 92);
INSERT INTO RESULT VALUES ('Ravi', 70);
INSERT INTO RESULT VALUES ('Bina', 92);

/* ==================================================
   TASK A: Sort Alphabetically (ASC is default)
   ================================================== */
SELECT * FROM RESULT 
ORDER BY Name; 

/* ==================================================
   TASK B: Sort by Merit (High to Low)
   ================================================== */
SELECT * FROM RESULT 
ORDER BY Marks DESC;

/* ==================================================
   SYSTEM SETUP (Amit and Bina have 92)
   ================================================== */
DROP TABLE IF EXISTS RESULT;
CREATE TABLE RESULT (Name VARCHAR(20), Marks INT);
INSERT INTO RESULT VALUES ('Zoya', 85);
INSERT INTO RESULT VALUES ('Amit', 92);
INSERT INTO RESULT VALUES ('Ravi', 70);
INSERT INTO RESULT VALUES ('Bina', 92);

/* ==================================================
   TASK: Multi-Level Sort
   1. Primary: Marks DESC (92 comes first)
   2. Secondary: Name ASC (Amit comes before Bina)
   ================================================== */
SELECT * FROM RESULT 
ORDER BY Marks DESC, Name ASC;

Introduction

The aggregate functions from earlier in this unit summarised an entire column into one value. This topic covers computing that same summary separately for each category — the SQL equivalent of Pandas' groupby().

Concept

SELECT column, aggregate_function(other_column) FROM table GROUP BY column;

Splits rows into groups sharing the same value in column, then computes the aggregate function within each group separately, one result row per group.

Worked Example

SELECT Stream, AVG(Marks) FROM STUDENT GROUP BY Stream;
SELECT Stream, COUNT(*) FROM STUDENT GROUP BY Stream;

The first query returns one row per Stream, each showing that stream's own average Marks — Science's average computed only from Science students, Commerce's only from Commerce students, not one overall average. The second returns how many students are in each Stream.

Note: Every column named in SELECT (other than inside an aggregate function) must also appear in GROUP BY — SELECT Stream, Name, AVG(Marks) ... GROUP BY Stream would be invalid, since Name isn't grouped and doesn't have one single value per group.

Explanation

This is exactly the same idea as Pandas' df.groupby("column")["other"].mean() from earlier in this course, just performed by the database directly — "break the data into categories, then summarise within each one" is the identical operation, in two different tools.

Summary

GROUP BY column splits rows into groups by that column's values, and any aggregate function in SELECT is then computed separately per group — the SQL counterpart to Pandas' groupby(), closing out this unit's SQL coverage before the final chapter on combining multiple tables.

/* ==================================================
   SYSTEM SETUP (Staff Data)
   Notice we have multiple people in 'HR' and 'IT'.
   ================================================== */
DROP TABLE IF EXISTS STAFF;
CREATE TABLE STAFF (Name VARCHAR(20), Dept VARCHAR(10), Salary INT);
INSERT INTO STAFF VALUES ('Amit', 'HR', 50000);
INSERT INTO STAFF VALUES ('Ravi', 'IT', 80000);
INSERT INTO STAFF VALUES ('Sita', 'HR', 55000); -- Another HR
INSERT INTO STAFF VALUES ('Tina', 'IT', 85000); -- Another IT
INSERT INTO STAFF VALUES ('Raj', 'Admin', 40000);

/* ==================================================
   TASK A: Count per Dept
   Result should be: HR=2, IT=2, Admin=1
   ================================================== */
SELECT 
    Dept, 
    COUNT(*) AS Staff_Count 
FROM STAFF 
GROUP BY Dept;

/* ==================================================
   TASK B: Average Salary per Dept
   ================================================== */
SELECT 
    Dept, 
    AVG(Salary) AS Avg_Pay 
FROM STAFF 
GROUP BY Dept;

/* ==================================================
   TASK: Filter Groups
   We use HAVING because we are checking an Aggregate (AVG)
   ================================================== */
SELECT 
    Dept, 
    AVG(Salary) AS Avg_Pay 
FROM STAFF 
GROUP BY Dept 
HAVING AVG(Salary) > 60000;