Chamberlain Group Interview Question

How SQL proceed with it's logic