SQLite in Python: retrieving data with SELECT

The tables used in this lesson

student and result are linked by student_id: a foreign key in result.

Table student

Results

Each row of result belongs to one student.

Table result

SELECT, FROM, WHERE and ORDER BY (spec 8.3.3)

SELECT lists the fields (* means all), FROM names the table, WHERE filters records, ORDER BY sorts (ASC is the default, DESC for descending). SELECT DISTINCT removes duplicate rows. Text values go in single quotes.

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 name, age FROM student WHERE form = '12A' ORDER BY name DESC")
rows = cur.fetchall()
print(rows)
for row in rows:
    print(row[0])
cur.execute("SELECT DISTINCT subject FROM result ORDER BY subject")
print(cur.fetchall())

Output:

[('Farah', None), ('Chen', 17), ('Amir', 17)]
Farah
Chen
Amir
[('Chemistry',), ('Maths',), ('Physics',)]

fetchone() and fetchall()

After execute(), fetchall() returns a list of tuples, one per row; fetchone() returns the next row as a tuple, or None if there are no more. An aggregate such as COUNT returns one row with one value, so use cur.fetchone()[0].

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 COUNT(*) FROM student")
print(cur.fetchone()[0])
cur.execute("SELECT name FROM student WHERE age > 17")
print(cur.fetchone())
print(cur.fetchone())

Output:

6
('Dana',)
None

Worked example

Write the query to list the names of students aged 17, in alphabetical order, and say how many rows it returns.

1. SELECT name FROM student WHERE age = 17 ORDER BY name
2. Rows: [('Amir',), ('Chen',), ('Eli',)]

Answer: 3 rows: Amir, Chen, Eli

Check yourself

1. Use the table shown. How many rows does this query return? ```sql SELECT * FROM result WHERE mark >= 70 ```
   Show answer

   4. Marks of at least 70: 72, 88, 91, 79: 4 rows.

2. Use the table shown. How many rows does this query return? ```sql SELECT DISTINCT form FROM student ```
   Show answer

   3. The forms are 12A, 12B and 12C: 3 distinct values.

3. Use the table shown. What is the name in the FIRST row returned by this query? SELECT name FROM student WHERE age IS NOT NULL ORDER BY age DESC
   Show answer

   Dana. Sorting by age descending puts the oldest student (18) first: Dana.

4. This code builds the table shown, then runs the lines after the comment. What is displayed? ```python 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 name, form FROM student WHERE student_id = 4") row = cur.fetchone() print(row[1]) ```
   Show answer

   12C. fetchone() returns the tuple ('Dana', '12C'), and index 1 is the form: 12C.

5. This code builds the table shown, then runs the lines after the comment. What is displayed? ```python 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 name FROM student WHERE form = '13Z'") print(cur.fetchone()) ```
   Show answer

   None. No record matches, so fetchone() returns None.

6. This code builds the table shown, then runs the lines after the comment. What is displayed? ```python 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, mark FROM result WHERE student_id = 3") print(cur.fetchall()) ```
   • [['Physics', 54], ['Maths', 91]]
   • ('Physics', 54, 'Maths', 91)
   • [('Physics', 54), ('Maths', 91)]
   • Physics 54 Maths 91
   Show answer

   [('Physics', 54), ('Maths', 91)]. fetchall() returns a list of tuples, one per row: [('Physics', 54), ('Maths', 91)].

7. Which WHERE clause correctly selects the students in form 12B?
   • WHERE form = '12B'
   • WHERE form == 12B
   • WHERE form = 12B
   • WHERE '12B'
   Show answer

   WHERE form = '12B'. SQL uses a single = for comparison and text values go in quotes.

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.