title: Calculating GPA in query
body: <p>I'm having some trouble connecting the tables on this one to get a single result. Here's how you calculate GPA:</p>
  
  <ol>
  <li>Get the average grade for each course.</li>
  <li>Multiply each average by the number of credit hours in said course.</li>
  <li>Add the results from 2.</li>
  <li>Divide the result from 3 by the total number of credit hours.</li>
  </ol>
  
  <p>I have three relevant tables:</p>
  
  <pre><code>CREATE TABLE Student
  (
  SSN        INT(11),
  SName    VARCHAR(25),
  Major    VARCHAR(25),
  PRIMARY KEY (SSN)
  );
  
  CREATE TABLE Course
  (
  CNO        INT(11),
  CName    VARCHAR(3),
  CreditHour    TINYINT(4),
  PRIMARY KEY (CNO)
  );
  
  CREATE TABLE Grade
  (
  SSN        INT(11),
  CNO        INT(11),
  Grade        TINYINT(4)
  );
  </code></pre>
  
  <p>Unfortunately, SQL fiddle seems to be down, so I can't make a fiddle for this. How would I make a select statement to list the student info with a calculated gpa column? Every time I attempt this, I end up with an overblown set of nested subqueries, like this:</p>
  
  <pre><code>SELECT student.*, (total/hours) as gpa WHERE hours = 
     (SELECT SUM(course.creditHours) AS hours 
     FROM course, student, grade 
     WHERE course.cno = grade.cno 
     AND grade.ssn = student.ssn 
     GROUP BY course.cno) 
     AND total = (SELECT SUM(AVG(grade.grade)*course.creditHours) as total 
     FROM grade, course, student 
     WHERE grade.ssn = student.ssn 
     AND grade.cno = course.cno)
  </code></pre>
  
  <p>That doesn't actually appear to be going in the right direction, either. What should I be doing to derive this column?</p>