OCP-1Z0-051-2015-16题

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. SELECTstudent 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

Explanation:题目要求出具一个报告，查出排名前十位学生在99年全年完成了课程INT_SQL,按照成绩降序排。

重点在ROWNUM和ROWID 的区别。ROWID 能显示出这一行的对象号，文件号，区号，块号等。ROWNUM显示行号。题中要求显示前十个学生，故此处应该使用ROWNUM，B错

题目第二个要求：这些学生中在科目INT_SQL最高的名次以及要求时间在1999内。所以选用子查询，且rownum<10条件放在子查询外。