SQL: SELECT, WHERE, and ORDER BY

Structured Query Language (SQL) is the standard declarative language for working with relational databases. "Declarative" means you describe what data you want, not how to fetch it — the DBMS's query optimiser decides the procedure. This lesson covers the single most important SQL statement, SELECT, together with the two clauses that shape its output: WHERE (which rows) and ORDER BY (in what order). These are examined in almost every database paper, usually by asking you to write a query by hand against a given schema, so the goal here is not just to recognise SQL but to be able to compose it accurately, with correct clause order, operators and string quoting.

Throughout this lesson — and the JOIN lesson that follows — we use one consistent sample schema so the queries build on each other.

---

Spec Mapping

This lesson covers the AQA A-Level Computer Science (7517) Fundamentals of databases requirement to use SQL to retrieve data: SELECT … FROM … WHERE … ORDER BY; comparison and logical operators; pattern matching with LIKE and wildcards; DISTINCT; and NULL handling. It is the retrieval half of SQL; the companion lessons cover joins, data modification (INSERT/UPDATE/DELETE), aggregation (GROUP BY) and the transaction model.

---

The Sample Schema

We will query three related tables — Student, Course and Enrolment (a junction table resolving the many-to-many between them).

Student

StudentID	FirstName	Surname	FormGroup	Email	DateOfBirth
1	Alice	Chen	13A	alice@school.uk	2006-09-15
2	Bob	Patel	13A	bob@school.uk	2007-03-22
3	Carol	Smith	13B	NULL	2006-12-01
4	Dev	Lin	13B	dev@school.uk	2007-01-30
5	Erin	Shah	13A	erin@school.uk	2006-07-08

Course

CourseID	CourseName	Level
C1	Computer Science	A-Level
C2	Mathematics	A-Level
C3	Physics	A-Level

Enrolment

StudentID	CourseID	Grade
1	C1	85
1	C2	78
2	C1	91
3	C2	64
4	C3	72
5	C1	88

We use Student/Course/Enrolment for the WHERE and ORDER BY examples below, drawing in single tables where that keeps a worked result table small and readable.

---

The SELECT Statement

SELECT retrieves data. Its simplest form names columns and a table.

Select specific columns

SELECT FirstName, Surname, FormGroup
FROM Student;

Result:

FirstName	Surname	FormGroup
Alice	Chen	13A
Bob	Patel	13A
Carol	Smith	13B
Dev	Lin	13B
Erin	Shah	13A

Select all columns with *

SELECT *
FROM Course;

Result:

CourseID	CourseName	Level
C1	Computer Science	A-Level
C2	Mathematics	A-Level
C3	Physics	A-Level

The asterisk selects every column. It is convenient for exploration but, in production code, naming columns explicitly is clearer and more efficient (the DBMS reads only what it needs, and the query does not silently break if a column is added).

Aliases with AS

SELECT FirstName AS "First name", Surname AS "Family name"
FROM Student;

This renames the output columns to "First name" and "Family name" without changing the stored data.

DISTINCT — remove duplicate rows

SELECT DISTINCT FormGroup
FROM Student;

Result:

FormGroup
13A
13B

Without DISTINCT, this query would list 13A three times and 13B twice. DISTINCT collapses identical result rows to one each — connecting neatly to the idea that a relation is mathematically a set.

---

The WHERE Clause

WHERE filters rows: only those for which the condition evaluates to true are returned.

Comparison operators

Operator	Meaning
=	Equal to
<> or !=	Not equal to
<	Less than
>	Greater than
<=	Less than or equal to
>=	Greater than or equal to

Worked example — a single condition

SELECT FirstName, Surname
FROM Student
WHERE FormGroup = '13A';

Result:

FirstName	Surname
Alice	Chen
Bob	Patel
Erin	Shah

Note the single quotes around the text value '13A'. Numeric literals are not quoted; strings and dates are.

Logical operators: AND, OR, NOT

-- Students in 13A born in 2006 or earlier
SELECT FirstName, Surname, DateOfBirth
FROM Student
WHERE FormGroup = '13A' AND DateOfBirth <= '2006-12-31';

Result:

FirstName	Surname	DateOfBirth
Alice	Chen	2006-09-15
Erin	Shah	2006-07-08

-- Students who are NOT in 13A
SELECT FirstName, Surname, FormGroup
FROM Student
WHERE NOT FormGroup = '13A';

Result:

FirstName	Surname	FormGroup
Carol	Smith	13B
Dev	Lin	13B

When combining AND and OR, use parentheses to make precedence explicit: WHERE A AND (B OR C) is not the same as (A AND B) OR C. AND binds more tightly than OR.

BETWEEN — inclusive range

-- Enrolments with a grade from 70 to 89 inclusive
SELECT StudentID, CourseID, Grade
FROM Enrolment
WHERE Grade BETWEEN 70 AND 89;

Result:

StudentID	CourseID	Grade
1	C1	85
1	C2	78
4	C3	72
5	C1	88

BETWEEN x AND y is inclusive of both endpoints — a frequent exam trap.

IN — membership in a set

SELECT FirstName, Surname, FormGroup
FROM Student
WHERE FormGroup IN ('13B', '13C');

Result:

FirstName	Surname	FormGroup
Carol	Smith	13B
Dev	Lin	13B

IN (…) is shorthand for several OR comparisons.

LIKE — pattern matching with wildcards

-- Surnames beginning with 'S'
SELECT FirstName, Surname
FROM Student
WHERE Surname LIKE 'S%';

Result:

FirstName	Surname
Carol	Smith
Erin	Shah

-- First names containing 'e' anywhere
SELECT FirstName
FROM Student
WHERE FirstName LIKE '%e%';

Result:

FirstName
Dev
Erin

The two wildcards are:

• % — matches any sequence of characters, including none.
• _ — matches exactly one character. For example, LIKE '1_A' would match 13A but not 13AA.

LIKE is for patterns; use = for an exact match.

IS NULL / IS NOT NULL

-- Students with no email recorded
SELECT FirstName, Surname
FROM Student
WHERE Email IS NULL;

Result:

FirstName	Surname
Carol	Smith

You must write IS NULL, never = NULL. NULL represents unknown, and any comparison with an unknown (including NULL = NULL) evaluates to UNKNOWN, not TRUE — so = NULL matches nothing. This is one of the most commonly penalised SQL mistakes.

---

The ORDER BY Clause

ORDER BY sorts the result rows.

• ASC — ascending (A→Z, 0→9, earliest→latest); this is the default.
• DESC — descending.

-- Enrolments by grade, highest first
SELECT StudentID, CourseID, Grade
FROM Enrolment
ORDER BY Grade DESC;

Result:

StudentID	CourseID	Grade
2	C1	91
5	C1	88
1	C1	85
1	C2	78
4	C3	72
3	C2	64

Sorting by multiple columns

-- By form group A→Z, then surname A→Z within each group
SELECT FirstName, Surname, FormGroup
FROM Student
ORDER BY FormGroup ASC, Surname ASC;

Result:

FirstName	Surname	FormGroup
Alice	Chen	13A
Bob	Patel	13A
Erin	Shah	13A
Dev	Lin	13B
Carol	Smith	13B

The first column listed is the primary sort; the second breaks ties within the first, and so on.

---

Combining SELECT, WHERE, and ORDER BY

SELECT FirstName, Surname, DateOfBirth
FROM Student
WHERE FormGroup = '13A'
ORDER BY DateOfBirth ASC;

Result:

FirstName	Surname	DateOfBirth
Erin	Shah	2006-07-08
Alice	Chen	2006-09-15
Bob	Patel	2007-03-22

The clauses must appear in the fixed order SELECT → FROM → WHERE → ORDER BY. Writing ORDER BY before WHERE is a syntax error.

---

Logical Processing Order vs. Written Order

A subtle but examinable point: the order in which you write the clauses is not the order in which the DBMS evaluates them. The logical processing order is:

1. FROM — identify the source table(s).
2. WHERE — discard rows that fail the condition.
3. SELECT — keep (and rename/compute) the chosen columns.
4. ORDER BY — sort the surviving rows.

This explains a common error: a column alias defined in SELECT cannot normally be used in WHERE, because WHERE is processed before SELECT exists. For example, SELECT Grade AS Mark FROM Enrolment WHERE Mark > 80 typically fails — you must write WHERE Grade > 80. (An alias can usually be used in ORDER BY, because that runs last.)

---

More Worked Queries

Building fluency requires seeing the operators combined. Each query below runs against the sample schema.

Combining AND and OR with parentheses

-- Students who are either in 13A, OR were born before 2007 (in any group)
SELECT FirstName, Surname, FormGroup, DateOfBirth
FROM Student
WHERE FormGroup = '13A' OR DateOfBirth < '2007-01-01'
ORDER BY Surname ASC;

Result:

FirstName	Surname	FormGroup	DateOfBirth
Alice	Chen	13A	2006-09-15
Bob	Patel	13A	2007-03-22
Erin	Shah	13A	2006-07-08
Carol	Smith	13B	2006-12-01

Carol (13B) qualifies via the second condition (born 2006); Dev (13B, born 2007) satisfies neither and is excluded. Compare with the more restrictive version using AND:

-- Students who are in 13A AND were born before 2007
SELECT FirstName, Surname, DateOfBirth
FROM Student
WHERE FormGroup = '13A' AND DateOfBirth < '2007-01-01'
ORDER BY DateOfBirth ASC;

Result:

FirstName	Surname	DateOfBirth
Erin	Shah	2006-07-08
Alice	Chen	2006-09-15

AND requires both conditions; OR requires at least one. This is the Boolean logic from elsewhere in the specification applied directly.

NOT IN

-- Students who are NOT in 13A or 13B (none, here — demonstrates the operator)
SELECT FirstName, Surname, FormGroup
FROM Student
WHERE FormGroup NOT IN ('13A', '13B');

Result: (no rows — every student is in 13A or 13B). NOT IN negates set membership; beware that if the list contained a NULL, NOT IN can behave unexpectedly because of three-valued logic.

Pattern matching with the single-character wildcard

-- Form groups matching the pattern '13' followed by exactly one character
SELECT DISTINCT FormGroup
FROM Student
WHERE FormGroup LIKE '13_';

Result:

FormGroup
13A
13B

The _ matches exactly one character, so '13_' matches 13A and 13B but would not match a hypothetical '13' or '13AB'.

Filtering then ordering on a numeric column

-- Enrolments above a pass mark of 70, best first
SELECT StudentID, CourseID, Grade
FROM Enrolment
WHERE Grade > 70
ORDER BY Grade DESC;

Result:

StudentID	CourseID	Grade
2	C1	91
5	C1	88
1	C1	85
1	C2	78
4	C3	72

Note > 70 is strict: a grade of exactly 70 would be excluded. Compare with >= 70 (inclusive) — boundary precision is a frequent source of lost marks.

---

Quoting, Case and Style Conventions

• String and date literals are wrapped in single quotes: '13A', '2007-01-01'. Numbers are not quoted.
• SQL keywords are conventionally UPPERCASE (SELECT, FROM, WHERE) to stand out from identifiers, though SQL itself is not case-sensitive on keywords.
• Identifiers (table/column names) may or may not be case-sensitive depending on the DBMS; be consistent.
• Statements end with a semicolon ;.
• Lay each clause on its own line in exam answers — it makes the structure (and your marks) clear to the examiner.

---

Worked Exercise — A Set of Queries on One Schema

To pull the operators together, here is a connected set of tasks on the Student/Enrolment tables, each with its query and result.

Task A: List the forename and surname of all students whose surname begins with a letter from A to M, sorted alphabetically by surname.

SELECT FirstName, Surname
FROM Student
WHERE Surname < 'N'
ORDER BY Surname ASC;

Result:

FirstName	Surname
Alice	Chen
Dev	Lin

(Surname < 'N' works because string comparison is alphabetical; "Chen" and "Lin" sort before "N", whereas "Patel", "Shah" and "Smith" do not.)

Task B: Show the distinct grades that appear in the Enrolment table, highest first.

SELECT DISTINCT Grade
FROM Enrolment
ORDER BY Grade DESC;

Result:

Grade
91
88
85
78
72
64