03 Combining and summarising data: challenges

Create sql_summaries.py , paste the complete runner below and click Run Python File with your Python interpreter selected. Replace only the SQL inside the query string for each exercise. Each run rebuilds the same in-memory database.

Results appear in the terminal; Python displays SQL NULL as None. The simplified schema focuses on retrieval rather than enforcing every range rule from earlier lessons.

import sqlite3

connection = sqlite3.connect(":memory:")
setup_sql = """
PRAGMA foreign_keys = ON;

CREATE TABLE Students (
    student_id INTEGER PRIMARY KEY,
    first_name TEXT NOT NULL,
    year_level INTEGER NOT NULL
);
CREATE TABLE Clubs (
    club_id INTEGER PRIMARY KEY,
    club_name TEXT NOT NULL,
    category TEXT NOT NULL,
    capacity INTEGER NOT NULL,
    fee_cents INTEGER NOT NULL
);
CREATE TABLE Memberships (
    student_id INTEGER NOT NULL,
    club_id INTEGER NOT NULL,
    role TEXT NOT NULL,
    score INTEGER,
    PRIMARY KEY (student_id, club_id),
    FOREIGN KEY (student_id) REFERENCES Students(student_id),
    FOREIGN KEY (club_id) REFERENCES Clubs(club_id)
);

INSERT INTO Students VALUES
    (101, 'Maya', 10), (102, 'Leo', 11), (103, 'Amina', 10),
    (104, 'Noah', 12), (105, 'Maya', 11);
INSERT INTO Clubs VALUES
    (1, 'Robotics', 'Technology', 12, 1500),
    (2, 'Game Design', 'Technology', 16, 0),
    (3, 'Art', 'Creative', 12, 800),
    (4, 'Film', 'Creative', 20, 1000),
    (5, 'Chess', 'Strategy', 16, 0);
INSERT INTO Memberships VALUES
    (101, 1, 'member', 95), (101, 2, 'leader', 85),
    (102, 1, 'member', NULL), (103, 3, 'member', 70),
    (103, 4, 'leader', 90), (104, 2, 'member', 80);
"""
query = """
SELECT club_id, club_name 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()

Join through Memberships, not directly between unrelated IDs.

SELECT s.student_id, s.first_name, c.club_name
FROM Students AS s
INNER JOIN Memberships AS m ON s.student_id = m.student_id
INNER JOIN Clubs AS c ON m.club_id = c.club_id
ORDER BY s.student_id, c.club_id;

There are six result rows. Repeated students reflect multiple memberships.

Preserve Students and test a required right-side key for NULL.

SELECT s.student_id, s.first_name
FROM Students AS s
LEFT JOIN Memberships AS m ON s.student_id = m.student_id
WHERE m.student_id IS NULL
ORDER BY s.student_id;

Student 105 qualifies. A NULL score would not prove an absent membership.

COUNT(*) and COUNT(score) count different things.

SELECT COUNT(*) AS memberships, COUNT(score) AS recorded_scores,
       AVG(score) AS average_score
FROM Memberships;

The result is 6, 5 and 84. Missing is not zero.

Aggregate the table that represents one club per row.

SELECT COUNT(*) AS clubs, SUM(capacity) AS total_capacity,
       MIN(capacity) AS minimum_capacity, MAX(capacity) AS maximum_capacity
FROM Clubs;

The result is 5, 76, 12 and 20.

Filter group counts with HAVING.

SELECT category, COUNT(*) AS clubs, SUM(capacity) AS total_capacity
FROM Clubs
GROUP BY category
HAVING COUNT(*) >= 2
ORDER BY category;

Creative has capacity 32 and Technology 28.

LEFT JOIN then count a required membership key.

SELECT c.club_id, c.club_name, COUNT(m.student_id) AS members
FROM Clubs AS c
LEFT JOIN Memberships AS m ON c.club_id = m.club_id
GROUP BY c.club_id, c.club_name
ORDER BY c.club_id;

Chess must have zero, not one. COUNT(*) would count its placeholder row.

WHERE applies to individual fees; HAVING applies to the capacity total.

SELECT category, SUM(capacity) AS paid_capacity
FROM Clubs
WHERE fee_cents > 0
GROUP BY category
HAVING SUM(capacity) >= 20
ORDER BY category;

Only Creative remains. Free clubs never enter these groups.

Use one aggregate value in the outer comparison.

SELECT club_name, capacity
FROM Clubs
WHERE capacity > (SELECT AVG(capacity) FROM Clubs)
ORDER BY capacity DESC, club_id;

The threshold is 15.2. Film, Game Design and Chess qualify.

An IN subquery can return several IDs.

SELECT student_id, first_name
FROM Students
WHERE student_id IN (SELECT student_id FROM Memberships WHERE role = 'leader')
ORDER BY student_id;

Maya 101 and Amina qualify. The outer Students table provides one row per student.

Both the join and what you count matter.

SELECT c.club_id, c.club_name, COUNT(m.student_id) AS members
FROM Clubs AS c
LEFT JOIN Memberships AS m ON c.club_id = m.club_id
GROUP BY c.club_id, c.club_name
ORDER BY c.club_id;

Changing only COUNT does not restore Chess; changing only JOIN makes COUNT(*) misleading.

Group by identity, not name alone.

SELECT s.student_id, s.first_name, COUNT(m.club_id) AS memberships
FROM Students AS s
INNER JOIN Memberships AS m ON s.student_id = m.student_id
GROUP BY s.student_id, s.first_name
HAVING COUNT(m.club_id) >= 2
ORDER BY s.student_id;

Maya 101 and Amina have two memberships each. Maya 105 is a different person.

Use LEFT JOIN, GROUP BY, HAVING and capacity minus the count.

SELECT c.club_id, c.club_name, c.capacity,
       COUNT(m.student_id) AS members,
       c.capacity - COUNT(m.student_id) AS remaining_places,
       AVG(m.score) AS average_score
FROM Clubs AS c
LEFT JOIN Memberships AS m ON c.club_id = m.club_id
GROUP BY c.club_id, c.club_name, c.capacity
HAVING COUNT(m.student_id) < 2
ORDER BY members, c.club_id;

Chess has zero members, 16 remaining planned places and NULL average. Art has one member, 11 places and average 70; Film has one member, 19 places and average 90. This is a report, not enforcement of a capacity rule.

COUNT(*) counts input rows. Real memberships may have NULL scores. Different clubs can share a capacity, so DISTINCT values lose legitimate contributions. Scalar subqueries must return the intended single value; use IN for a list. ORDER BY controls output ordering.

No students remain without memberships; no club has zero members; the membership count rises to seven. If the new score is NULL, recorded-score count remains five and average recorded score remains 84. Restore the original dataset before comparing with the solutions on this page.