CS 122
Spring 2002
Project 3 solution
Written by J. Chen

	Read all questions carefully before you attempt to solve them. Clarify with 
	me if you are not clear about any question.
	Questions 1 to 5 are 18 points each. Question 6 is 10 points.

	(1) Note that in Computer Science, classes carry different weight. For
	example, CS 120, CS 122, CS 160, CS 190 all carry 3 units; CS 201, CS 202,
	CS 203 each carries 5 units; etc...
	    Now update your Classes table by doing the followings:
		- delete all records from your current Classes table.
		- create a new Classes table based on the records from the Classes
		table in the test-database.
		- Make sure that the newly created Classes-table have the multiple
		primary keys declared as in the Classes table under the test-database.

	Sol.:
	 drop table Classes;
	 create table Classes select * from test.Classes;
	 alter table Classes add key(class);
	 alter table Classes add key(instID);


	(2) Based on your newly created Classes table, what is the total number of 
	units offered in Spring 2005 quarter?
	    Your result set should contain the year, quarter, and the total number 
	of units.
	Sol.:
	   select quarter, year, sum(unit) 
		from 
		   Classes 
		where 
		   quarter = "Spring" 
		and 
		   year = "2005" 
		GROUP BY quarter; 


	(3) If a student takes CS 120, CS 203, and CS 290 this quarter; then what
 	is the total number of units this student carries?
	    Your result set should list the total number of units ONLY.

	Sol.:
	   select quarter, year, sum(unit) 
	   	from 
		   Classes 
		where 
		   quarter = "Spring" 
		and 
		   year = "2005" 
		and 
		   class in ('CS 101', 'CS 312', 'CS 440') 
		GROUP BY quarter; 
	

	(4) Find the grade point average (GPA) of a student with the following
	grade distribution:
		CS 122: A
		CS 203: B
		CS 290: B
	where A = 4, B = 3, C = 2, D = 1, and F = 0. DO NOT count NC (no credit) grade.
	    GPA is calculated as follow:
		- number of unit per class X corresponding numerical value for the grade.
		- Add values of all classes.
		- Divide this total by the total number of units.
	    Your result set should show this student's GPA only.
	Sol.:
	   Suggestion: Here is one way to solve this problem:
	 	- First we find the grade point for each class by using three seperate
		SELECT statement, then add these three values up.
		Next we divide this sum by the total number of units of these three classes
		to obtain the grade point average for these three classes.

	   select (
		   (select if(class='CS 122',unit*4,'') from Classes where class='CS 122' and unit <>0)
		     +
		   (select (select if(class='CS 203',unit*3,'') from Classes where class='CS 203' and unit <>0) as 'CS 203 GPA')
		     +	
		   (select (select if(class='CS 290',unit*3,'') from Classes where class='CS 290' and unit <>0) as 'CS 290 GPA'))
		     /
		   (select sum(unit) from Classes where class in ('CS 122','CS 203','CS 290')) as 'TTL GPA'


	(5) How many units does Mr. Jen Chen teach this quarter? Assume that you 
	don't know Mr. Chen's instructor id.
	    Your result set should include the Instructor's name, year, quarter,
	and the total number of units.

	Sol.:
	   select concat(I.firstName, ' ', I.lastName) AS Name, C.quarter, 
		C.year, sum(C.unit) as Units from Instructor I, Classes C where 
		I.lastName = 'Chen' and I.firstName = 'Jen' and C.instID = I.instID 
		GROUP BY I.instID; 


	(6) From the result of question 5, list the Instructor's name, year, quarter,
	class, section, and the class name for each of the class that Mr. Chen teaches.
	    Note: You may have to query from more than two tables.
	    Hint: You need to link each table via a primary key field (or any field
	in that these tables have in common).
	
	Sol.:
	   select concat(I.firstName, ' ', I.lastName) AS Name, C.quarter, 
		C.year, C.class, C.section, Cr.className as 'Class Name' 
	   from 
		Instructor I, 
		Classes C, 
		Courses2 Cr 
	   where 
		I.lastName = 'Chen' and 
		I.firstName = 'Jen' 
		and C.instID = I.instID 
		and C.class = Cr.Class;