Theory Notes/🗄️ Topic 4: Data & Information Management, Solution Development/12.4.11

12.4.11Grade 12

Querying a Multi-Table Database

The final SQL subtopic — joining related tables together, subqueries, and (for the PAT) connecting to a database from code.

Subqueries (nested queries) — a SELECT statement placed inside another SQL statement, used when a query depends on the result of another query.

Example

SELECT Name FROM Learners WHERE Grade = (SELECT MAX(Grade) FROM Learners);

Joining tables using primary and foreign keys:

Join type	Returns
INNER JOIN (or WHERE to combine tables)	Only records that have a matching value in both tables
LEFT JOIN	All records from the left table, plus matching records from the right table (unmatched right-side fields show as NULL)
RIGHT JOIN	All records from the right table, plus matching records from the left table (unmatched left-side fields show as NULL)
LEFT/RIGHT JOIN or NOT IN (to find unrelated records)	Records that exist in one table but have NO matching related record in the other table

Example

-- Records in Learners with NO matching record in the Awards table SELECT Name FROM Learners WHERE LearnerID NOT IN (SELECT LearnerID FROM Awards);

Altering data across a join context: INSERT with a SELECT — inserting the results of a SELECT statement (which can itself be any combination of SQL constructs) directly into a table.

Example

INSERT INTO TopLearners (LearnerID, Name) SELECT LearnerID, Name FROM Learners WHERE Average >= 80;

Accessing a multi-table database through programming language constructs (ONLY examinable in the PAT, not the written exams):

Setting up a database connection using a database connection class, providing a file path in code.
Querying and editing the multi-table database using appropriate SQL constructs from within your program.
Accessing and modifying specific fields and records programmatically.
Representing retrieved data in primary memory using appropriate data structures (e.g. storing query results in an array of objects for further processing in the program).

💡 Exam Tip

For join questions, always identify the primary key / foreign key pair connecting the two tables first — every join condition is built around matching those two specific fields.