Computer ScienceCore20 min read

Table and Query

Asking the database a question, and getting only the rows you wanted

This topic appears in:

2nd Year Computer Science · ch.6O Level Computer Science (2210) · ch.9
01

A query is a saved question

Once data is in tables, almost everything useful is a query: which students failed, which books are overdue, how many sales each region made. A query stores the question and produces the answer afresh from the tables every time it runs, so it is always current.

In Access a query is built in the Query Design grid, and the same query can be viewed as SQL — the language the grid is really generating. Both appear in the exam.

SELECT field list-- which columnsFROMtable(s)-- where they come fromWHEREcondition-- which rowsORDER BY field ASC|DESC-- what orderSELECT and FROM are compulsory; WHERE and ORDER BY are optional

Compare >60 AND 10A with >60 OR 10A on the same table. AND always returns fewer rows than either condition alone; OR always returns more. Swapping them is the commonest error in a query question.

02

Criteria

The criteria row of the design grid — the WHERE clause in SQL — decides which rows appear. Criteria on the same row of the grid are combined with AND; criteria on different rows are combined with OR.

Text values are enclosed in quotation marks and dates in hash symbols; numbers are written bare. Forgetting the quotation marks is the most frequent syntax error.

CriterionSelects
>60values greater than 60
>=60 And <=80the range 60 to 80 inclusive
"10A"exactly that text
Like "A*"text beginning with A
Like "*khan*"text containing khan anywhere
Between #1/1/2026# And #31/1/2026#dates within January
Is Nullrecords where the field is empty
Not "10A"everything except that value

Empty is not the same as zero

A blank Marks field means the mark is unknown; a Marks field containing 0 means the student scored nothing. Is Null finds the first, =0 finds the second, and an average calculated over the column ignores the nulls but includes the zeros — producing two different answers. Treating a missing value as zero is the classic way to corrupt a report.

03

Queries across more than one table

Normalisation spread the data across several tables, so most real queries must bring it back together. A query listing student names alongside their class teacher needs both the STUDENT and CLASS tables, joined on the matching key.

Access joins them automatically if a relationship has been defined, which is one practical reason for setting the relationships up first. In SQL the join is written explicitly, matching the foreign key to the primary key it refers to.

Worked example

STUDENT(RollNo, Name, ClassID) and CLASS(ClassID, ClassName, Teacher). Write a query listing each student's name with their teacher, for class 10A only, sorted by name.

  1. Both tables are needed, joined on ClassID — the foreign key in STUDENT matching the primary key in CLASS.Without the join, every student would be paired with every class.
  2. SELECT Name, Teacher — only the two columns actually asked for.Selecting everything and letting the reader find the columns loses marks and is slower.
  3. FROM STUDENT INNER JOIN CLASS ON STUDENT.ClassID = CLASS.ClassIDThe join condition states which values must match.
  4. WHERE ClassName = "10A"Text in quotation marks. This is applied after the join, so it can use fields from either table.
  5. ORDER BY Name ASCAscending is the default, but stating it costs nothing and removes any doubt.

SELECT Name, Teacher FROM STUDENT INNER JOIN CLASS ON STUDENT.ClassID = CLASS.ClassID WHERE ClassName = "10A" ORDER BY Name

04

Calculations, totals and the other kinds of query

A query can produce columns that exist in no table. A calculated field is written as a name followed by a colon and an expression — Total: [Price] * [Quantity] — and is recomputed every time the query runs, which is why the result should never be stored in a table as well.

A totals query groups rows and summarises each group: count the students in each class, or average the marks per subject. The aggregate functions are Count, Sum, Avg, Min and Max.

Beyond selection there are action queries, which change the data rather than reading it: update, append, delete and make-table. These are irreversible in Access, so a backup before running one is not optional.

Never store a value you can calculate

If a table holds Price, Quantity and Total, the three can disagree the moment someone edits one of them — a classic redundancy. Calculate the total in a query or on the report instead, and it is always right by construction. The exception is a value that must be frozen in time, such as the price actually charged on an invoice, which must not change when the product price does.

Before you leave this chapter

  1. SELECT columns, FROM tables, WHERE rows, ORDER BY sequence.
  2. Same criteria row = AND (fewer rows); different rows = OR (more rows).
  3. Text in quotation marks, dates in hashes, numbers bare.
  4. Is Null finds empty fields, which are not the same as zero.
  5. Calculate values in a query rather than storing them — unless they must be frozen in time.
05

Action queries, and why they need care

A select query reads and changes nothing, so it can be run freely. An action query alters the data, and in Access the change is applied immediately with no undo.

There are four kinds. An update query changes values in existing records — raising every price by 5%. An append query copies records from one table into another. A delete query removes records matching its criteria. A make-table query creates a new table from the results of a select.

Query typeEffectReversible?
Selectreads and displaysnothing changed
Updatechanges field valuesno
Appendadds records to another tableonly by deleting them again
Deleteremoves recordsno
Make-tablecreates a new table from resultsdelete the new table

Run it as a select query first

Before running any delete or update query, change it to a select query with the same criteria and look at the rows it returns. Those are exactly the records that will be changed or destroyed. If the list is longer than expected — or contains something surprising — the criteria are wrong, and you have found out while it still costs nothing. Take a backup as well: Access action queries cannot be undone.

Practice questions

6 questions · 20 marks · full working on every one

Try each one on paper first, then open the working. The marks are shown where they are actually awarded, because that is where they are actually lost.

Short questions

3 · 6 marks

Two marks each, in the style of the short-question section of the paper. Answer in two or three lines.

SQ1[2 marks]
What is a query, and does it store data?
Model answer

A query is a saved question that selects, filters, sorts or calculates from one or more tables. It stores no data — only the question — and produces its results afresh from the tables each time it runs, so the answer is always current.

Examiner tip. "Stores the question, not the answer" is the phrase worth writing. It also explains why a query is never out of date.

SQ2[2 marks]
State the difference between AND and OR in query criteria.
Model answer

AND requires a record to satisfy every condition, so the result is smaller than either condition alone. OR requires only one, so the result is larger. In the Access design grid, criteria on the same row are ANDed and criteria on different rows are ORed.

Examiner tip. The "fewer rows / more rows" test is the quickest way to check you have used the right one.

SQ3[2 marks]
What does the criterion Like "A*" select?
Model answer

All records whose value in that field begins with A. The asterisk is a wildcard standing for any sequence of characters, so Like "*A*" would instead find values containing A anywhere.

Examiner tip. Where the asterisk goes is the whole question. Show the contrasting case to prove you know.

Solved numericals

2 · 8 marks

Full working, one step per line, with the marks shown where they are awarded.

N1[4 marks]
Write an SQL query listing the Name and Marks of all students in the STUDENT table who scored above 50, sorted from highest to lowest.
Full working
  1. SELECT Name, Marksonly the requested columns[1]
  2. FROM STUDENT[1]
  3. WHERE Marks > 50no quotation marks — Marks is numeric[1]
  4. ORDER BY Marks DESCDESC is required for highest first[1]

SELECT Name, Marks FROM STUDENT WHERE Marks > 50 ORDER BY Marks DESC

Examiner tip. ORDER BY defaults to ascending, so "highest first" always needs DESC written explicitly.

N2[4 marks]
Explain the difference between a field that is Null and a field containing 0, and state a criterion to find each.
Full working
  1. A Null field is empty — the value is unknown or was never entered[1]
  2. A field containing 0 holds a known value, namely zeroa real mark of nothing, not a missing one[1]
  3. Criterion Is Null finds the empty ones; =0 finds the zeros[1]
  4. The distinction matters because an average ignores nulls but includes zeros, giving two different answers from the same columnthe consequence is the fourth mark[1]

Null is unknown, 0 is a known value. Is Null and =0 respectively; averages treat them differently.

Examiner tip. This is why absent students must not have 0 entered for a test they did not sit — it would drag the class average down as though they had scored nothing.

Long questions

1 · 6 marks

Theory and numerical together, as they appear in the long-question section.

LQ1[6 marks]
A library database has BOOK(ISBN, Title, Author) and LOAN(LoanID, ISBN, MemberID, DateDue).
  1. Write a query listing the Title and DateDue of all books due before 1 March 2026.
  2. Explain why this query needs both tables.
  3. Describe how you would produce a count of loans per member.
Mark scheme
  1. SELECT Title, DateDue[1]
  2. FROM BOOK INNER JOIN LOAN ON BOOK.ISBN = LOAN.ISBNjoined on the shared key[1]
  3. WHERE DateDue < #1/3/2026#dates enclosed in hash symbols[1]
  4. Title is stored only in BOOK and DateDue only in LOAN, so neither table alone can produce both columnsa direct consequence of normalisation[1]
  5. Use a totals query: group by MemberID[1]
  6. and apply Count to LoanID, giving one row per member with their number of loansaccept SELECT MemberID, Count(LoanID) FROM LOAN GROUP BY MemberID[1]

(a) a join on ISBN with a date criterion (b) the two columns live in different tables (c) a totals query grouping by MemberID with Count

Examiner tip. Part (b) is asking you to notice that normalisation is why joins exist. Splitting the data was deliberate; the join is how it is put back together for a report.