The table sc contains the student id and the course id they attended, followed by it’s corresponding grade.
The table course contains the course id and corresponding credits
The table student1 contains the student id and corresponding info.
In this case I need to calculate the average score of every student and add the rank to table student1
The code is following:
create or replace procedure save_scholarship is
begin
UPDATE STUDENT1
SET STUDENT1.gpa = (
select rank() over (order by sum(sc.grade*course.credit)/sum(course.credit) desc)
from sc
, course
where sc.sno = student1.sno
and sc.cno = course.cno
and sc.sno not in (select sc.sno from sc where grade < 60)
group by sc.sno
order by sum(sc.grade*course.credit)/sum(course.credit) desc
)
;
end save_scholarship;
This is the error I got:
SQL Error: ORA-00907: missing right parenthesis
The error is located on : order by sum(sc.grade*course.credit)/sum(course.credit) desc);
Anyone can help me? I am new to Oracle 11g currently studying it.
I tried to run the subquery of the update SQL but it performed as good as expected.
Just can’t figure it out.
Advertisement
Answer
Note: I’m pretty sure that the Oracle optimizer is not naive enough to regenerate the “ranked_students” subquery below for every row updated, but of course one should always test your optimizations on significant amounts of data where applicable, and use “explain plan” to decide what’s best.
It appears to me that the issue with your query is that the rank() function is not producing the correct results, because it is only looking at a single student at a time, and therefore that student’s rank is always #1…with respect to themselves!
I also think you probably want dense_rank() in case two students have equal GPAs.
So I made an example query that uses a “with” clause to rank all of the students (that don’t have a grade < 60), and then uses that to update the student1 table.
update student1
set student1.gpa = (
with ranked_students as
(
select student1.sno,
dense_rank() over (
order by sum(sc.grade*course.credit)/sum(course.credit) desc
) as gpa
from student1
join sc on sc.sno = student1.sno
join course on course.cno = sc.cno
where not exists (
select 1 from sc where sc.sno =student1.sno
and sc.grade < 60
)
group by student1.sno
)
select gpa from ranked_students r
where r.sno = student1.sno
);
select * from student1 order by gpa;
+-----+-------+-----+
| SNO | SNAME | GPA |
+-----+-------+-----+
| 002 | Alice | 1 |
| 001 | Bob | 2 |
| 003 | Mary | - |
+-----+-------+-----+
Here are statements to recreate my testing tables:
create table sc as select '001' as sno, 'C1' as cno, 80 as grade from dual union select '001' as sno, 'C3' as cno, 70 as grade from dual union select '002' as sno, 'C2' as cno, 90 as grade from dual union select '003' as sno, 'C2' as cno, 59 as grade from dual union select '003' as sno, 'C5' as cno, 98 as grade from dual; create table student1 as select '001' as sno, 'Bob' as sname, 100 as gpa from dual union select '002' as sno, 'Alice' as sname, 100 as gpa from dual union select '003' as sno, 'Mary' as sname, 100 as gpa from dual; create table course as select 'C1' as cno, 4 as credit from dual union select 'C2' as cno, 4 as credit from dual union select 'C3' as cno, 3 as credit from dual union select 'C4' as cno, 3 as credit from dual union select 'C5' as cno, 1 as credit from dual;
The above tables describe Alice as having a high GPA, Bob as having a lower one, and Mary having a grade that is below 60 which disqualifies her from ranking.
Here is a query to illustrate the GPA averages:
select student1.sno, student1.sname, round(sum(sc.grade*course.credit)/sum(course.credit),0) as gpa from student1 join sc on sc.sno = student1.sno join course on course.cno = sc.cno group by student1.sno, student1.sname order by gpa desc; +-----+-------+-----+ | SNO | SNAME | GPA | +-----+-------+-----+ | 002 | Alice | 90 | | 001 | Bob | 76 | | 003 | Mary | 67 | +-----+-------+-----+