Chamberlain Group Interview Question

How SQL process the logic?