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.

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.

BookIDTitleGenrePagesCopies
1The Clockwork FoxFantasy3203
2Harbour LightsMystery2562
3Tides of InkFantasy4101
4The Midnight MapMystery1984
5Pocket RobotsScience1445
6Under the OrchardHistorical2882
7Signal FiresAdventure3523
8Starfall AcademyFantasy2752
9Little VolcanoScience1206

(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?

Show what happens

It outputs the Title field of every record in the Book table: all nine titles, in the order they are stored, with a heading above.

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

In SELECT BookID, Pages FROM Book; which of these is the name of the table?
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.

Show what happens

Records 1, 3 and 8 have the genre Fantasy. So the output has just those three BookID values, in the order they are in the table.

The output of SELECT BookID FROM Book WHERE Genre = “Fantasy”;
Output rowBookID
1
2
3
4

    Show the completed table
    Output rowBookID
    11
    23
    38
    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:

    SignMeaningExample
    =equal toGenre = "Fantasy"
    <>not equal toGenre <> "Fantasy"
    >greater thanPages > 300
    <less thanPages < 200
    >=greater than or equal toPages >= 275
    <=less than or equal toPages <= 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

    Which query outputs the titles of all books with more than 300 pages?
    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?

    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";
    Check each record against the condition, then write the output of each query
    QueryOutput row 1Output row 2Output row 3Output row 4
    Genre = "Fantasy" AND Pages > 300
    Genre = "Science" OR Genre = "Mystery"

      Show the completed table
      QueryOutput row 1Output row 2Output row 3Output row 4
      Genre = "Fantasy" AND Pages > 30013
      Genre = "Science" OR Genre = "Mystery"2459

      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

      The librarian wants every book that is Science or Historical. Which WHERE line does that?
      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.

      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.

      The output of the query above: BookID, shortest book first
      Output rowBookIDPages (to help you)
      1120
      2144
      3198
      4256
      5275
      6288
      7320
      8352
      9410

        Show the completed table
        Output rowBookIDPages (to help you)
        19120
        25144
        34198
        42256
        58275
        66288
        71320
        87352
        93410

        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

        What does the query above output?
        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?

        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

        Which query outputs the number of copies the library owns of books with fewer than 200 pages?
        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;
        1. FROM: which table? Book, all nine records.
        2. 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.
        3. ORDER BY: in what order? Pages DESC means largest first: 410 (record 3), then 320 (record 1).
        4. 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

        What does the query above output?
        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

        Which words complete the query above?
        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

        What is wrong with the pupil’s query?
        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

        Another pupil writes SELECT SUM(Title) FROM Book; hoping to find out how many books there are. What is wrong, and what should it be?
        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.

        Show a model answer

        SELECT chooses which fields (columns) appear in the output; WHERE chooses which records (rows) are used. For example, SELECT Title with WHERE Pages > 300 outputs only the Title column, and only for the three long books.

        SELECT COUNT(*)
        FROM Book
        WHERE Genre = "Fantasy" OR Pages > 300;

        Apply it

        What does the query above output?
        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: SELECT lists the fields to output, FROM names the table, WHERE keeps only the records that pass a condition.
        • AND needs both conditions true for the same record; OR needs at least one. ORDER BY field ASC or DESC sorts 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.