A First Look at Databases

Asking a database a question

Read a query as three separate decisions — which fields, which records, in what order — and work out how much comes back.

A table of five hundred records is no use to anybody as five hundred records. What people actually want is a question answered — which pupils are over sixty, listed by name, best first — and a query is how that question is written down. It comes apart into three separate decisions, and keeping them separate is most of the skill. Which fields do you want back? That is the SELECT. Which records? That is the WHERE. And in what order should they come? That is the ORDER BY. The answer is itself a small table: as many rows as there were records passing the condition, and as many columns as fields were asked for.

A query asks, and never changes anything

The table is exactly as it was before the query ran, whatever the answer looked like. A condition that let twelve records through has not deleted the other four hundred and eighty-eight, or hidden them, or marked them in any way — it simply did not put them in this answer. Run the query again with a different condition and a different twelve come back from the same unchanged table. This is worth being sure of, because the whole point of keeping information in one place is that a hundred different questions can be asked of it without any of them costing anything.

Worked example

What does this query give back, over a table of four hundred records of which thirty have a mark over 80?

SELECT name, house, marks
FROM pupils
WHERE marks > 80
ORDER BY marks

  1. The WHERE settles the rows: thirty records pass it, so the answer has thirty rows.

    Nothing else in the query can change that number. SELECT cannot add a row and ORDER BY cannot remove one.

Try it together

Now work out one answer's shape. A table of members has nine fields and holds two hundred records; sixty of them joined this year, and forty-five of those sixty have paid.

Take the condition first and the fields afterwards — the rows never depend on which fields were asked for.

    1.A query asks for the members who joined this year and have paid. How many rows does the answer have?

    Have a go

    Have a go on your own. A table holds six records whose marks are 22, 58, 77, 91, 44 and 60. How many rows does a query with the condition below give back?

    WHERE marks >= 60

    Ready to practice?

    Eight questions on what you have just read. Nothing is timed, and you can play as many times as you like.

    Print a worksheet