Query Quest

A form, not a language

You already know what a database table is: rows called records, columns called fields, and a primary key that identifies each row uniquely. SQL is how you ask that table a question, and here is the idea that makes it far easier than it looks. SQL is not a language you have to speak fluently. It is a form you fill in, and the boxes always come in the same order. There are four of them for reading data. SELECT says which columns you want back. FROM says which table to look in. WHERE says which rows to include. ORDER BY says what order to put them in. That is the whole shape, and it never changes: you cannot put WHERE before FROM, and ORDER BY is always last. So when you are faced with a question in an exam, you are not composing anything. You are working out what goes in each of four boxes. Two of them are almost always easy, because the question tells you which table and which columns. The thinking is in the WHERE. There is a second half to this topic as well: three commands that change the data rather than reading it, and those need more care, because a mistake in a SELECT shows you the wrong answer while a mistake in a DELETE removes the wrong rows.

Words for asking a table

Six keywords. Read each definition as the question that clause answers, because that is how you decide what to write in it.

Put the clauses in order

Put the four clauses of a SELECT query into the order they must always be written.

• SELECT, naming the columns you want back
• FROM, naming the table to look in
• WHERE, giving the condition rows must meet
• ORDER BY, giving the field to sort on and the direction

Which rows the query returns

A table called Student holds these records: (1, Adeyemi, 10, Oak), (2, Blake, 11, Ash), (3, Chen, 10, Ash), (4, Dubois, 11, Oak), (5, Ellis, 10, Oak), with fields StudentID, Name, YearGroup and House. What does this query return? SELECT Name FROM Student WHERE YearGroup = 10 ORDER BY Name DESC

• Ellis, Chen, Adeyemi
• Adeyemi, Chen, Ellis
• Adeyemi, Blake, Chen, Dubois, Ellis
• Dubois, Blake

Getting the syntax exactly right

The specification asks for exact syntax, and there are only three things to get right, so they are worth learning properly. Quotes go around text, and not around numbers. A condition comparing a field to a piece of text needs single quotes: WHERE House = 'Ash'. A condition comparing to a number does not: WHERE YearGroup = 10. Writing WHERE House = Ash without the quotes is the single commonest error, because the database reads Ash as the name of a field rather than as a piece of text. ORDER BY needs a direction if you want anything other than ascending. ORDER BY Name gives you A to Z, and so does ORDER BY Name ASC. If you want the reverse you must write DESC. Leaving it off when you meant descending gives you a list that is right in content and wrong in order, which loses the mark. And the condition in WHERE has to be something a record either meets or does not. It is usually a field name, a comparison operator such as =, and a value. If the question asks for two conditions at once you join them with AND or OR. One last piece of advice worth taking. When you write a query in an exam, write the four clauses in order and check each box separately: right columns, right table, right condition with the quotes correct, right sort field with the right direction. Nearly every mark lost on this topic is one of those four boxes, not the idea behind the query.

Match each clause to its question

Complete the query

Complete this query, which should return the names of students in Ash house, listed A to Z. _____ Name _____ Student _____ House = _____ ORDER BY Name _____

Build the SELECT statement

Assemble a query returning the names of Year 11 students, listed A to Z.

The three that change data

The second half of this topic. These do not show you anything: they alter what is stored, which is why the WHERE clause matters far more here than it does in a SELECT.

Which two are true of DELETE

Select the TWO accurate statements about UPDATE and DELETE.

• A DELETE written without a WHERE clause removes every record in the table
• An UPDATE changes values in records that already exist rather than adding new ones
• DELETE returns the rows it has removed so you can check them
• UPDATE without a WHERE clause changes nothing, since no records are selected

Reading a table, predicting output

Your specification asks you to practise reading a table and predicting what a query returns, so here is the method worked through on the Student table: (1, Adeyemi, 10, Oak), (2, Blake, 11, Ash), (3, Chen, 10, Ash), (4, Dubois, 11, Oak), (5, Ellis, 10, Oak). The query: SELECT Name, House FROM Student WHERE YearGroup = 10 ORDER BY Name ASC Work it in the order the database does, which is not the order it is written. First the FROM, because you need to know which table: Student, all five records. Then the WHERE, which filters: YearGroup = 10 keeps Adeyemi, Chen and Ellis, and discards Blake and Dubois. Then the SELECT, which chooses columns: Name and House, so the StudentID and YearGroup columns are dropped from the output. Then the ORDER BY, which sorts what is left: Name ascending, so Adeyemi, Chen, Ellis. The answer is therefore three rows: Adeyemi with Oak, Chen with Ash, Ellis with Oak. Two things are worth taking from that. The first is the order of operations: filter, then choose columns, then sort. Students who work in the written order often forget to drop the columns they were not asked for. The second is that you should write out the surviving records before you sort them, because trying to filter and sort in your head at the same time is where mistakes happen. In an exam with a table printed in front of you, cross out the rows the WHERE excludes. It takes seconds and it makes the rest mechanical.

The query that will not run

Four queries against the Student table. Select the ONE with a syntax error.

• SELECT Name FROM Student WHERE House = 'Oak' ORDER BY Name ASC
• SELECT Name FROM Student WHERE YearGroup = 11
• SELECT Name FROM Student WHERE House = Ash ORDER BY Name DESC
• SELECT Name, House FROM Student ORDER BY YearGroup DESC

SQL drill

Answer from memory. Syntax and clause purpose.

One table, several questions

You are asked to write queries against the Student table. Work through each.

• You need the names of students in Oak house. What condition do you write?
• The results must run from Z to A by name. What do you add?
• You now need to move one student to a different house. Which command?
• Before running it, what should you check most carefully?

Explain how to query a table

Explain how SQL is used to read and change data in a single table. Use the Student table as your example and write out at least two complete queries.

• Name the four clauses of a SELECT query and say what each one decides
• Explain why the clauses must be written in a fixed order
• Write a query returning selected columns for rows meeting a condition, sorted
• Explain when single quotes are needed and when they are not
• Explain what ASC and DESC do, and which one you get if you write neither
• Name the three commands that change data and say what each does
• Explain why a missing WHERE is more serious in a DELETE than in a SELECT
• Finish by describing how you would predict what a query returns from a printed table