Oracle Interview Question

Please explain the query and how it is works