Skip to content
Mentor Subhanu

Class 12 SQL Queries: WHERE vs HAVING, GROUP BY and ORDER BY Explained

By Subhanu Sankar Roy · · 3 min read

SQL is worth a large share of the CBSE Class 12 Computer Science paper, and most lost marks come from a handful of confusions: WHERE versus HAVING, GROUP BY versus ORDER BY, and how NULL behaves. This guide works through each one on a single small table, with the exact output, so students can check their own answers.

The sample table

Table: student (roll, name, city, marks)

  • 1, Aarav, Dubai, 88
  • 2, Diya, Muscat, 72
  • 3, Kabir, Dubai, 95
  • 4, Zara, Doha, 64
  • 5, Ishaan, Muscat, 91
  • 6, Myra, Doha, NULL

WHERE vs HAVING

WHERE filters rows before they are grouped. HAVING filters groups after they are formed. Aggregate functions like AVG() and COUNT() can be used in HAVING, but not in WHERE.

SELECT city, AVG(marks) FROM student
GROUP BY city
HAVING AVG(marks) > 80;

Output: Dubai 91.5 and Muscat 81.5. Doha's average is 64, because AVG ignores Myra's NULL, so Doha is filtered out.

Both together:

SELECT city, COUNT(*) FROM student
WHERE marks > 80
GROUP BY city;

Output: Dubai 2 and Muscat 1. WHERE removes the rows with marks of 80 or less first; Doha has no rows left, so it doesn't appear at all.

GROUP BY vs ORDER BY

GROUP BY combines rows into one row per group, so it is used with aggregate functions. ORDER BY only sorts the result. A query can use both, and ORDER BY always comes last.

SELECT name, marks FROM student
ORDER BY marks DESC;

Output: Kabir 95, Ishaan 91, Aarav 88, Diya 72, Zara 64, Myra NULL. In MySQL, NULL sorts first in ascending order and last in descending order.

COUNT(*) vs COUNT(column)

SELECT city, COUNT(*), COUNT(marks) FROM student
GROUP BY city;

Output: Dubai 2 2, Muscat 2 2, Doha 2 1. COUNT(*) counts rows; COUNT(marks) skips NULL values. SUM, AVG, MIN and MAX also ignore NULL.

NULL needs IS NULL

SELECT name FROM student WHERE marks IS NULL;

Output: Myra. Writing marks = NULL returns no rows at all, because nothing is ever equal to NULL. This shows up often as a find-the-error question.

LIKE patterns

  • % matches any number of characters; _ matches exactly one.
  • WHERE name LIKE '%a' returns names ending in a: Diya, Zara, Myra.
  • WHERE name LIKE '_a%' returns names whose second letter is a: Aarav, Kabir, Zara.

BETWEEN and IN

SELECT name FROM student WHERE marks BETWEEN 72 AND 91;

Output: Aarav, Diya, Ishaan. BETWEEN includes both end values, so 72 and 91 are in.

WHERE city IN ('Dubai', 'Doha') is a shorter way to write city = 'Dubai' OR city = 'Doha'.

Changing tables and data

  • ALTER TABLE student ADD phone VARCHAR(15); adds a column.
  • ALTER TABLE student MODIFY name VARCHAR(40); changes a column's type or size.
  • ALTER TABLE student DROP phone; removes a column.
  • UPDATE student SET marks = 70 WHERE roll = 4; changes data. Without WHERE, it changes every row.
  • DELETE FROM student WHERE roll = 6; removes rows but keeps the table. DROP TABLE removes the table itself.

ALTER, CREATE and DROP are DDL (they change structure). INSERT, UPDATE, DELETE and SELECT are DML (they work with data).

Joins

The syllabus covers the Cartesian product, equi-join and natural join. Suppose a second table fees (roll, amount) exists.

SELECT student.name, fees.amount
FROM student, fees
WHERE student.roll = fees.roll;

Without the WHERE condition, this would be a Cartesian product: every student row paired with every fees row. An equi-join keeps only the matching pairs. A NATURAL JOIN matches on columns with the same name automatically, and shows the common column only once.

A checklist for the exam

  • Aggregate in a condition? Use HAVING, not WHERE.
  • Comparing with NULL? Use IS NULL or IS NOT NULL.
  • Writing output? Count the rows carefully and keep the column order of the SELECT.
  • Clause order: SELECT, FROM, WHERE, GROUP BY, HAVING, ORDER BY.

Related articles