OCP-1Z0-051 補充題庫 第16題 rownum的用法

一、原題

Examine the structure of the STUDENTS table:

You need to create a report of the 10 students who achieved the highest ranking in the course INT

SQL and who completed the course in the year 1999.

Which SQL statement accomplishes this task?

A.

SELECT student_ id, marks, ROWNUM "Rank"

  FROM students

 WHERE ROWNUM <= 10

   AND finish_date BETWEEN '01-JAN-99' AND '31-DEC-99

AND course_id = ' INT_SQL '

ORDER BY marks DESC;

B.

SELECT student_id, marks, ROWID "Rank"

  FROM students

 WHERE ROWID <= 10

   AND finish_date BETWEEN '01-JAN-99' AND '31-DEC-99'

   AND course_id = 'INT_SQL'

 ORDER BY marks;

C.

SELECT student_id, marks, ROWNUM "Rank"

  FROM (SELECT student_id, marks

          FROM students

         WHERE ROWNUM <= 10

           AND finish_date BETWEEN '01-JAN-99' AND '31-DEC-99'

           AND course_id = 'INT_SQL'

         ORDER BY marks DESC);

D.

SELECT student_id, marks, ROWNUM "Rank"

  FROM (SELECT student_id, marks

          FROM students

         WHERE finish_date BETWEEN ’01 - JAN - 99 AND ’31 - DEC - 99’

           AND course_id = ‘INT_SQL’

         ORDER BY marks DESC)

 WHERE ROWNUM <= 10;

E.

SELECT student id, marks, ROWNUM “Rank”

  FROM (SELECT student_id, marks FROM students ORDER BY marks)

 WHERE ROWNUM <= 10

   AND finish date BETWEEN ’01 - JAN - 99’ AND ’31 - DEC - 99’

   AND course_id = ‘INT_SQL’;

答案: D

二、題目翻譯

看下面的STUDENTS表的結構：

現在要建一個報表，科目是INT_SQL最高的、并且是在1999年完成的10個學生。

下面哪個sql語句能完成這個任務?

三、題目解析

rownum是顯示序号，要顯示最高的前10名，需要先按降序排列，再取rownum<=10;