How to get the student academic progress?

Question

course Table

course_code   course_name      credit_points_reqd
1             Comp Science     300
2             Soft Engineering 300

subject Table

subject_code   subject_name    credit_points
CS123          C Prog          15
CS124          COBOL           15

enrolment table

student_id    student_name  course_code      subject_code      Results  
1             Lara Croft    1                CS123               70
1             Lara Croft    1                CS124               50
2             Tom Raider    2                CS123               60
2             Tom Raider    2                CS124               40
3             James Bond    1                CS123               NULL
3             James Bond    1                CS124               40

OUTPUT TABLE

student_name   course_name      credit_points_obt    credit_points_reqd
Lara Croft     Comp Science     30                   300          
Tom Raider     Soft Engineering 15                   300

I'm currently using TSQL. So here's the situation. I've prepared these tables to get the output like the way it i showed u up there. I need to calculate the credit points obtained. Credit points are achieved if the student receives > 50 for a subject they took. I want to ignore students that has not received any credit points at all (eg, James Bond is ignored as he has not achieved any points yet)

select student_name, course_name,credit_points_obt,credit_points_reqd
FROM enrolment (SELECT student_full_name, SUM(credit_points) AS credit_points_obt
               FROM enrolment
               GROUP BY student_id),

Totally stuck...I have no idea where to go now.

Answer 1

You can sum conditionally to get points for subject. If none are given result will be null, so you filter out those student/course pairs in having clause.

I've changed > 50 condition to >= 50 because your results contradict your requirements. Also, by the data I'd say that you have omitted student table for brewity, but if you haven't, it is a must.

Live test is @ Sql Fiddle.

select enrolment.student_name, 
       course.course_name,
       course.credit_points_reqd,
       sum(case when enrolment.results >= 50 
                then subject.credit_points 
            end) credit_points_obt
  FROM enrolment
 inner join course
    on enrolment.course_code = course.course_code
 inner join subject
    on enrolment.subject_code = subject.subject_code
 group by enrolment.student_name, 
          course.course_name, 
          course.credit_points_reqd
having sum(case when enrolment.results >= 50 
                then subject.credit_points 
            end) is not null