A query inside a query, and the value that means "unknown"

A nested query is a function call that returns a set

A nested query places one SELECT inside another's WHERE clause, similar to calling one function from inside another in C, except the inner query's result is a whole set of values, not one return value. "Find students whose branch appears in the merit-list branches" naturally nests: the inner query computes the set of merit-list branches, and the outer query's WHERE ... IN checks each student's branch against that set.

A nested query can also appear after a comparison operator when it is known to return exactly one value — WHERE fee > (SELECT AVG(fee) FROM Student) finds students paying above the average, computing that average freshly with each query run rather than a hardcoded number that could go stale.

SELECT name
FROM Student
WHERE branch IN (
  SELECT branch FROM MeritListBranches
);

Aggregates: turning many rows into one number

COUNT, SUM, AVG, MIN, and MAX each collapse a set of rows into a single value — COUNT(*) counts rows, SUM(fee) totals a column, AVG(fee) averages it. Combined with GROUP BY, they answer per-category questions instead of one grand total — SELECT branch, COUNT(*) FROM Student GROUP BY branch gives a headcount for every branch in one query, rather than running the same COUNT once per branch by hand.

NULL is not zero, not empty text, and not false

NULL represents an unknown or inapplicable value — a student who has not yet declared a minor has NULL in that column, not an empty string or a zero. Any arithmetic or comparison involving NULL produces NULL, not true or false — fee = NULL is never true, even for a row whose fee genuinely is NULL, because SQL cannot confirm an unknown value equals anything, including another unknown.

This is why SQL provides IS NULL and IS NOT NULL as separate operators instead of reusing = and != — checking for NULL is a different kind of question ("is this value unknown") than checking for equality ("does this value match that one"), and the three-valued logic (true, false, unknown) this creates is a genuinely new way of reasoning compared to C's plain true/false.