Pocket Gems Interview Question

Given a test set of tables as an example, how do you implement the query in SQL