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)
WHEREROWNUM <= 10
ANDfinish date BETWEEN �01-JAN-99� AND �31-DEC-99�
ANDcourse_id = �INT_SQL�;
I think D is wrong. The answer is A,because syntax of D is incorrect
is that correct?
D is only right,
A is wrong because rownum first takes rownum then it applyes the next filters and orders on them.
D IS CORRECT (N ANALYSIS)
THE SYNTAX
select [column_list], ROWNUM [ROWNUM_ALIAS]
from (select [column_list] from table ORDER BY Top-N_column)
where ROWNUM<=N;
SO D IS CORRECT ✔
@Emad, Totally agree with you, D is correct apart from the missing “)” in the code.
Answer D has 2 left parenthesis and only 1 right parenthesis.
The correct answer, I think “almost correct” is D. Because it has little mistake in the subquery: there is a “(” between WHERE and FINISH_DATE, then D es wrong.
The option D takes the ten highest marks in the subquery, then, it numbered each one in descending order.
For a moment thonght that C was correct, but, ROWNUM<=10 cuts the result before ORDER BY marks DESC will be applied.
If any student to finish his course on 31/12/1999 , none of the above would return the records of the day . To include all records of every day of the year 1999, the correct would be:
” finish_date BETWEEN ’01-JAN-99′ AND ’01-JAN-00′ “