CS 122- Spring 2005
Solution
Written by Jen Chen

(1) Add a primary key, called recNum of an integer type, into your Classes table.
    This field HAS TO BE the FIRST field in your table. 
   Sol.:
   alter table Classes add recNum int auto_increment first primary key;

(2) List all the classes that are in your Courses table.
    List the classes and their names ONLY in alphabetical order.
   Sol.:
    select class, className 
	from Courses 
	order by 1;

(3) How many classes are offered from your Courses table for this quarter?
    Your result set should contain the total number of classes ONLY.
   Sol.:
    select count(Cr.class) as 'Classes for Spring, 2005' 
	from Courses Cr, Classes C 
	where Cr.class=C.class 
	and C.year='2005' 
	and C.quarter='Spring';

(4)  List all the classes that are offered from your Courses table for this quarter.
     Sort your result according to the class.
     Your resutl set should include the class, class' name, quarter, and year.
   Sol.:
     select Cr.class,Cr.className,C.quarter,C.year 
	from Courses Cr, Classes C 
	where Cr.class=C.class 
	and C.year='2005' 
	and C.quarter='Spring' 
	order by C.class;

(5)  From your Classes table, list all professors (instID) who are on duty this quarter.
     Your resutl set should include the instID only.
     Sort your result in ascending order.
   Sol.:
     select distinct instID 
	from Classes 
	where year='2005' 
	and quarter='Spring' 
	and instID <> '' 
	order by 1;

(6)  From your Classes table, find how many professors teach this quarter.
     Your result set should display the quarter, the year, and the total 
     number of professors who teach this quarter.
   Sol.:
     select 'Spring ', '2005 ', count(distinct instID) as 'Number of Professors'
	from Classes 
	where year='2005' 
	and quarter='Spring' 
	and instID <> ''; 

(7)  List the first name, last name, and instructor' id for all instructors who teach this quarter.
     Your result set should include the last name, first name and the instructor's id.
     Sort your result set based on the instructor's id.
   Sol.:
     select distinct I.instID, I.firstName, I.lastName 
	from Instructor I, Classes C 
	where C.instID=I.instID 
	and C.year='2005' 
	and C.quarter='Spring' 
	order by C.instID;

(8)  From Courses table in the "test" database, find how many classes	offered about Data base.
     Your resutl set should include the class and the className.
     Sort your result set based on the class field.
     Note: To use any table from the "test" database, precede the table's 
     name with the name of the database. (ex: test.Courses)
   Sol.:
     select class as 'Database Classes', className 
	from test.Courses 
	where className like '%data%base%' 
	group by className 
	order by class;