SQL: Retrieving Data with SELECT, FROM, WHERE, ORDER BY

What Is SQL?

SQL (Structured Query Language) is the language used to communicate with a relational database. When a website shows you a list of products, when a teacher pulls up your grades, or when a bank checks your balance, SQL is running behind the scenes. In this lesson, we focus on retrieving data — asking the database to show us records that match certain criteria.

We will use the same three tables introduced in the previous lesson throughout all our examples:

Students — student_id (PK), student_name, year_group
Courses — course_id (PK), course_title
Enrolments — enrolment_id (PK), student_id (FK), course_id (FK)

SELECT and FROM

Every retrieval query begins with SELECT, followed by the column names you want to see. Then comes FROM, followed by the table name. If you want all columns, use a star (*) instead of listing them.

Query: Show all data from the Students table.

SELECT * FROM Students;

The * means “all columns”. The database returns every row and every field from the Students table. The semicolon marks the end of the query.

Query: Show only the student_name column from the Students table.

SELECT student_name FROM Students;

This time only one column is returned. You can list multiple columns separated by commas: SELECT student_name, year_group FROM Students;

Filtering with WHERE

Most of the time you don’t want every row — you want rows that meet a condition. The WHERE clause lets you set that condition. The database checks each row and only includes it in the results if the condition is true.

You can use these comparison operators in a WHERE clause: = (equal), <> (not equal), < (less than), > (greater than), <= (less than or equal to), >= (greater than or equal to). You can also combine conditions with AND and OR, and use LIKE with a % wildcard for partial text matches.

Equal to (text value):

SELECT * FROM Students WHERE student_name = ‘Aisha Khan’;

Text values are wrapped in single quotes. This returns only the row where the student’s name is exactly ‘Aisha Khan’.

Greater than (numeric value):

SELECT * FROM Students WHERE year_group > 10;

Returns only students in year 11 (or above, if higher year groups exist). Numbers do not need quotes.

Combining conditions with AND:

SELECT * FROM Students WHERE year_group = 10 AND student_name LIKE ‘A%’;

Both conditions must be true. LIKE ‘A%’ matches any name that starts with the letter A. The % is a wildcard meaning “any characters here”.

Either condition with OR:

SELECT * FROM Students WHERE year_group = 10 OR year_group = 11;

Returns students in year 10 OR year 11 — either condition being true is enough to include the row.

Key Fact

In a WHERE clause: text values go inside single quotes; numbers do not. The wildcard character % is used with LIKE: LIKE ‘%son’ matches anything ending in ‘son’; LIKE ‘A%’ matches anything starting with ‘A’; LIKE ‘%art%’ matches anything containing ‘art’. LIKE goes beyond the four commands the specification names (SELECT, FROM, WHERE, ORDER BY), so treat it as useful extra rather than a guaranteed exam skill.

Sorting with ORDER BY

By default, rows come back in no guaranteed order. To sort the results, add an ORDER BY clause at the end of the query, followed by the column name. Use ASC for ascending order (A to Z, lowest to highest) or DESC for descending order (Z to A, highest to lowest). If you leave out ASC or DESC, the database defaults to ascending.

Alphabetical order:

SELECT student_name FROM Students ORDER BY student_name ASC;

Returns all student names sorted A to Z.

Filtered and sorted together:

SELECT student_name, year_group FROM Students WHERE year_group = 10 ORDER BY student_name DESC;

Returns only year 10 students, but sorted Z to A by name. Notice the clause order: SELECT — FROM — WHERE — ORDER BY. This order is fixed and must not be changed.

Querying Across Two Tables

Exam questions may ask you to retrieve data from two tables at once. You can do this by listing both tables after FROM and adding a WHERE condition that matches the primary key in one table to the foreign key in the other. This effectively joins the tables by finding rows where the key values match.

Task: Find the names of all students enrolled on course number 3.

SELECT Students.student_name FROM Students, Enrolments WHERE Students.student_id = Enrolments.student_id AND Enrolments.course_id = 3;

Step by step: FROM Students, Enrolments tells the database to look at both tables. WHERE Students.student_id = Enrolments.student_id links the two tables through matching key values (primary key = foreign key). The second AND condition filters to course 3 only. You can use TableName.ColumnName notation when the same column name appears in both tables, to avoid ambiguity.