SQL: SELECT Queries

Imagine the school office keeps a giant table of every pupil — hundreds of rows, one per pupil, with columns for their forename, surname, year group, house and date of birth. Now the head of Year 10 asks: "Give me the surnames of everyone in Year 10, in alphabetical order." You could scroll through all those rows by hand… or you could just ask the database. The language you ask in is called SQL — Structured Query Language — and it's how almost every database in the world is questioned, from your school's system to the servers behind your favourite app.

An instruction written in SQL is called a query. The single most important query — the one you'll use far more than any other — is the SELECT statement. SELECT doesn't change anything in the database; it just retrieves data and hands you back a fresh table of results.

A table to query

Everything below queries this one table, called pupil. Each row is one pupil (a record) and each column is one piece of information (a field).

Keep this table in mind — as we build up each query, picture which rows and which columns it would hand back.

The shape of a SELECT query

A SELECT query is read almost like an English sentence. It has up to four parts, and they always come in this order:

SELECT columns -- WHICH fields you want FROM table -- WHICH table to look in WHERE condition -- WHICH rows to keep (optional) ORDER BY column -- how to SORT the results (optional)

Only the first two lines are compulsory: you must say what you want and where to get it. The WHERE and ORDER BY parts are optional extras that let you filter and sort. SQL keywords are usually written in CAPITALS so they stand out, and each statement ends with a semicolon (;).

Choosing columns: * or a named list

Right after SELECT you say which columns you want. The star * is shorthand for "every column":

SELECT * FROM pupil;

This returns the whole table — all columns, all rows:

Usually you don't want everything. List just the columns you need, separated by commas, and the result only contains those:

SELECT forename, surname, house FROM pupil;

Same rows, fewer columns. Naming your columns keeps results tidy and is considered better style than reaching for * every time.

Filtering rows with WHERE

WHERE is a test applied to every row. A row is kept only if the test is true for it. For example, "only pupils in Year 10":

SELECT forename, surname, year FROM pupil WHERE year = 10;

Notice 10 is a number, so it needs no quotes. But text values do need quote marks around them:

SELECT forename, surname FROM pupil WHERE house = 'Kestrel';

You can compare with any of these operators:

For example, "everyone born in or before 2009" and "every surname starting with P":

SELECT surname, dob FROM pupil WHERE dob < '2010-01-01'; SELECT surname FROM pupil WHERE surname LIKE 'P%'; -- Patel, Price, …

Combining tests with AND / OR

You can join several conditions. AND keeps a row only when both tests are true; OR keeps it when either is true.

SELECT forename, surname, house, year FROM pupil WHERE year = 10 AND house = 'Kestrel';

"Year 10 pupils who are in Kestrel" — a row must satisfy both parts.

SELECT forename, surname, house FROM pupil WHERE house = 'Kestrel' OR house = 'Falcon';

"Anyone in Kestrel or Falcon" — a row is kept if either part is true.

Sorting with ORDER BY

A database has no promised order, so if you want the results sorted you must say so. ORDER BY names the column to sort on; add ASC for ascending (A→Z, small→large — the default) or DESC for descending (Z→A, large→small).

SELECT forename, surname FROM pupil WHERE year = 10 ORDER BY surname ASC;

That answers the head of Year 10's original question: the Year 10 pupils, surnames sorted alphabetically. Swap in ORDER BY dob DESC and you'd get the same pupils listed youngest first instead.

The same idea in code

The database does the work of a SELECT for you, but it helps to see what's happening underneath. A table is really just a list of records, and a query is filter (the WHERE), then sort (the ORDER BY), then pick the columns (the SELECT). Here's that exact query written in TypeScript over an array of pupil objects — press Run and compare it with the SQL above:

interface Pupil { forename: string; surname: string; year: number; house: string; } const pupil: Pupil[] = [ { forename: "Ada", surname: "Price", year: 10, house: "Kestrel" }, { forename: "Ben", surname: "Okafor", year: 11, house: "Falcon" }, { forename: "Chloe", surname: "Patel", year: 10, house: "Falcon" }, { forename: "Dev", surname: "Ahmed", year: 10, house: "Kestrel" }, ]; // SELECT forename, surname FROM pupil WHERE year = 10 ORDER BY surname ASC const result = pupil .filter((p) => p.year === 10) // WHERE year = 10 .sort((a, b) => a.surname.localeCompare(b.surname)) // ORDER BY surname ASC .map((p) => p.forename + " " + p.surname); // SELECT forename, surname for (const row of result) console.log(row);

Change the year in the filter, or sort on a different field, and you're doing exactly what a different SELECT query would do.

Digging deeper

SQL is a declarative language: you describe the result you want, and the database figures out the fastest way to fetch it. You never write the loops that scan the rows — there's no "for each pupil, check the year…" in SQL. That's the opposite of the TypeScript above, where you spell out every step. Because the database is free to choose how, it can use clever tricks (like indexes) to answer a query over millions of rows in a blink — something your hand-written loop couldn't match.

SQL grew out of research at IBM in the 1970s, based on ideas by Edgar F. Codd, who invented the relational model — the idea of storing data in linked tables. It was standardised in 1986 and has been the common language of databases ever since. Learn the SELECT statement once and you can query almost any database you'll ever meet.

Two classic mix-ups to avoid: