Function help

[AdamThib]

Table:Department Column:DeptId, Name, Address, Phone
Table:Grade Column: Grade, Numerical
Table: Student_Course Column: StudentId, Department, Code, Final_Grade, Semester
Table: Student_Course_Instance Column: StudentId, Department, Code, Semester, Grade
Table:Course_Instance Column:DeptId, Code, Semester, Instructor

I need help writing a function to count_major_courses. Given a student ID, it should return the NUMBER of major related courses(Table: Student_Course Column:Department)this student has taken. I am new so the code would be greatly appreciated. Thanks


Also, can you please help me with this SQL function. I have no idea how to do this becuase I am just starting to use Oracle.
I need to write a function GPA_calc. Given a student ID, it returns the GPA of that student. Grades are based on a 4-point scale.

Table: Student
Column: StudentID(primary key social security #)

Table: Grade
Column: Grade (letter grade), Numerical(4.0 scale)

Sample code would greatly be appreciated. Thanks

create or replace function f_gpa
(p_student_id in student_courses.student_id%type)
return number
is
v_gpa number;
begin
select round(avg(g.value), 2)
into v_gpa
from student_courses cd, grades g
where cd.student_id = p_student_id
and g.grade = cd.grade;
return (v_gpa);end;/

This came out with an error


[Edited by AdamThib on 10-04-2002 at 10:42 PM]

[slimdave]

What was the error?

[Shestakov]

Error in code of function.
Has to be :

create or replace function f_gpa
(p_student_id in student_courses.student_id%type)
return number
is
v_gpa number;
begin
select round(avg(g.value), 2)
into v_gpa
from student_courses cd, grades g
where cd.student_id = p_student_id
and g.grade = cd.grade;
return (v_gpa);
end;
/

'/' <-- always in new line!!!