/*
CS 122
Spring 2004
Lesson 4
Author: Jen Chen
Copyrighted by Jen Chen

  - Database: Student.mdb
  - Table: Grade_Report(Student_Number, Section_ID, Grade)
  - Task: To find the GPA of the student whose Student_Number is 2.
  Solution: First find the total grade for classes with A's, then multiply
            this value by 4 (since A = 4) to get the total for these classes.
   	    Next find tht total grade for classes with B's, then multiply
	    this value by 3 (since B = 3) to get the total for these classes.
	    Then find the total for C's classes, D's classes and F's classes.
            Once these grades are found, combine these grades by using UNION.
	    This will generate a table with fields that contain information
	    about the total GPA of this student.
	    Next write an SQL statement to find the GPA of this student using
	    the result from the table that we created above).
/*
select STUDENT_NUMBER,sum(TTLGPA) AS GPA, sum(Classes)*4 AS units, sum(TTLGPA)/(sum(Classes)*4) AS GRADE
from
(
select STUDENT_NUMBER, COUNT(SECTION_ID)*4*4 AS TTLGPA, COUNT(SECTION_ID) AS Classes
from
Grade_Report
where
Grade='A'
AND STUDENT_NUMBER=2
GROUP BY STUDENT_NUMBER
UNION
select STUDENT_NUMBER, COUNT(SECTION_ID)*3*4 AS TTLGPA, COUNT(SECTION_ID) AS Classes
from
Grade_Report
where
Grade='B'
AND STUDENT_NUMBER=2
GROUP BY STUDENT_NUMBER
UNION
select STUDENT_NUMBER, COUNT(SECTION_ID)*2*4 AS TTLGPA, COUNT(SECTION_ID) AS Classes
from
Grade_Report
where
Grade='C'
AND STUDENT_NUMBER=2
GROUP BY STUDENT_NUMBER
UNION
select STUDENT_NUMBER, COUNT(SECTION_ID)*1*4 AS TTLGPA, COUNT(SECTION_ID) AS Classes
from
Grade_Report
where
Grade='D'
AND STUDENT_NUMBER=2
GROUP BY STUDENT_NUMBER
UNION
select STUDENT_NUMBER, COUNT(SECTION_ID)*0 AS TTLGPA, COUNT(SECTION_ID) AS Classes
from
Grade_Report
where
Grade='F'
AND STUDENT_NUMBER=2
GROUP BY STUDENT_NUMBER)
GROUP BY STUDENT_NUMBER;