最新OCP考试题库：11g-1Z0-051-第9题

文章来源：更新时间:2016/8/4 15:53:24

在线老师点击咨询：

最新学讯：近期OCP认证正在报名中，因考试人员较多请尽快报名获取最近考试时间，报名费用请联系在线老师，甲骨文官方认证，报名从速！

我要咨询

最新OCP考试题库：11g-1Z0-051-第9题，完整题库请点击这里联系老师咨询了解

9、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 WHER EROWNUM <= 10 AND finish_date BETWEEN '01-JAN-99' AND '31-DEC-99 ANDcourse_id = 'INT_SQL' ORDER BYmarks DESC;

B. SELECT student_id, marks, ROWID "Rank" FROM students WHEREROWID <= 10 ANDfinish_date BETWEEN '01-JAN-99' AND '31-DEC-99' ANDcourse_id = 'INT_SQL' ORDER BYmarks;

C. SELECT student_id, marks, ROWNUM "Rank" FROM(SELECT student_id, marks FROM students WHEREROWNUM <= 10 AND finish_date BETWEEN '01-JAN-99' AND '31-DEC-99' ANDcourse_id = 'INT_SQL' ORDER BYmarks 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’;

Answer: D

首先是ROWNUM和ROWID 的问题。ROWID 能显示出这一行的对象号，文件号，区号，块号等。ROWNUM显示行号。题中要求显示前十个学生，故此处应该使用ROWNUM。所以B错误。

题中首先要求，这些学生中在INT_SQL最高的名次以及要求时间在1999内。故此处需要一个子查询。