SELECT, WHERE, ORDER BY and LIMIT

The four clauses that answer ninety percent of real questions, with output shown for each query.

The table we are working on

Keep this in front of you. Every query below runs against it.

roll_no	name	branch	year	cgpa
101	Anita Sharma	CSE	2	8.40
102	Ravi Kumar	CSE	2	7.10
103	Meera Nair	ECE	3	9.05
104	Imran Qureshi	CSE	1	6.80
105	Sneha Patil	ECE	2	8.90
106	Arjun Das	CSE	3	5.95

SELECT picks columns

SQL

SELECT name, cgpa FROM students;

You get six rows and two columns. SELECT gives every column, which is fine while learning and a bad habit in real code — if someone later adds a password_hash column, every SELECT in your project starts dragging it around.

WHERE picks rows

WHERE is a filter applied to each row on its own. The row either survives or it does not.

SQL

SELECT name, cgpa
FROM students
WHERE branch = 'CSE' AND cgpa >= 7;

Result:

name	cgpa
Anita Sharma	8.40
Ravi Kumar	7.10

Ravi at 7.10 passes because the test is >=. Arjun at 5.95 fails. Meera is CSE-less, so she is out even though her cgpa is the highest in the table.

Useful operators you will actually use:

= <> < <= > >= — the obvious ones. Note SQL uses <> for "not equal", though most databases also accept !=.
BETWEEN 7 AND 9 — inclusive on both ends. 7 and 9 are included.
IN ('CSE', 'ECE') — shorthand for a chain of ORs.
LIKE 'A%' — % matches any number of characters, _ matches exactly one. So 'A%' finds Anita and Arjun.
IS NULL / IS NOT NULL — the only correct way to test for missing values.

SQL

SELECT name FROM students WHERE year IN (1, 2) AND name LIKE '%a%';

Anita, Ravi, Imran and Sneha all contain a lowercase a and are in year 1 or 2.

⚠️

LIKE case sensitivity depends on the database and even on the column's collation. In MySQL with the default collation, LIKE 'a%' also matches Anita. In PostgreSQL it does not — you need ILIKE. Do not assume; test on the database your college actually uses in the lab.

ORDER BY sorts the result

SQL

SELECT name, branch, cgpa
FROM students
ORDER BY cgpa DESC;

name	branch	cgpa
Meera Nair	ECE	9.05
Sneha Patil	ECE	8.90
Anita Sharma	CSE	8.40
Ravi Kumar	CSE	7.10
Imran Qureshi	CSE	6.80
Arjun Das	CSE	5.95

ASC is the default and is usually left out. You can sort on several columns: ORDER BY branch ASC, cgpa DESC groups the branches alphabetically and, inside each branch, puts the highest cgpa first.

Sorting happens after filtering. The database throws rows away first, then sorts what is left. That is why a good WHERE makes a query fast and a fancy ORDER BY never does.

LIMIT cuts the result short

SQL

SELECT name, cgpa
FROM students
ORDER BY cgpa DESC
LIMIT 3;

Meera, Sneha, Anita. That is your "top 3" query, and it is the standard interview follow-up.

LIMIT 3 OFFSET 3 skips the first three and gives the next three — that is how page 2 of any website's list is built.

⚠️

LIMIT without ORDER BY is meaningless. A table is a set with no order, so "the first 3 rows" is whatever the storage engine felt like returning. It may look stable on your tiny test table and then change completely once the table has a lakh of rows. Always pair LIMIT with ORDER BY.

The order the database actually runs things

Write it in this order:

SELECT → FROM → WHERE → ORDER BY → LIMIT

But the database evaluates it in this order:

FROM → WHERE → SELECT → ORDER BY → LIMIT

FROM gets the rows. WHERE throws most of them away. SELECT then picks columns and computes expressions. Only then does sorting happen, and finally the cut.

This is not trivia. It explains a real error. A column alias created in SELECT does not exist yet when WHERE runs:

SQL

SELECT cgpa * 9.5 AS approx_percent
FROM students
WHERE approx_percent > 80;

That fails on most databases with "unknown column approx_percent". WHERE cgpa * 9.5 > 80 works. Remember the evaluation order and errors like this stop being mysterious.