root@vm-learning
~
$
open
ch-3-2
UNIT 3 ▪ CHAPTER 2
11
Advanced SQL
WHERE · LIKE · BETWEEN · IN · ORDER BY · Aggregates · GROUP BY · HAVING · JOIN
Chapter 10 gave you the skeleton of SQL — creating tables and inserting rows. This chapter gives you the muscles: how to find exactly the rows you want with powerful
WHERE clauses, how to summarise data with aggregate functions, and how to combine tables with joins.
Real-life analogy. Chapter 10 taught you how to build a library and put books on its shelves. This chapter teaches you how to be the librarian — answering questions like “find every English novel published before 2000,” “count how many books each author wrote,” and “list book titles alongside the publisher’s address.”
11.1 Sample Tables We’ll Use
Every example in this chapter works on these two tables. Re-create them once and try the queries along with the text.
CREATE TABLE student (
roll INT PRIMARY KEY,
name VARCHAR(40) NOT NULL,
class INT,
marks DECIMAL(5,2),
city VARCHAR(30),
dept_id INT
);
INSERT INTO student VALUES
(1,'Asha', 12, 92.0, 'Delhi', 10),
(2,'Rahul', 12, 78.0, 'Mumbai', 20),
(3,'Riya', 12, 85.0, 'Delhi', 10),
(4,'Ankit', 11, 71.0, 'Pune', 30),
(5,'Meera', 11, 95.0, 'Mumbai', 20),
(6,'Karan', 11, 66.0, NULL, 30),
(7,'Rohan', 12, 55.0, 'Delhi', NULL);
CREATE TABLE department (
dept_id INT PRIMARY KEY,
dname VARCHAR(40) NOT NULL,
head VARCHAR(40)
);
INSERT INTO department VALUES
(10, 'Science', 'Ms. Sharma'),
(20, 'Commerce', 'Mr. Kapoor'),
(30, 'Arts', 'Ms. Dutt'),
(40, 'Sports', 'Mr. Khan');
11.2 The WHERE Clause — Filtering Rows
A SELECT without WHERE returns every row. WHERE keeps only the rows whose condition evaluates to TRUE.
11.2.1 Comparison operators
| Operator | Meaning |
|---|---|
= | Equal to |
<> or != | Not equal to |
> < >= <= | Greater / less than (or equal) |
SELECT name, marks FROM student WHERE marks >= 80;
SELECT name FROM student WHERE city <> 'Delhi';
11.2.2 Logical operators — AND, OR, NOT
-- students of class 12 scoring > 80
SELECT * FROM student WHERE class = 12 AND marks > 80;
-- students from Delhi or Mumbai
SELECT * FROM student WHERE city = 'Delhi' OR city = 'Mumbai';
-- students not in class 11
SELECT * FROM student WHERE NOT class = 11;
11.2.3 BETWEEN … AND …
Selects values inside an inclusive range. The two endpoints are included.
SELECT name, marks FROM student
WHERE marks BETWEEN 70 AND 90;
-- complement
SELECT name, marks FROM student
WHERE marks NOT BETWEEN 70 AND 90;
11.2.4 IN — match against a list
SELECT * FROM student
WHERE city IN ('Delhi', 'Mumbai');
SELECT * FROM student
WHERE class NOT IN (11);
11.2.5 LIKE — pattern matching with wildcards
| Wildcard | Matches |
|---|---|
% | Any sequence of characters (0 or more) |
_ | Exactly one character |
-- names starting with 'R'
SELECT name FROM student WHERE name LIKE 'R%';
-- names ending with 'a'
SELECT name FROM student WHERE name LIKE '%a';
-- names with 'ar' anywhere
SELECT name FROM student WHERE name LIKE '%ar%';
-- five-letter names
SELECT name FROM student WHERE name LIKE '_____';
-- second letter is 'a'
SELECT name FROM student WHERE name LIKE '_a%';
11.2.6 IS NULL / IS NOT NULL
NULL means “unknown” / “missing” — it is not the same as 0 or an empty string. Never use = NULL; always use IS NULL.
-- students without a city filled in
SELECT name FROM student WHERE city IS NULL;
-- students who DO have a department assigned
SELECT name FROM student WHERE dept_id IS NOT NULL;
11.3 Sorting — ORDER BY
-- ascending (default)
SELECT name, marks FROM student ORDER BY marks;
-- descending
SELECT name, marks FROM student ORDER BY marks DESC;
-- two-level sort: class ASC, then marks DESC
SELECT * FROM student ORDER BY class ASC, marks DESC;
11.3.1 LIMIT — first n rows
-- top 3 scorers
SELECT name, marks FROM student
ORDER BY marks DESC
LIMIT 3;
11.4 Aggregate Functions — Summarising a Column
An aggregate function takes many rows and returns a single value (a total, an average, a count).
| Function | Returns | NULL behaviour |
|---|---|---|
COUNT(*) | Number of rows | NULLs counted |
COUNT(col) | Number of non-NULL values in col | NULLs ignored |
COUNT(DISTINCT col) | Distinct non-NULL values | NULLs ignored |
SUM(col) | Total of numeric column | NULLs ignored |
AVG(col) | Average of numeric column | NULLs ignored |
MAX(col) | Largest value | NULLs ignored |
MIN(col) | Smallest value | NULLs ignored |
SELECT COUNT(*) FROM student; -- total students
SELECT AVG(marks), MAX(marks), MIN(marks) FROM student;
SELECT COUNT(DISTINCT city) FROM student; -- unique cities
COUNT(*) counts every row including rows with NULLs; COUNT(column) only counts rows where that column is not NULL. Pick the right one for the question.
11.5 GROUP BY — Aggregate per Category
GROUP BY divides the rows into groups having the same value in the named column(s), and the aggregate is computed for each group.
-- average marks per class
SELECT class, AVG(marks)
FROM student
GROUP BY class;
-- number of students per city
SELECT city, COUNT(*)
FROM student
GROUP BY city;
-- maximum marks in each (class, city) combination
SELECT class, city, MAX(marks)
FROM student
GROUP BY class, city;
Rule of thumb: every column in the
SELECT list must either appear in GROUP BY or be wrapped in an aggregate function.
11.6 HAVING — Filtering on Aggregates
WHERE filters individual rows before grouping. HAVING filters groups after aggregation.
-- classes where the average marks is above 80
SELECT class, AVG(marks) AS avg_marks
FROM student
GROUP BY class
HAVING AVG(marks) > 80;
-- cities with at least 2 students
SELECT city, COUNT(*) AS cnt
FROM student
GROUP BY city
HAVING COUNT(*) >= 2;
11.6.1 WHERE vs HAVING
WHERE | HAVING | |
|---|---|---|
| Operates on | Rows | Groups |
| Runs | Before GROUP BY | After GROUP BY |
| Can use aggregate? | ❌ No | ✅ Yes |
11.7 The Full SELECT — Clause Order
When you combine everything, the clauses must appear in this exact order:
SELECT column_list -- 5
FROM table_list -- 1
WHERE row_conditions -- 2
GROUP BY grouping_columns -- 3
HAVING group_conditions -- 4
ORDER BY sort_columns [ASC|DESC] -- 6
LIMIT n; -- 7
(The numbers show logical execution order, not writing order.)
11.8 Joins — Combining Two Tables
A join lets you retrieve data from two or more tables in a single query, using a linking column (typically a primary-key / foreign-key pair).
11.8.1 Cartesian Product (cross join)
If you list two tables in the FROM clause with no condition, every row of the first table is paired with every row of the second — the Cartesian product. For tables of size m and n, the result has m × n rows.
SELECT * FROM student, department; -- 7 × 4 = 28 rows, mostly nonsense
Cartesian products are almost never what you want — always add a join condition.
11.8.2 Equi-join — matching on equality
Add a WHERE condition that matches the linking columns. This is the classic CBSE form.
SELECT s.name, s.class, d.dname
FROM student s, department d
WHERE s.dept_id = d.dept_id;
11.8.3 Natural Join
A NATURAL JOIN automatically joins on columns that share the same name in both tables — here both tables have dept_id.
SELECT name, class, dname
FROM student NATURAL JOIN department;
11.8.4 Modern INNER JOIN … ON … syntax
This form is recommended — the join condition is visible at the top of the query instead of buried in WHERE.
SELECT s.name, d.dname, d.head
FROM student AS s
INNER JOIN department AS d ON s.dept_id = d.dept_id;
11.8.5 Summary of the three forms
| Form | Syntax | Notes |
|---|---|---|
| Cartesian | FROM a, b | No condition → m × n rows |
| Equi-join | FROM a, b WHERE a.x = b.x | Manual equality condition |
| Natural join | FROM a NATURAL JOIN b | Auto-matches columns with the same name |
| Inner join | FROM a INNER JOIN b ON a.x = b.x | Modern / recommended |
11.9 CBSE-style Worked Queries
11.9.1 Names of students scoring above the class average
SELECT name, marks
FROM student
WHERE marks > (SELECT AVG(marks) FROM student);
11.9.2 City-wise topper list
SELECT city, MAX(marks) AS topper_marks
FROM student
WHERE city IS NOT NULL
GROUP BY city;
11.9.3 Department with the most students
SELECT d.dname, COUNT(*) AS strength
FROM department d INNER JOIN student s
ON d.dept_id = s.dept_id
GROUP BY d.dname
ORDER BY strength DESC
LIMIT 1;
11.9.4 Names containing the letter ‘h’
SELECT name FROM student WHERE name LIKE '%h%';
11.9.5 Students whose name has exactly five letters
SELECT name FROM student WHERE name LIKE '_____';
11.9.6 Students without a department and without a city
SELECT name
FROM student
WHERE dept_id IS NULL OR city IS NULL;
11.10 Common Mistakes to Avoid
| # | Mistake | Fix |
|---|---|---|
| 1 | Using = NULL instead of IS NULL | NULL never equals anything — always IS NULL / IS NOT NULL |
| 2 | WHERE with an aggregate — WHERE AVG(marks) > 80 | Aggregates belong in HAVING, not WHERE |
| 3 | Naked column in SELECT with GROUP BY | It must be in GROUP BY or wrapped in an aggregate |
| 4 | Writing ORDER BY before WHERE | The correct clause order is SELECT / FROM / WHERE / GROUP BY / HAVING / ORDER BY / LIMIT |
| 5 | Joining without a condition (Cartesian) | Always add ON … or WHERE a.x = b.x |
| 6 | Using % wildcard but forgetting LIKE operator | % only works in LIKE patterns |
| 7 | Mixing up _ and % wildcards | _ = exactly one char; % = any sequence |
| 8 | Expecting BETWEEN 70 AND 90 to exclude endpoints | It is inclusive — both 70 and 90 are matched |
Quick-revision summary
WHEREfilters rows — operators:= < > <= >= <>,AND / OR / NOT,BETWEEN,IN,LIKE(%+_),IS NULL.ORDER BY col [ASC|DESC];LIMIT nfor the first n rows.- Aggregate functions:
COUNT,SUM,AVG,MIN,MAX(NULLs ignored except inCOUNT(*)). GROUP BYpartitions rows;HAVINGfilters the resulting groups.- Clause order:
SELECT / FROM / WHERE / GROUP BY / HAVING / ORDER BY / LIMIT. - Joins — Cartesian (no condition), equi-join (manual
WHERE a.x = b.x), natural join (auto-matches same-name columns), INNER JOIN … ON … (modern, recommended).