SQL选择内连接，子选择和限制

I've been working with this SQL problem for about 2 days now and suspect I'm very close to resolving the issue but just can't seem to find a solution that completely works. I've been working with this SQL problem for abo