The STUDENT_GRADES table has these columns:
STUDENT_IDNUMBER(12)
SEMESTER_ENDDATE
GPANUMBER(4,3)
The registrar has asked for a report on the average grade point average (GPA), sorted from the
highest grade point average to each semester, starting from the earliest date.
Which statement accomplish this?
A.
SELECT student_id, semester_end, gpa
FROM student_grades
ORDER BY semester_end DESC, gpa DESC;
B.
SELECT student_id, semester_end, gpa
FROM student_grades
ORDER BY semester_end, gpa ASC
C.
SELECT student_id, semester_end, gpa
FROM student_grades
ORDER BY gpa DESC, semester_end ASC;
D.
SELECT student_id, semester_end, gpa
FROM student_grades
ORDER BY gpa DESC, semester_end DESC;
E.
SELECT student_id, semester_end, gpa
FROM student_grades
ORDER BY gpa DESC, semester_end ASC;
F.
SELECT student_id,semester_end,gpa
FROM student_grades
ORDER BY semester_end,gpa DESC
It’s A – earliest means from now on, that’s why “semester_end DESC” needs to be specified
earliest means oldest, newest means now
Why option C is incorrect?
It also returns the result-set in semester_end in ascending order and gpa in descending order.
Bad quality of possible answers here – C and E are equal… :-/
The registrar asked for two options:
– semester_end ordered ASC (in fact, ascending is default and can be omitted)
– gpa DESC
This will eliminate A,B and D so far.
But now you have to remember the correct order in this question:
“sorted from the highest grade point average to each semester” (first, and then) “starting from the earliest date”
This will eliminate F!
In fact, C and E are equal – choose your favorite… 😉
The only correct answer is F.
Read the question carefully… They want to see the highest to lowest GPA within each semester, starting from earliest date. So we need to order by semester date ASC first, as this effectively groups the GPA by semester.
A is wrong because semester_end should say ASC or omitted
B is wrong because GPA should be DESC
C is wrong because the order by columns are in the wrong order
D is wrong because the order by columns are in the wrong order AND semester should be ASC
E is wrong because the order by columns are in the wrong order (same as C)
The key point of the answer is “starting from the earliest date”. Thats why column semester_end must be first in the order by clause and his default order is asc. F is correct.
starting from the earliest date – asc
sorted from the highest – desc
semester_end,gpa DESC or semester_end ASC,gpa DESC
The question creates confusion !!!
After I read three times I conclude that F is the correct answer !
When I read first time I was sure that the answer is C,E egal.
Correct answer F !