Query Quest: Select, From, Where

Three words, and a very short list

On the arrays topic you built a grid: one row per record, one column per field, the same shape a database table has. And it ended on a limitation - holding the data is not the same as being able to ask it anything. This is the asking. A query is a question put to a table, and the table hands back an answer. ⚠️ Now the part no revision video will tell you, and it is worth real time. On this specification the whole of it is three clauses: SELECT, FROM and WHERE. That is the entire required language. Material written for other exam boards will teach you INSERT, UPDATE and DELETE, and primary keys and foreign keys and why tables get split up. None of that is required here. ⚠️ Knowing exactly where the edge sits is worth as much as anything else in this module, because it means you can learn three clauses properly instead of half-learning eight.

The three clauses, and what they pick

Three clauses and two words for what happens when you run them. Every one of these is in scope; nothing else on this topic is.

Columns, table, rows

Three clauses, three completely different jobs. Read the bottom row of each column, because that is the bit that gets muddled.

How to read a query before answering

Take it a part at a time, and ask one question of each part: what is this choosing? ⚠️ Some parts choose which columns appear. One chooses which rows appear. Getting those two the wrong way round is the commonest mistake on this whole topic, and it is a nasty one, because the answer it produces looks perfectly confident and is completely wrong. Then predict the SHAPE of the answer before you work out the contents. How many columns wide will it be, and roughly how many rows long? Doing that first catches the mix-up immediately: if you thought the row-choosing part decided the columns, your predicted shape comes out obviously wrong before you have written anything down. A weak answer reads the query back in English. A strong answer says which records survive the test and which fields are displayed - two clauses, and they are not the same clause.

Match each part to what it chooses

Rows or columns

A query asks for the Name and Form fields from a Students table, for every student whose Year is 9. Which part of the query decided that only Year 9 students appear in the answer?

• The WHERE clause, because it decides which records are returned
• The SELECT clause, because it lists what the query is asking for
• The FROM clause, because it names the table
• The result, because the rows are filtered after they come back

One table, one question

Here is a Students table with three fields, Name, Year and Form: Ada - 9 - 9B Bo - 10 - 10A Cai - 9 - 9A Dee - 11 - 11C And here is the query: SELECT Name, Form FROM Students WHERE Year = 9 Work it one clause at a time. FROM Students says which table. Four records are in play. WHERE Year = 9 sets the test. Ada passes, Bo does not, Cai passes, Dee does not. Two records survive. SELECT Name, Form says which fields to show. Two columns, and Year is not one of them - even though it was the field the test was based on, which surprises people. So the result is two rows and two columns: Ada with 9B, and Cai with 9A. ⚠️ And look at the table again. It is exactly as it was. Nobody was removed, nothing was edited. A query gives you a view of what is stored; it does not alter it. On this paper that is always true, because the clauses that would change stored data are not part of what you are required to know.

How many rows come back

Using the same Students table shown above, a query asks for every student whose Year is 9. Work out how many rows the result will contain.

Assemble the question

Put the three clauses into the right places to ask a table for the name and form of every Year 9 student.

Complete the query clauses paragraph

The clause that names which fields are displayed in the answer is _____. The clause that names the table being asked is _____. The clause that sets the test a record must pass in order to be included is _____. Run together they produce a _____, and nothing about the table being questioned is altered by the asking.

Which clause

Five in a row, three lives. The last two are about the edges of what this paper asks for.

Spot the true query facts

Tap the TWO statements that are true.

• The WHERE clause decides which records are returned
• Running a query leaves the stored table exactly as it was
• The SELECT clause decides which records are returned
• A query has to include ORDER BY before it will run

Three questions of one table

Three situations. Choose the answer you could defend in writing.

• A query returns every field of a table, but only three of its twenty records. Which part of the query did that work?
• A classmate revising from another exam board's notes asks whether they need to learn DELETE for this paper. What do you tell them?
• A student wants their result sorted alphabetically and cannot find the clause anywhere in their notes for this course. What do you tell them?

Explain how to ask a table a question

A friend has been revising SQL from a textbook written for a different exam board and is worried by how much of it they cannot remember. Write them the answer that sorts it out.

• Name the three clauses this specification requires and say what each one chooses
• Explain the difference between what SELECT decides and what WHERE decides
• Explain what a query hands back, and what it does to the table it was asked of
• Work through a short query against a small table and say exactly what the result contains
• Finish by saying what this specification does NOT require you to know about SQL, and why that is worth knowing