title: How to calculate GPA from values from two tables
body: <pre class="lang-sql prettyprint-override"><code>create table student (
  number      int identity(1,1) primary key,
  s_id       int ,
  s_full_name   varchar(50),
  Academic_year   int,
  sex        varchar(10),
  block_num    int,
  dorm_num    int,
  depar_name    varchar(30),
  c_name       varchar(30) foreign key references course(c_name),
  grade      char,
  GPA             float,
  gradeWeight     float
  );
  
  create table studMark(
    c_name varchar(30), 
    ID int,
    marks_5 int,
    marks_10 int,
    marks_15 int,
    marks_20 int,
    marks_50 int
  ) 
  
  update student set  s_id=p.ID,  grade=
  CASE WHEN (marks_5+marks_10+marks_15+marks_20+marks_50) &gt; 84.5
      THEN 'A' 
      WHEN (marks_5+marks_10+marks_15+marks_20+marks_50) &lt; 84.5 AND (marks_5+marks_10+marks_15+marks_20+marks_50) &gt; 64.5
      THEN 'B'
      WHEN (marks_5+marks_10+marks_15+marks_20+marks_50) &lt; 64.5 AND (marks_5+marks_10+marks_15+marks_20+marks_50) &gt; 49.5
      THEN 'C'
      WHEN (marks_5+marks_10+marks_15+marks_20+marks_50) &lt; 49.5 AND (marks_5+marks_10+marks_15+marks_20+marks_50) &gt; 29.5
      THEN 'D'
      ELSE 'F'
    end
    FROM studMark p, student s where s_id=p.ID and s.c_name=p.c_name
  
  update student set gradeWeight=
   CASE WHEN grade='A'  
      THEN 4
     WHEN grade='B'  
      THEN 3.5
     WHEN grade='c'    
      THEN 3.0
     WHEN grade='D'  
      THEN  2.5
     WHEN grade='E'  
      THEN 2.0
    WHEN grade='F'  
      THEN  0
    end
    FROM student
  </code></pre>
  <p>The SQL below is to calculate GPA but it takes the average of the calculated value for all Id:</p>
  <pre><code>update student set GPA=
  ( select (sum(t.gradeWeight*c.c_cHr))/sum(c.c_cHr) as gpa from student t,Course c,studMark z where t.c_name=c.c_name and c.c_name= z.c_name and z.ID=t.s_id)
  </code></pre>