Asking questions: SELECT, WHERE, ORDER BY and GROUP BY

Every single-table query uses the same skeleton:

SELECT   which fields you want
FROM     which table
WHERE    which rows qualify
ORDER BY how to sort the result;

Only SELECT and FROM are compulsory. The others slot in at fixed positions, and putting them in the wrong order is a syntax error — not a matter of style.

SELECT Name, Mark
FROM Student
WHERE ClassID = 1
ORDER BY Mark DESC;

That reads as: "give me the name and mark of every student in class 1, highest mark first."

WHERE takes a condition and keeps only the rows where it is true.

Two rules that catch people out:

• Text goes in single quotes. WHERE Grade = 'A' works; WHERE Grade = A makes the DBMS look for a field named A.
• Dates are compared as text in date order: WHERE Enrolled > '2026-09-01'. That only works because DATE fields sort correctly — one more reason not to store dates in a VARCHAR.

ORDER BY sorts the result rows. It goes last, and it sorts by a field, not by a row number:

SELECT Name, Mark FROM Student ORDER BY Mark DESC;
SELECT Name, Mark FROM Student ORDER BY Mark DESC, Name ASC;

• ASC is ascending (smallest first) and is the default, so ORDER BY Mark means ascending.
• DESC is descending (largest first).
• With several fields, the first is the main sort and the second breaks ties.

ORDER BY changes only the order of the output. It does not change which rows are returned and it does not change the table itself.

Three aggregate functions turn many rows into one number:

SUM and AVG need a numeric field. COUNT(*) counts rows, including rows that have empty fields; COUNT(field) counts only rows where that field is filled in.

On its own, an aggregate returns one row. That is why this is legal even though Name is not aggregated — it is not selected at all:

SELECT AVG(Mark) FROM Student WHERE ClassID = 1;

AVG(Mark) for the whole school is rarely what you want. You want the average per class. That is GROUP BY: it splits the rows into groups that share a value, then applies the aggregates to each group.

SELECT ClassID, COUNT(*) AS NumberOfStudents, AVG(Mark) AS AverageMark
FROM Student
GROUP BY ClassID;

Result: one row per class, with the count and average for that class.

One rule, and it is the one that loses marks: every field in SELECT that is not inside an aggregate must appear in GROUP BY. SELECT ClassID, Name, COUNT(*) ... GROUP BY ClassID is invalid, because there are many names per class and the DBMS cannot choose one.

GROUP BY comes after WHERE and before ORDER BY:

SELECT ClassID, AVG(Mark)
FROM Student
WHERE Mark >= 40
GROUP BY ClassID
ORDER BY AVG(Mark) DESC;