02.1 SELECT, FROM and WHERE

01 | FROM STORAGE TO QUESTIONS

We can now design a database, but storing facts is only useful if we can retrieve what we need. A club organiser might ask, “Which technology clubs can take at least 15 students?”

A query is a request to the database. SELECT retrieves a result table; it does not remove unselected columns or delete rows that fail a condition.

Use this section’s expanded Clubs table. It is a separate retrieval dataset , not a migration of the Section 01 database. All examples use SQLite. The queries below read data without changing stored records.

import sqlite3

connection = sqlite3.connect(":memory:")
setup_sql = """
CREATE TABLE Clubs (
    club_id INTEGER PRIMARY KEY,
    club_name TEXT NOT NULL,
    category TEXT NOT NULL,
    capacity INTEGER NOT NULL,
    room TEXT,
    fee_cents INTEGER NOT NULL,
    session_minutes INTEGER NOT NULL
);

INSERT INTO Clubs
    (club_id, club_name, category, capacity, room, fee_cents, session_minutes)
VALUES
    (1, 'Robotics', 'Technology', 12, 'AIR Lab', 1500, 90),
    (2, 'Game Design', 'Technology', 16, 'Room 12', 0, 60),
    (3, 'Art', 'Creative', 12, 'Studio', 800, 60),
    (4, 'Film', 'Creative', 20, NULL, 1000, 90),
    (5, 'Chess', 'Strategy', 16, 'Room 12', 0, 45),
    (6, 'Coding', 'Technology', 20, 'AIR Lab', 0, 60),
    (7, 'Drama', 'Creative', 24, NULL, 1200, 90),
    (8, 'Robotics Junior', 'Technology', 10, '', 500, 45);
"""

query = """
SELECT club_id, club_name, category, capacity, room, fee_cents, session_minutes
FROM Clubs
ORDER BY club_id;
"""

try:
    connection.executescript(setup_sql)
    cursor = connection.execute(query)
    print(" | ".join(column[0] for column in cursor.description))
    for row in cursor.fetchall():
        print(row)
except sqlite3.Error as error:
    print("Database error:", error)
finally:
    connection.close()

02 | THE DATA YOU WILL QUERY

Money is stored as integer cents to avoid using binary floating point as the stored monetary amount. fee_cents = 0 means free, not unknown. Film and Drama have NULL rooms; Robotics Junior has an empty text room, deliberately included so you can compare the two situations.

03 | SELECT CHOOSES COLUMNS

SELECT club_name, capacity
FROM Clubs
ORDER BY club_id;

SELECT lists the columns to display, separated by commas. FROM names the source table. Reversing the selected column order changes the result layout, not the stored table.

The ORDER BY line makes the displayed row order predictable for this example. Page 02.2 explains it. Without ORDER BY, do not rely on a particular result order.

04 | THE STAR IS A SHORTCUT

SELECT *
FROM Clubs;

* requests all source columns. It is convenient when exploring a small table, but an explicit list makes your intended output clearer and avoids unexpected extra columns after a schema change.

The semicolon ends the SQL statement. Put the query inside the runner’s query string, not into the terminal as a Python command.

05 | WHERE CHOOSES ROWS

SELECT club_name, capacity
FROM Clubs
WHERE category = 'Technology'
ORDER BY club_id;

The WHERE expression is evaluated for each source row. Rows for which it is true are included. A row can satisfy WHERE even when the filtering column is not displayed.

Here category is used to choose records, while only the name and capacity appear. Text literals use single quotes; category is a column name, not a quoted word.

06 | COMPARE VALUES

SELECT club_name, capacity
FROM Clubs
WHERE capacity >= 20
ORDER BY club_id;

The boundary matters: >= 20 includes 20; > 20 does not. SQLite also accepts != for not equal, but <> is widely used SQL notation.

07 | AND REQUIRES BOTH CONDITIONS

SELECT club_name, capacity
FROM Clubs
WHERE category = 'Technology' AND capacity >= 15
ORDER BY club_id;

Game Design and Coding satisfy both requirements. Robotics is technology-related but has capacity 12, so it fails the second condition.

Read AND as an intersection: a row must be in both qualifying groups. Write each comparison in full.

08 | OR ALLOWS EITHER CONDITION

SELECT club_name, category
FROM Clubs
WHERE category = 'Creative' OR category = 'Strategy'
ORDER BY club_id;

OR includes a row when either condition is true, including when both are true. Do not write category = 'Creative' OR 'Strategy' : the second part must be a complete comparison.

09 | GROUP LOGIC WITH PARENTHESES

SELECT club_name, category, capacity
FROM Clubs
WHERE (category = 'Creative' OR category = 'Strategy')
  AND capacity >= 16
ORDER BY club_id;

We first want either named category, then require capacity at least 16. AND binds more tightly than OR, so parentheses make the intended grouping explicit.

Without them, category = 'Creative' OR category = 'Strategy' AND capacity >= 16 admits every Creative club, including Art with capacity 12.

10 | DEBUG YOUR QUESTION

If no rows appear, it may be a correct empty result. Check table and column names, quotes, comparisons and boundaries before assuming the data are missing.

SQL = compares values here; it does not assign a new capacity. A SELECT remains a read operation even when the result is much smaller than the source.

SELECT club_name, session_minutes
FROM Clubs
WHERE fee_cents = 0 AND session_minutes >= 60
ORDER BY club_id;

11 | INDEPENDENT PRACTICE

SELECT club_name, fee_cents
FROM Clubs
WHERE category = 'Creative' AND fee_cents <= 1000
ORDER BY club_id;

Before moving on, explain the difference between choosing columns and choosing rows. Next we will control the order of the result and how many rows are returned.

KEEP LEARNING

Previous page 02.2 Sorting and limiting results

Reference: SQLite SELECT · SQLite expressions and filters