First exams 2027 · SL & HL

A3.3.2 · Database programming

Constructing queries between two tables

A single table can only ever answer questions about itself, the moment a question needs "which artist played which concert," two tables have to be brought together in one query.

Core for SL & HL Command term: construct Builds on A3.3.1

Explainer

A3.3.1 introduced SELECT as one of the DML statements. This statement is about everything SELECT can actually DO once two tables are involved: combining them, filtering what comes back, matching text patterns, and putting the results in a sensible order.

Df

A JOIN combines rows from two tables based on a matching column between them, usually a primary key in one table and the matching foreign key in the other. Without it, a query can only ever see one table at a time.

How a JOIN actually builds its result

Not by magic, by checking one ID at a time against the other table, over and over.

Step 1 of 4 Two tables

The command toolkit

CommandWhat it doesExample
JOIN ... ON ...Combines rows from two tables using a matching column.
SELECT *
FROM Concert
JOIN Artist ON Concert.ArtistID = Artist.ArtistID;
DISTINCTRemoves duplicate rows from the result, keeping only unique ones.
SELECT DISTINCT Genre
FROM Artist;
WHEREFilters individual rows before any grouping happens.
SELECT *
FROM Concert
WHERE VenueName = "Blue Note Club";
= <> > < >= <=Relational operators: compare a field's value against a given value.
SELECT *
FROM Concert
WHERE TicketPrice > 50;
BETWEEN ... AND ...Filters values that fall within a range.
SELECT *
FROM Concert
WHERE TicketPrice BETWEEN 40 AND 80;
LIKE, with % and _Matches text against a pattern instead of an exact value. % stands in for any run of characters, however long, while _ stands in for one character and one only.
SELECT *
FROM Concert
WHERE VenueName LIKE "%Club";
AND, OR, NOTCombines or negates multiple conditions in a WHERE clause.
SELECT *
FROM Concert
WHERE TicketPrice > 50
AND NOT VenueName = "Grand Arena";
ORDER BY ... ASC / DESCSorts the result, ascending or descending, by one or more fields.
SELECT *
FROM Concert
ORDER BY TicketPrice DESC;
GROUP BYCollapses rows sharing a value into groups.
SELECT VenueName, COUNT(*)
FROM Concert
GROUP BY VenueName;
HAVINGFilters GROUPS after they've been formed, unlike WHERE, which filters rows before grouping.
SELECT VenueName, COUNT(*)
FROM Concert
GROUP BY VenueName
HAVING COUNT(*) > 1;

Worked example

ARTIST and CONCERT: one command at a time

Two tables, ARTIST and CONCERT, linked by ArtistID. Click through and watch the same two tables answer a different question at every step.

Step 1 of 7 JOIN

Try it yourself

Run your own queries

The same ARTIST and CONCERT tables, live. The command term for this statement is construct, and constructing means writing one yourself, not recognising one someone else wrote. Everything A3.3 teaches works here, not only this statement's own commands, so INSERT, UPDATE and DELETE run too and really do change the tables until you reset them.

ArtistArtistID, ArtistName, Genre
4 rows
ConcertConcertID, ArtistID, VenueName, TicketPrice, ConcertDate
5 rows
Ctrl + Enter also runs
?

Things worth trying. Write ArtistID on its own in a joined query and see why it has to be qualified. Swap HAVING for WHERE in the last example. Change LIKE "Dana%" to LIKE "%s" and work out why the results change. Each one is a real exam error, and triggering it yourself makes it stick.

Check your understanding

A query needs to find only the venues that have hosted MORE THAN ONE concert. Which clause makes that filter possible?

Common mistake

Markers reported this pattern for A3.3.2

RecurringWHERE and HAVING both look like they "filter the query," and candidates often use them interchangeably. WHERE filters ROWS, before any grouping exists. HAVING filters GROUPS, after GROUP BY has already run, and it's the only one of the two that can reference an aggregate like COUNT(*) at all.

✕

What a weak answer looks like: "WHERE COUNT(*) > 1" to find venues with more than one concert. This is invalid, COUNT(*) doesn't exist yet at the point WHERE runs, grouping hasn't happened.

✓

What it should say: "GROUP BY VenueName HAVING COUNT(*) > 1", group the rows by venue first, THEN filter which groups survive based on how many rows ended up in each one.

Linking question

B2

How do programming languages interact with databases to store, retrieve and manipulate data?

A JOIN query like the ones on this page rarely runs on its own, a real application embeds it inside Python, Java or another high-level language, which sends the SQL string to the database, waits for the result set, then loops over it like any other collection. The SQL itself stays declarative (WHAT to retrieve), the surrounding program stays imperative (HOW to use what comes back).

Revision questions

Past-paper style. Write an answer, then reveal the marking points and mark yourself.

Question 1 · 2 marks

[2]Construct a query that joins ARTIST and CONCERT to list every artist's name alongside the venue they played.

Question 2 · 3 marks

[3]Construct a query that finds every artist whose name starts with "The".

Question 3 · 5 marks

[5]Construct a query that lists every concert with a TicketPrice between 40 and 80, ordered by TicketPrice in descending order. Explain why BETWEEN is inclusive of both boundary values.

Flashcards

Active recall for this statement's key terms.