Chamberlain Group interview question

How SQL process the logic?