How do I repeat a SQL query over a set of records?

Say I have a student who hands in exams for different subjects. Students can submit multiple exams for a subject.

You can infer what subjects a student is taking from the exams they have completed.

SELECT subject 
FROM subject
JOIN exam ON subject.idx = exam.subject_idx
JOIN student ON exam._student_idx = student.idx
WHERE student.name = 'Joe Bloggs'

You can also query the latest exam of a given subject & student with the following:

SELECT exam 
FROM exam
JOIN subject ON exam.subject_idx = subject.idx
JOIN student ON exam.student_idx = student.idx
WHERE subject.name = 'math' AND student.name = 'Joe Bloggs'
ORDER BY completed_at DESC
LIMIT 1

Given a certain student 'Joe Bloggs', how would you get the latest tests of all subjects the student takes? The logical way would be to get subjects with the first query, & iterate over the subjects with the second query but I'm not sure how to do that, or if that is the best way.

Untested but it looks to me like you just need SQL Group By:

select 
    max(completed_at) as completed_at,
    subject.name as SubjectName,
    student.name as StudentName
from 
    exam
    inner join subject 
    on exam.subject_idx = subject.idx
    inner join student 
    on exam.student_idx = student.idx
group by 
    subject.name,
    student.name
order by 
    student.name,
    subject.name

These days using a CTE is also a very common solution to such problems:

with cte as (
    select
        exam.completed_at,
        subject.name as SubjectName,
        student.name as StudentName,
        row_number() over (partition by student.name, subject.name order by exam.completed_at desc) as RowNumber
    from 
        exam
        inner join subject 
        on exam.subject_idx = subject.idx
        inner join student 
        on exam.student_idx = student.idx
)
select
    cte.completed_at,
    cte.SubjectName,
    cte.StudentName
from cte 
where cte.RN = 1
order by 
    cte.StudentName,
    cte.SubjectName