1Z0-071 practice question 21 of 35

Which two statements will return the names of the three employees with the lowest salaries?

Full set: 326 questions
Answer options
A: SELECT last_name, salary FROM employees WHERE ROWNUM<=3
B: SELECT last_name,salary FROM employees ORDER BY salary FETCH FIRST 3 ROWS ONLY; Correct answer
C: SELECT last_name,salary FROM employees WHERE ROWNUM<=3 ORDER BY (SELECT salary FROM employees);
D: SELECT last_name,salary FROM (SELECT * FROM employees ORDER BY salary)
E: SELECT last_name,salary FROM employees FETCH FIRST 3 ROWS ONLY ORDER BY salary;
Correct Answer:
B. SELECT last_name,salary FROM employees ORDER BY salary FETCH FIRST 3 ROWS ONLY;
Explanation

To find the employees with the lowest salaries, the query must first sort all employees by salary in ascending order and then select the first three rows from the sorted result set. The row-limiting clause (FETCH FIRST ... ROWS ONLY), introduced in Oracle 12c, is designed for this purpose. It is applied after the ORDER BY clause, ensuring the correct top-N result. Option B correctly implements this logic. Although the question asks for two statements, only option B is correct among the choices provided.

Why Incorrect

A: The WHERE ROWNUM<=3 predicate is applied before any sorting, returning three arbitrary rows from the table, not necessarily the ones with the lowest salaries. C: The WHERE ROWNUM<=3 is applied before sorting. The ORDER BY clause is also syntactically incorrect and would fail. D: This query sorts all employees but does not limit the result to three rows; it returns all employees. E: The ORDER BY clause is processed after the FETCH clause, meaning it would fetch three arbitrary rows and then sort only those three.

References

Oracle Database SQL Language Reference, 19c, "ORDER BY Clause" and "rowlimitingclause". The documentation specifies the order of execution, showing that the ORDER BY clause is processed before the FETCH clause, making it suitable for top-N queries.

Oracle Database SQL Language Reference, 19c, "SELECT". The syntax diagram and semantics confirm that FETCH is part of the ORDER BY clause's extensions for row limiting.

Scroll to Top