4.10.4 - SQL retrieval across related tables (Python)
Use SQL to turn related tables into precise answers: select fields, combine conditions, join matching records and summarise groups. In this Python route, a small SQLite exploration lets you inspect the results; the Paper 2 SQL answers themselves are written without Run. Defining tables and changing stored data follow in the next lesson.
Selecting, filtering and ordering
A retrieval query describes which data you want, rather than a loop that visits every record yourself.
SQL
Structured Query Language: a language used to define, retrieve and change data in relational databases.
A table has named fields (columns) and records (rows). A SELECT statement produces a result table without changing the stored records. Here is part of a workshop database. MemberID uniquely identifies a member; a name need not be unique.
| MemberID | FullName | YearGroup |
|---|---|---|
| 1 | Ava Khan | 11 |
| 2 | Ben Cole | 12 |
| 3 | Cara Shah | 12 |
| 4 | Dina Reed | 13 |
SELECT FullName, YearGroup
FROM Member
WHERE YearGroup >= 12
ORDER BY YearGroup DESC, FullName ASC;
SELECT chooses the output fields, in their output order. FROM identifies the source table. WHERE keeps records whose condition is true. ORDER BY sorts the resulting records: DESC means descending, while ASC means ascending and is the default. This query returns Dina first, then Ben and Cara. The second sort field breaks ties in the first. Without ORDER BY, do not promise any particular result order. SELECT * returns every field, but naming the needed fields makes the intended output clear.
SQL compares values with =, <>, <, <=, > and >=. Numbers are unquoted; a text literal is quoted, for example FullName = 'Ava Khan'. Single quotes distinguish the text value from a field name. SQL keywords are usually written in capitals for readability; capitals are not a requirement for credit.
Combine conditions with AND when both must be true and OR when either is enough. NOT reverses a condition. Parentheses make a mixed condition unambiguous:
SELECT FullName
FROM Member
WHERE (YearGroup = 11 OR YearGroup = 12)
AND FullName <> 'Ben Cole';
The brackets make the name restriction apply to both year groups. AND otherwise binds more tightly than OR, so omitting brackets can change which records qualify. For inclusive numerical ranges, YearGroup BETWEEN 11 AND 12 includes both endpoints. LIKE 'Ca%' matches text starting with Ca; % represents any sequence of characters and _ one character. Case matching can depend on the database, so do not assume a particular case rule unless the task states it.
A NULL represents an absent or unknown value, not zero or an empty string. Test it with IS NULL or IS NOT NULL, rather than = NULL. A normal comparison with NULL does not become true and therefore will not pass a WHERE filter. SELECT DISTINCT YearGroup FROM Member returns each different year group once; ordinary SELECT retains duplicate output values.
Joining related records
A join reconstructs related information by matching a key in one table with the corresponding foreign key in another.
The workshop database also has these tables. Booking.MemberID refers to Member.MemberID; Booking.WorkshopID refers to Workshop.WorkshopID. The Attended field is 1 for attended and 0 for not attended.
| WorkshopID | Title | Fee |
|---|---|---|
| 10 | Robotics | 8.00 |
| 20 | Drawing | 5.00 |
| 30 | Coding | 8.00 |
| BookingID | MemberID | WorkshopID | Attended |
|---|---|---|---|
| 101 | 1 | 10 | 1 |
| 102 | 2 | 10 | 0 |
| 103 | 2 | 20 | 1 |
| 104 | 3 | 10 | 1 |
| 105 | 4 | 20 | 1 |
| 106 | 3 | 30 | 0 |
SELECT m.FullName, w.Title
FROM Member AS m
INNER JOIN Booking AS b ON m.MemberID = b.MemberID
INNER JOIN Workshop AS w ON b.WorkshopID = w.WorkshopID
WHERE b.Attended = 1
ORDER BY m.FullName, w.Title;
m, b and w are aliases, short names for the tables within this statement. m.MemberID is a qualified field name. Qualification is essential when two source tables contain a field with the same name and the reference would otherwise be ambiguous. AS may be omitted for these table aliases.
An INNER JOIN returns only combinations that satisfy its matching condition. Booking 101 matches member 1 and workshop 10, so it produces Ava Khan / Robotics. Booking 102 matches correctly but is removed by the attendance filter. The full result contains Ava / Robotics, Ben / Drawing, Cara / Robotics and Dina / Drawing. A member with no booking would not appear. A member with two matching bookings may appear twice: that is the expected one-to-many relationship, not automatically an error.
A join condition establishes the relationship; a filter states the additional selection requirement. Missing a relationship condition can pair unrelated records. Joining all four members to all six bookings without matching IDs produces 24 combinations before filtering. DISTINCT can remove repeated output values, but cannot establish a missing relationship. Join IDs, rather than names or coincidentally equal fees.
The following alternative uses a comma-separated table list and puts all relationship conditions in WHERE. It is equivalent for this inner join when every condition is present:
SELECT m.FullName, w.Title
FROM Member m, Booking b, Workshop w
WHERE m.MemberID = b.MemberID
AND b.WorkshopID = w.WorkshopID
AND b.Attended = 1
ORDER BY m.FullName, w.Title;
Python can send SQL to SQLite, then print each returned row. The SQL inside the string is still SQL; the surrounding Python supplies the database and displays its result. This exploration has no input or external file, and creates a new in-memory database each run.
Inspect a join before changing its condition
{"code":"import sqlite3\n\ndb = sqlite3.connect(":memory:")\ndb.executescript("""\nCREATE TABLE Member(MemberID INTEGER PRIMARY KEY, FullName TEXT, YearGroup INTEGER);\nCREATE TABLE Workshop(WorkshopID INTEGER PRIMARY KEY, Title TEXT, Fee REAL);\nCREATE TABLE Booking(BookingID INTEGER PRIMARY KEY, MemberID INTEGER, WorkshopID INTEGER, Attended INTEGER);\nINSERT INTO Member VALUES (1, 'Ava Khan', 11), (2, 'Ben Cole', 12), (3, 'Cara Shah', 12), (4, 'Dina Reed', 13);\nINSERT INTO Workshop VALUES (10, 'Robotics', 8.0), (20, 'Drawing', 5.0), (30, 'Coding', 8.0);\nINSERT INTO Booking VALUES (101, 1, 10, 1), (102, 2, 10, 0), (103, 2, 20, 1), (104, 3, 10, 1), (105, 4, 20, 1), (106, 3, 30, 0);\n""")\nquery = """\nSELECT m.FullName, w.Title\nFROM Member m\nINNER JOIN Booking b ON m.MemberID = b.MemberID\nINNER JOIN Workshop w ON b.WorkshopID = w.WorkshopID\nWHERE m.YearGroup = 12 AND w.Fee >= 8\nORDER BY m.FullName, w.Title;\n"""\nfor row in db.execute(query):\n print(row[0] + ' / ' + row[1])\ndb.close()","instructions":"Predict the printed rows and their order from the three tables, then run. Modify only the WHERE line by appending AND b.Attended = 1. Predict which rows disappear, then run again. Explain why Cara can have two result rows in the original query.","files":[],"original_filename":"main.py","runtime_profile":"python-console-v1","file_mode":"fresh"}
Continue this lesson.
You’re previewing NovaLearn without an account. Create a free account to read the rest.
Create free account