QUESTION NO: 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 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内。故此处需要一个子查询。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)