Infosys Interview Question

They have asked to write Query for a given scenario.