SQLite: filtering, aggregating and joining

Combining conditions (spec 8.3.4)

AND, OR and NOT combine conditions; BETWEEN a AND b is inclusive; IS NULL and IS NOT NULL test for missing values (= NULL never matches). LIKE matches patterns: % stands for any number of characters and _ for exactly one.

SELECT name FROM student WHERE form = '12A' AND age >= 17
SELECT * FROM result WHERE mark BETWEEN 60 AND 80
SELECT name FROM student WHERE name LIKE 'D%'
SELECT name FROM student WHERE age IS NULL

Results on the lesson's tables: [('Amir',), ('Chen',)]; [(1,), (2,), (7,), (8,)]; [('Dana',)]; [('Farah',)] (the second shows only result_id).

Aggregates and grouping

COUNT, SUM, AVG, MAX and MIN summarise many rows. GROUP BY makes one summary row per group; HAVING filters the groups (WHERE filters rows before grouping).

import sqlite3
conn = sqlite3.connect(":memory:")
cur = conn.cursor()
# ... CREATE TABLE and INSERT INTO statements that build the student and result tables shown in the lesson ...
cur.execute("SELECT subject, COUNT(*), AVG(mark) FROM result GROUP BY subject ORDER BY subject")
for row in cur.fetchall():
    print(row)
cur.execute("SELECT subject FROM result GROUP BY subject HAVING COUNT(*) >= 3")
print(cur.fetchall())

Output:

('Chemistry', 2, 63.0)
('Maths', 4, 77.75)
('Physics', 2, 59.5)
[('Maths',)]

Joins

A join combines rows from two tables where the linking fields match: ON student.student_id = result.student_id.

• INNER JOIN (or JOIN): only rows with a match in both tables.
• LEFT JOIN: every row of the left table, with NULL where there is no match.
• RIGHT JOIN and FULL OUTER JOIN: every row of the right table, or of both tables.

import sqlite3
conn = sqlite3.connect(":memory:")
cur = conn.cursor()
# ... CREATE TABLE and INSERT INTO statements that build the student and result tables shown in the lesson ...
cur.execute("""SELECT student.name, result.mark FROM student
               INNER JOIN result ON student.student_id = result.student_id
               WHERE result.subject = 'Maths' ORDER BY result.mark DESC""")
print(cur.fetchall())
cur.execute("""SELECT student.name, result.subject FROM student
               LEFT JOIN result ON student.student_id = result.student_id
               WHERE result.subject IS NULL""")
print(cur.fetchall())

Output:

[('Chen', 91), ('Bea', 88), ('Amir', 72), ('Eli', 60)]
[('Farah', None)]

UNION

UNION stacks the results of two SELECTs with the same number of fields and removes duplicate rows: SELECT name FROM a UNION SELECT name FROM b.

Worked example

Find the name of the student with the highest Maths mark using one query.

1. Join the tables on student_id, keep Maths rows, sort by mark descending and take the first row.
2. SELECT student.name FROM student JOIN result ON student.student_id = result.student_id WHERE subject = 'Maths' ORDER BY mark DESC
3. First row: Chen

Answer: Chen

Check yourself

1. Use the table shown. How many rows does this query return? ```sql SELECT * FROM result WHERE mark BETWEEN 54 AND 72 ```
   Show answer

   4. BETWEEN is inclusive: 54, 60, 65 and 72: 4 rows.

2. Use the table shown. How many rows does this query return? ```sql SELECT name FROM student WHERE name LIKE '%a%' ```
   Show answer

   4. LIKE is not case-sensitive for English letters in SQLite, and % matches anything, so names containing a or A count: 4.

3. Use the table shown. What single value does this query return? ```sql SELECT COUNT(*) FROM result WHERE subject = 'Maths' ```
   Show answer

   4. There are 4 Maths results.

4. Use the table shown. What single value does this query return? ```sql SELECT AVG(mark) FROM result WHERE student_id = 5 ```
   Show answer

   69.50. Eli's marks are 60 and 79, so the average is 69.5.

5. Use the table shown. What single value does this query return? ```sql SELECT MAX(mark) FROM result WHERE subject = 'Physics' ```
   Show answer

   65. The Physics marks are 65 and 54: the maximum is 65.

6. Use the table shown. How many rows does this query return? ```sql SELECT student.name FROM student INNER JOIN result ON student.student_id = result.student_id WHERE result.mark > 70 ```
   Show answer

   4. An inner join keeps matching rows; marks above 70 are Amir 72, Bea 88, Chen 91 and Eli 79: 4 rows.

7. Use the table shown. How many rows does this query return? ```sql SELECT student.name, result.mark FROM student LEFT JOIN result ON student.student_id = result.student_id ```
   Show answer

   9. Every student appears at least once: 8 matched results plus Farah with NULL: 9 rows.

8. Use the table shown. How many rows does this query return? ```sql SELECT student_id FROM result GROUP BY student_id HAVING COUNT(*) = 2 ```
   Show answer

   3. Students 1, 3 and 5 have two results each: 3 groups.

Practise with instant feedback Print this lesson

Sources

• Pearson Edexcel International Advanced Subsidiary/Advanced Level in Computer Science specification (XCP01/YCP01), first teaching 2026
• Pearson IAL Computer Science Unit 2 Programming Language Subset (PLS), Python 3
• Pearson IAL Computer Science IAS sample assessment materials (Unit 2 paper and mark scheme)

Last updated 8 November 2026. Spotted a mistake? Tell us.