CIE IGCSE | 9.4 SQL

Lesson objective

Read, understand and complete SQL queries on a single table, and identify their output using filtering, sorting, SUM and COUNT.

Learn

9.3 | PRIMARY KEYS

01 | A TABLE IS USEFUL WHEN YOU CAN ASK QUESTIONS

A library stores hundreds of book records. A student wants fiction, while the librarian needs the number of available copies. Reading every row manually would be slow. A database query describes the answer required so the DBMS can retrieve or calculate it.

SQL means Structured Query Language. It is a language used to give instructions to a relational database. In this lesson, every query reads one table. A query result is the data returned by that request, not a new version of the stored table.

02 | MEET THE BOOKS TABLE

Our table is named Books. Each record describes one catalogue title, with Copies storing the number of available copies. BookID is its primary key. Category is text, and Copies is an INTEGER.

Keep this table beside the examples. SQL uses its exact table and field names. A displayed result may show fewer columns or records than this original table.

Books: the source for every worked query
BookIDTitleCategoryCopies
B001The Coding ClubFiction3
B002Ocean ScienceNon-fiction2
B003A Journey to MarsFiction4
B004Robots at WorkNon-fiction1
B005The Hidden IslandFiction0

03 | SELECT FIELDS FROM A TABLE

SELECT names the fields to return. FROM names the table containing them. Read this as “return Title and Copies from Books”. The comma separates field names; it is not placed before FROM.

The query returns all five records because it has no filtering condition. Only Title and Copies are displayed. BookID and Category remain stored even though they are absent from this result.

SELECT Title, Copies
FROM Books;
Selected columns, all five records
TitleCopies
The Coding Club3
Ocean Science2
A Journey to Mars4
Robots at Work1
The Hidden Island0

04 | THE STAR AND THE STATEMENT

SELECT * requests all fields. The star does not mean “multiply” in this position. Use named fields when the question asks for particular columns.

SQL keywords are conventionally uppercase to make a query easy to read. Line breaks help organise the clauses. A semicolon terminates the statement. Names and text matching can depend on the DBMS, so copy the provided identifiers and values carefully.

Without ORDER BY, SQL does not guarantee the order of returned rows. The unsorted worked tables here follow source order for ease of checking.

SELECT *
FROM Books;

05 | WHERE CHOOSES RECORDS

WHERE gives a condition that each record must satisfy. To find fiction, compare its Category value with the text value Fiction. Text literals are enclosed in single quotation marks. Field names are not quoted as text literals.

B001, B003 and B005 match. B002 and B004 do not. The result still shows only the fields listed after SELECT.

SELECT Title, Copies
FROM Books
WHERE Category = 'Fiction';
Three matching fiction records
TitleCopies
The Coding Club3
A Journey to Mars4
The Hidden Island0

06 | NUMERIC CONDITIONS AND BOUNDARIES

For numeric comparisons, write the number without quotation marks. Copies > 2 means strictly greater than two, so two does not qualify. Copies >= 2 includes two. The boundary changes the result.

Use = for equal to, <> for not equal to, < and > for strict comparisons, and <= or >= when equality is included. SQL uses = for a comparison here, rather than the pseudocode assignment arrow.

SELECT Title, Copies
FROM Books
WHERE Copies > 2;
Two records with more than two copies
TitleCopies
The Coding Club3
A Journey to Mars4

07 | AND REQUIRES BOTH CONDITIONS

Suppose the student needs fiction with at least one available copy. Category alone includes The Hidden Island, which has zero. Copies alone includes non-fiction. AND keeps a record only when both conditions are true for that same record.

The Coding Club and A Journey to Mars qualify. The Hidden Island fails the availability condition, even though it is fiction.

SELECT Title
FROM Books
WHERE Category = 'Fiction' AND Copies > 0;
Both requirements must hold
Title
The Coding Club
A Journey to Mars

08 | OR ACCEPTS EITHER CONDITION

OR keeps a record when at least one condition is true, including when both are true. It does not mean “exactly one”. In this example every fiction title qualifies, and any non-fiction title with more than one copy also qualifies.

Ocean Science is included because Copies is 2. Robots at Work fails both conditions. A row matching both conditions appears once, not twice.

SELECT Title
FROM Books
WHERE Category = 'Fiction' OR Copies > 1;
Either condition is enough
Title
The Coding Club
Ocean Science
A Journey to Mars
The Hidden Island

09 | ORDER BY ARRANGES THE RESULT

ORDER BY names the field used to sort the returned rows. Ascending order puts smaller numbers first, text in the database’s ascending text order, and earlier dates first. Descending reverses that direction.

The standard SQL spellings are ASC and DESC. These implement the syllabus’s ascending and descending ordering. Put ORDER BY after WHERE when both clauses are needed.

This query filters fiction first, then sorts by Copies from largest to smallest. Sorting does not alter stored values. If values tie, their relative order is not guaranteed unless another sort field is specified.

SELECT Title, Copies
FROM Books
WHERE Category = 'Fiction'
ORDER BY Copies DESC;
Fiction sorted by descending available copies
TitleCopies
A Journey to Mars4
The Coding Club3
The Hidden Island0

10 | SUM ADDS VALUES, COUNT COUNTS RECORDS

An aggregate combines values from several rows into one result. SUM(Copies) adds the numeric Copies values: 3 + 2 + 4 + 1 + 0 = 10. It does not count the number of book titles.

COUNT(*) counts records. Five records means five catalogue titles, not ten copies. COUNT(Copies) counts non-NULL Copies values, rather than adding them. All five are present here, including zero, so it also returns 5.

A zero value is present data, not NULL. With missing values, COUNT(field) can differ from COUNT(*). SUM ignores NULL values; keep that distinction separate from the elementary arithmetic examples.

SELECT SUM(Copies)
FROM Books;

SELECT COUNT(*)
FROM Books;
Copies versus catalogue records
Query expressionSingle result
SUM(Copies)10
COUNT(*)5
COUNT(Copies)5

11 | FILTER BEFORE CALCULATING

To total fiction copies, WHERE restricts the records used by SUM. The relevant values are 3, 4 and 0, so the answer is 7. Counting those same records gives 3, including the title with no available copies.

Without grouping, these aggregate queries each return a single summary value rather than one output row per matching book. The exact displayed heading depends on the DBMS.

SELECT SUM(Copies)
FROM Books
WHERE Category = 'Fiction';

SELECT COUNT(*)
FROM Books
WHERE Category = 'Fiction';
Same matching rows, different calculations
Expression on fiction recordsResult
SUM(Copies)7
COUNT(*)3

12 | BUILD A QUERY AND SEE ITS OUTPUT

Choose a result, a filter and a sorting direction. Read the generated SQL before checking the output. The fixed Books table above supplies the data. For summary queries, sorting is omitted because the result is one value.

Before running, predict which records survive the filter. Then predict the columns, order or summary value. This is a teaching simulation of these fixed query choices.

13 | READ A QUERY IN A RELIABLE ORDER

Find the source table in FROM. Check each row against WHERE, including the exact comparison boundary and whether conditions use AND or OR. Then identify the requested fields or calculation in SELECT. Finally apply ORDER BY if supplied.

This is a method for predicting the answer, not a claim about the DBMS’s physical execution steps. Write the requested field headings and values, rather than copying complete source records when only Title is requested.

When completing a query, translate the requirement carefully: “at least” uses >=; “more than” uses >; “both” usually needs AND; “either” uses OR. Confirm whether the task asks for records, their count, or a total.

14 | PRACTISE COMPLETING THE REQUEST

The librarian asks for non-fiction titles with at least two copies, ordered from fewest to most copies. Begin with the required output fields, use the exact table name, combine the restrictions, and choose ascending order.

SELECT Title, Copies
FROM Books
WHERE Category = 'Non-fiction' AND Copies >= 2
ORDER BY Copies ASC;
Only Ocean Science qualifies
TitleCopies
Ocean Science2

15 | KEEP THE COMMON MISTAKES SEPARATE

SELECT chooses columns; WHERE chooses records. FROM names the table. ORDER BY arranges the result, rather than deciding which rows qualify. SUM adds values; COUNT counts records or non-NULL field values.

A query returning no matching rows is not necessarily incorrect: its conditions may simply match none of the stored records. COUNT(*) then returns 0. SUM with no matching values generally returns NULL, not an automatic zero.

These SELECT queries retrieve information and do not edit the original table. Check names, quotation marks, condition boundaries and aggregate choice before checking the final output.

MATCH THE SQL KEYWORD TO ITS ROLE

Terminology

Terminology

SQL

Structured Query Language, used to give instructions to a relational database.

Query

A request to retrieve or process database data.

Result

The data returned by a query.

SELECT

Specifies the fields or expressions to return.

FROM

Specifies the source table.

WHERE

Restricts records using a condition.

Condition

A test that is true or false for a record.

Text literal

A text value written in single quotation marks in these SQL examples.

SELECT *

Requests all fields.

AND

True only when both conditions are true.

OR

True when at least one condition is true, including both.

ORDER BY

Sorts returned rows by the named field or fields.

ASC

Ascending order, such as smaller numeric values first.

DESC

Descending order, such as larger numeric values first.

Aggregate

An operation combining data from multiple rows into a summary.

SUM(field)

Adds non-NULL numeric values in the field across qualifying records.

COUNT(*)

Counts qualifying records.

COUNT(field)

Counts qualifying records having a non-NULL value in that field.

Questions

Questions

CHECK YOUR UNDERSTANDING

Select all correct choices. Each exact set earns one point.

1. Which describe SELECT Title FROM Books?
2. Which match Copies > 2?
3. Which describe AND?
4. Which describe OR?
5. Which fit descending numeric order?
6. Which results apply to all five Books records?
7. Which apply to fiction records?
8. Which are correct?

EXPLAIN AND COMPARE

Write your answers before revealing each sample.

1. Define SQL and describe the purpose of a query.

2. Explain the different roles of SELECT, FROM and WHERE.

3. Write a query returning Title for books with no available copies.

4. Compare AND with OR using the Books table.

5. Write a query returning Title and Copies in ascending Copies order.

6. Explain why COUNT(*) and SUM(Copies) produce different answers.

7. Write a query calculating the total copies of fiction.

8. Describe how to identify the output of a query with WHERE and ORDER BY.

BUILD IT | A LIBRARY REQUEST

Using Books, write three queries: return non-fiction titles with at least one copy in descending Copies order; count fiction titles with available copies; total copies of titles with more than two copies. Predict every result before checking.

Reveal sample queries and results
SELECT Title, Copies
FROM Books
WHERE Category = 'Non-fiction' AND Copies >= 1
ORDER BY Copies DESC;

SELECT COUNT(*)
FROM Books
WHERE Category = 'Fiction' AND Copies > 0;

SELECT SUM(Copies)
FROM Books
WHERE Copies > 2;

First: Ocean Science, 2; Robots at Work, 1. Second: 2 qualifying titles. Third: 7 copies, from values 3 and 4.

Explain why replacing AND with OR in the second query would change the count to 5: all fiction titles qualify, and both non-fiction titles have available copies.

Flashcards

Flashcards

Click to flip. Select the ideas you need to revisit.

0 cards selected for revision.

    Selections are kept while this page is open.

    Workbook

    Workbook

    COMING SOON

    The workbook for 9.4 SQL is coming soon. Complete the matching task and written questions in your exercise book.