OCP-1Z0-051 补充题库第16题 rownum的用法

最新推荐文章于 2021-02-28 03:12:13 发布

原创最新推荐文章于 2021-02-28 03:12:13 发布 · 2.4k 阅读

1 ·

CC 4.0 BY-SA版权

文章标签：

#OCP-1Z0-051 #rownum #order by #11g

OCP-1Z0-051 专栏收录该内容

220 篇文章

订阅专栏

本文介绍如何使用SQL查询在特定条件下成绩最高的前十名学生。通过对比不同的SQL语句，解析正确答案并解释其工作原理。

一、原题

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;