Infosys interview question

SQL question regarding joins and nested query