Study Guide
Querying a Database with SQL
How to ask a database a question in SQL: choosing fields with SELECT and FROM, filtering with WHERE, AND and OR, sorting with ORDER BY, and adding up or counting with SUM and COUNT, and how to work out what a query will output.
Lesson 6.2 Databases
For: Cambridge IGCSE Computer Science learners (about ages 14 to 16), or anyone who has met a database table and wants to ask questions of it
Stage: Think
A question for the librarian
A school library (fictional) has nine kinds of book on its catalogue. A pupil walks up to the desk and asks: “Please can I have the fantasy books that have more than 300 pages?”
Think it through
With the catalogue open at the full list of books, how would you answer that question? Describe each step you take, in order.
- Hint 1: Which details of each book do you look at?
- Hint 2: Do you check every book, or only some of them?
Show what happens
A natural method: look at each book in turn; keep it only if its genre is Fantasy and it has more than 300 pages; then report the ones you kept. A database does exactly this, but needs the question written in a strict form it can follow without guessing.
Stage: Discover
The table, and a first question
The catalogue is a table called Book. Every title and number is invented for this lesson.
| BookID | Title | Genre | Pages | Copies |
|---|---|---|---|---|
| 1 | The Clockwork Fox | Fantasy | 320 | 3 |
| 2 | Harbour Lights | Mystery | 256 | 2 |
| 3 | Tides of Ink | Fantasy | 410 | 1 |
| 4 | The Midnight Map | Mystery | 198 | 4 |
| 5 | Pocket Robots | Science | 144 | 5 |
| 6 | Under the Orchard | Historical | 288 | 2 |
| 7 | Signal Fires | Adventure | 352 | 3 |
| 8 | Starfall Academy | Fantasy | 275 | 2 |
| 9 | Little Volcano | Science | 120 | 6 |
(BookID is the primary key. Copies is how many copies of that book the library owns.)
A question asked of a database is called a query, and the language most databases use for it is SQL, short for Structured Query Language. Here is the simplest query:
SELECT Title
FROM Book;Think it through
Read the query line by line. What do you think SELECT Title FROM Book; will output?
- Hint 1: Which word names the column wanted?
- Hint 2: Which word names the table it comes from?
Two keywords start every query. SELECT lists the field or fields to output, separated by commas. FROM names the table to take them from. Each query ends with a semicolon, and writing each keyword on its own line makes a long query easy to read. SQL does not care about capital letters, but Cambridge writes the keywords in capitals, and so does this lesson.
Check your understanding
Show the answer
Answer: B. Book, because it comes after FROM.
SELECT is followed by fields, and FROM by the table.
Stage: Experiment
Only the rows that fit: WHERE
The pupil does not want every book, only the fantasy ones. SQL adds a third line to the query:
SELECT BookID
FROM Book
WHERE Genre = "Fantasy";Think it through
Before you fill in the output below, which records do you expect this query to keep? Check the Genre column of each one.
- Hint 1: The query keeps a record only if the condition is true for it.
| Output row | BookID |
|---|---|
| 1 | |
| 2 | |
| 3 | |
| 4 |
Show the completed table
| Output row | BookID |
|---|---|
| 1 | 1 |
| 2 | 3 |
| 3 | 8 |
| 4 |
A query’s output is a table too. Leave a row empty if the query outputs no such row.
WHERE keeps only the records for which its condition is true. A condition compares a field with a value, using one of these:
| Sign | Meaning | Example |
|---|---|---|
= | equal to | Genre = "Fantasy" |
<> | not equal to | Genre <> "Fantasy" |
> | greater than | Pages > 300 |
< | less than | Pages < 200 |
>= | greater than or equal to | Pages >= 275 |
<= | less than or equal to | Pages <= 275 |
Text goes inside quote marks, as in "Fantasy", and numbers do not. This lesson uses double quotes; a Cambridge exam question shows its own style, so copy the one it uses.
Check your understanding
Show the answer
Answer: C. SELECT Title FROM Book WHERE Pages > 300;
The field after SELECT is what you see; the field in the WHERE condition is what decides which records you see.
Stage: Predict
Two conditions: AND and OR
Back to the pupil’s question: fantasy books that have more than 300 pages. That has two conditions.
Think it through
Look at the Fantasy records (1, 3 and 8). Which of them also have more than 300 pages? Now imagine the pupil had asked for books that are Fantasy or have more than 300 pages. Would record 7 (Adventure, 352 pages) be included?
- Hint 1: For “and”, a record must pass both conditions.
- Hint 2: For “or”, passing one condition is enough.
Show what happens
Records 1 (320 pages) and 3 (410 pages) are Fantasy with more than 300 pages; record 8 has only 275. With “or”, record 7 would be included, because it has more than 300 pages even though it is not Fantasy.
AND keeps a record only if both conditions are true. OR keeps it if at least one is true (the same rule as the OR gate in logic). Each side of AND or OR is a full condition, with its own field name.
SELECT BookID
FROM Book
WHERE Genre = "Fantasy" AND Pages > 300;SELECT BookID
FROM Book
WHERE Genre = "Science" OR Genre = "Mystery";| Query | Output row 1 | Output row 2 | Output row 3 | Output row 4 |
|---|---|---|---|---|
| Genre = "Fantasy" AND Pages > 300 | ||||
| Genre = "Science" OR Genre = "Mystery" |
- Go down the Genre and Pages columns one record at a time.
Show the completed table
| Query | Output row 1 | Output row 2 | Output row 3 | Output row 4 |
|---|---|---|---|---|
| Genre = "Fantasy" AND Pages > 300 | 1 | 3 | ||
| Genre = "Science" OR Genre = "Mystery" | 2 | 4 | 5 | 9 |
In everyday speech, “science and mystery books” means both kinds together. In SQL, AND joins two conditions that must hold for the same record. Each book has only one genre, so Genre = "Science" AND Genre = "Mystery" can never be true, and the query outputs nothing. To get both kinds of book, use OR.
Check your understanding
Show the answer
Answer: D. WHERE Genre = "Science" OR Genre = "Historical";
OR when either will do; AND when both must hold for the same record.
Stage: Discover
Putting results in order: ORDER BY
The pupil would like the books in order, shortest first.
Think it through
Which book would come first, and which last, if the books were listed with the fewest pages first? Look at the Pages column.
- Hint 1: Find the smallest and the largest number of pages.
Show what happens
Record 9, Little Volcano (120 pages), would be first; record 3, Tides of Ink (410 pages), last.
ORDER BY sorts the output by a field. It goes last in the query, followed by ASC (ascending, smallest or A first) or DESC (descending, largest or Z first):
SELECT BookID
FROM Book
ORDER BY Pages ASC;Without ORDER BY, records come out in the order they are stored. The field you sort by does not have to be one of the fields you output.
| Output row | BookID | Pages (to help you) |
|---|---|---|
| 1 | 120 | |
| 2 | 144 | |
| 3 | 198 | |
| 4 | 256 | |
| 5 | 275 | |
| 6 | 288 | |
| 7 | 320 | |
| 8 | 352 | |
| 9 | 410 |
Show the completed table
| Output row | BookID | Pages (to help you) |
|---|---|---|
| 1 | 9 | 120 |
| 2 | 5 | 144 |
| 3 | 4 | 198 |
| 4 | 2 | 256 |
| 5 | 8 | 275 |
| 6 | 6 | 288 |
| 7 | 1 | 320 |
| 8 | 7 | 352 |
| 9 | 3 | 410 |
The third column is only there to help you; the query does not output it.
SELECT BookID
FROM Book
WHERE Genre = "Fantasy"
ORDER BY Pages DESC;Check your understanding
Show the answer
Answer: A. 3, 1, 8: the Fantasy books, with the most pages first.
WHERE chooses the records first; ORDER BY then sorts those that remain.
Stage: Explain
Adding up and counting: SUM and COUNT
So far each query has output fields from records. Sometimes the pupil, or the librarian, wants one number instead.
Think it through
The librarian asks: “How many Fantasy copies do we own in total?” Which field would you add up, and what is the total? And how many different Fantasy titles are there?
- Hint 1: Copies counts the copies of each title.
- Hint 2: Titles are records: how many records are Fantasy?
Show what happens
Add the Copies for records 1, 3 and 8: 3 + 1 + 2 = 6 copies. There are 3 Fantasy titles. “How many copies” and “how many titles” are different questions, answered in different ways.
SUM adds up the values of a numeric field. COUNT counts records. In Cambridge IGCSE exams they are written in these forms:
SELECT SUM(Copies)
FROM Book
WHERE Genre = "Fantasy";SELECT COUNT(*)
FROM Book
WHERE Pages < 200;The first outputs a single value, 6. In the second, COUNT(*) counts the records that pass the WHERE condition: records 4, 5 and 9, so it outputs 3. Without a WHERE line, COUNT(*) counts every record in the table and SUM adds the field for every record.
Do not confuse: SUM and COUNT both give one number, but not the same one
- AddsSUM(field)
- Adds up the values of a number field in the records that pass the condition.SUM(Copies) for Fantasy: 3 + 1 + 2 = 6.
- CountsCOUNT(*)
- Counts the records that pass the condition, whatever their values.COUNT(*) for Fantasy: records 1, 3 and 8, so 3.
SUM needs a field of numbers. Adding up a Title or a Genre makes no sense.
Check your understanding
Show the answer
Answer: C. SELECT SUM(Copies) FROM Book WHERE Pages < 200;
SUM adds a field; COUNT(*) counts records. The WHERE line decides which records they work on.
Stage: Explain
Reading a whole query
Real queries use several parts together. The method is the same: take the lines in the order the database does, and each line cuts down what is left.
SELECT BookID, Pages
FROM Book
WHERE Genre = "Fantasy" AND Pages > 300
ORDER BY Pages DESC;- FROM: which table? Book, all nine records.
- WHERE: which records stay? Keep a record only if the genre is Fantasy and it has more than 300 pages. Record 1 (Fantasy, 320) stays, 3 (Fantasy, 410) stays, 8 (Fantasy, 275) fails the page test, and every other record fails the genre test. Two records are left: 1 and 3.
- ORDER BY: in what order? Pages DESC means largest first: 410 (record 3), then 320 (record 1).
- SELECT: which fields are shown? Only BookID and Pages, so the output is two rows: (3, 410) and (1, 320).
The database reads the table, the condition and the sort before it shows the fields; working in that order stops you outputting a field you have not yet checked.
SELECT BookID, Copies
FROM Book
WHERE Copies > 3
ORDER BY Copies DESC;Apply it
Show the answer
Answer: B. BookID 9 with 6 copies, then 5 with 5, then 4 with 4.
First find the records that pass WHERE, then sort them, then read off only the fields after SELECT.
Stage: Apply
Completing and correcting queries
Exam questions often give a query with a gap, or a query that does not work. Work from the question the query has to answer.
The librarian wants the titles of all Mystery books, with the longest first. This query has a gap:
SELECT Title
FROM Book
WHERE Genre = "Mystery"
........ Pages DESC;Apply it
Show the answer
Answer: A. ORDER BY
The keyword before a field and ASC or DESC is ORDER BY.
A pupil writes this query for “fantasy books with more than 300 pages”:
SELECT Title
FROM Book
WHERE Genre = "Fantasy" Pages > 300;Apply it
Show the answer
Answer: C. Nothing joins its two conditions: it needs AND between them.
Two conditions in one WHERE line need AND or OR between them.
Apply it
Show the answer
Answer: A. SUM adds numbers, and titles are text; to count the books, use SELECT COUNT(*) FROM Book;
Choose SUM to add a number field, COUNT(*) to count records.
Think it through
Write a query that outputs the titles of the Science books, shortest book first.
Show a model answer
SELECT Title FROM Book WHERE Genre = “Science” ORDER BY Pages ASC;
It outputs Little Volcano (120 pages) and then Pocket Robots (144 pages). Check that every line is there, in this order: SELECT, FROM, WHERE, ORDER BY.
Stage: Consolidate
Show what you know
Think it through
A friend says: “SELECT and WHERE both choose things.” Explain what SELECT chooses and what WHERE chooses, with an example from the Book table.
SELECT COUNT(*)
FROM Book
WHERE Genre = "Fantasy" OR Pages > 300;Apply it
Show the answer
Answer: C. 4, because records 1, 3, 7 and 8 pass at least one condition.
List the records that pass the condition first, then count them.
Think it through
Which part of SQL are you least sure about? Write one question you would ask your teacher, and try to answer it using the Book table.
Show a model answer
There is no wrong answer. Good ones to try on the table: “how do I tell AND from OR?” (list which records pass each) and “what is the difference between SUM and COUNT?” (SUM adds a field, COUNT(*) counts records).
- A query asks a database a question. SQL is the language:
SELECTlists the fields to output,FROMnames the table,WHEREkeeps only the records that pass a condition. ANDneeds both conditions true for the same record;ORneeds at least one.ORDER BYfieldASCorDESCsorts the output, and goes last.SUM(field)adds a number field;COUNT(*)counts records. Cambridge IGCSE exam questions use these keywords, and each query ends with a semicolon.- To work out an output, use the table, then WHERE, then ORDER BY, then SELECT.
- Lesson 1, Designing a Database Table, covered how the table, its fields and its primary key are defined.
Sources and scope
- What this lesson is. The second of two lessons on databases for Cambridge IGCSE Computer Science (0478, Topic 9), written for this site. It is a draft awaiting the site owner’s editorial review. Its curriculum links are provisional: the wording of each linked specification point has been checked against the published specification, but no link has yet been verified by the site owner. The links show where the lesson may help with a course; they do not imply that any examination board has reviewed or endorsed it. They were written against the 0478 syllabus for examination in 2026 to 2028.
- Original material. The school library, its nine books and their page and copy counts are fictional. Every query output in the lesson was worked out by hand and checked a second time by running the query on the same table in a scratch database.
- Simplified on purpose. The lesson uses only the SQL the 0478 syllabus lists: SELECT, FROM, WHERE, ORDER BY ascending and descending, SUM, COUNT, AND and OR. Other statements (such as inserting or changing records, joining tables or grouping) are for AS and A Level. Databases differ in small details, such as the style of quote marks around text; an exam question shows the style to use.
An organised collection of data stored so that it can be searched, sorted and updated.
One column of a database table: one kind of detail held about every record, such as a name or a year group.
A logic gate whose output is 1 when at least one of its inputs is 1, including when both are.
A field whose value is different in every record, so that it identifies exactly one record.
A question asked of a database, written so that the database can answer it, usually in SQL.
One row of a database table: all the data held about one item, such as one member or one book.
A language for asking questions of a database and changing it, written as short statements such as SELECT and WHERE.
The part of a database that stores data in rows and columns: each row is a record and each column is a field.
Your progress on this lesson
Saved in this browser only. It is not sent anywhere, does not follow you to another device, and is lost if you clear this site’s data.
This browser is not allowing the site to save progress, so it will be forgotten when you leave the page.