Here are my suggested additions, for making the later queries
easier to test.

	insert into registration values('00104', 1104, 'B', 'A', 'R');
	insert into registration values('00104', 1102, 'B', 'B', 'R');
	insert into registration values('00105', 1102, 'C', 'B', 'R');

	update crssection set facultyID = 555 where CsId = 1205;

To undo: rollback, or else:

	delete from registration where StudentID ='00104' and CsId= 1104;
	delete from registration where StudentID ='00104' and CsId= 1102;
	delete from registration where StudentID ='00105' and CsId= 1102;

	update crssection set facultyID = null where CsId = 1205;


Some SQL queries to be based on the Indo-US College Database.


1. List all classrooms with a capacity of at least 50.

	Select * from location where capacity >= 50;
	
	    ROOMID BUILDIN ROO   CAPACITY R
	---------- ------- --- ---------- -
	        14 Kennedy 204         50 L
        	15 Nehru   301         50 C
        
2. List all students who took CIS253 and were NOT Computer Science majors (either AAS or BS)

You do have to look up what the codes for the CS majors are; 
there's no other way to determine that.

	select s.last, s.first, s.majorID from student s, crssection c, registration r
	where s.studentId = r.studentId and r.csid = c.csid and 
	c.courseId = 'CIS253' and s.majorId NOT IN (200, 500);

LAST            FIRST
--------------- ------
Diaz            Jose
Patel           Rajesh
Patel           Rajesh
Lee             Brian

(I should have used SELECT DISTINCT)

3. List all students who took a course in WN03

	select distinct s.last, s.first from student s, crssection c, registration r
	where s.studentId = r.studentId and r.csid = c.csid 
	and c.TermId = 'WN03';

    LAST            FIRST
    --------------- -------
    Patel           Rajesh	2
    Rickles         Deborah	2
    Diaz            Jose	3
    
With additions:

    Patel           Rajesh
    Rickles         Deborah
    Lee             Brian
    Diaz            Jose
    Khan            Amir

4. List all students who took at least two courses in WN03. 

This is a straightforward GROUP BY / HAVING example.

	select s.last, s.first from student s, crssection c, registration r
	where s.studentId = r.studentId and r.csid = c.csid 
	and c.TermId = 'WN03'
	group by s.last, s.first
	having count(*) >= 2;

   LAST            FIRST
   --------------- ---------
   Patel           Rajesh
   Rickles         Deborah
   Diaz            Jose
   
With additions: 

    Patel           Rajesh
    Rickles         Deborah
    Lee             Brian
    Diaz            Jose

5. List all courses offered in WN03

select courseID from crssection where termID = 'WN03';

	CIS265
	CIS253
	MA150
	AC101

6. List all courses taught in a lab (RoomType = 'L')

	select courseID , termID from crssection c, location l 
	where c.roomID = l.roomId and L.roomtype = 'L';

    ------ ----
    CIS265 WN03
    CIS253 WN03
    CIS265 SP03
    CIS265 SP03
    CIS253 SP03
    CIS253 SP03
    CIS253 SP03

7. List all students who took a course taught by their advisor

	select s.last, s.first , c.courseId
	from student s, crssection c, registration r
	where s.studentId = r.studentId and r.csid = c.csid 
	and s.facultyID = c.facultyID;

    Diaz            Jose            CIS253
    Patel           Rajesh          CIS265

With additions:

    Diaz            Jose            CIS253
    Tyler           Mickey          CIS265
    Patel           Rajesh          CIS265
    Lee             Brian           AC101

8. List all students who took a course that their advisor has taught,
but not necessarily at the same time.

I did this with a 4-way join, with two crssections c1 and c2.
The student took c1, and the advisor taught c2. 
The registration is needed to tie s and c1 together,
and we also have to say the courses are the same (have the same CourseID),
and that the student's advisor did the teaching in c2.

-- student took c1
-- advisor taught c2

	select distinct s.last, s.first from student s, crssection c1,
	crssection c2, registration r
	where s.studentId = r.studentId and r.csid = c1.csid 
	and c1.courseID = c2.courseID
	and s.facultyId = c2.facultyID;

	Patel           Rajesh
	Diaz            Jose

With additions:

    Patel           Rajesh
    Rickles         Deborah
    Tyler           Mickey
    Lee             Brian
    Diaz            Jose

9. List all students who got all A's or B's in WN03

At least one A or B (not part of this problem, but here for comparison):
select s.last, s.first from student s, crssection c, registration r
where s.studentId = r.studentId and r.csid = c.csid 
AND c.termId = 'WN03' and r.final IN ('A', 'B');

Students and their grades (useful for checking your answer):
select s.last, s.first, c.courseID, c.termID, r.final 
from student s, crssection c, registration r
where s.studentId = r.studentId and r.csid = c.csid 
order by s.last;

Here's the answer. Note that I had to add the AND EXISTS 
to eliminate students who had taken NO courses in WN03.

	select distinct s.studentid, s.last, s.first
	from student s
	WHERE NOT EXISTS 
	    ( 
		Select r.final
		from crssection c, registration r
		where s.studentid = r.studentid 
		and c.CsId = r.CsId
		and c.termID = 'WN03'
		and r.final NOT IN ('A', 'B')
	    )
	AND
	EXISTS
	    ( 
		Select r.final
		from crssection c, registration r
		where s.studentid = r.studentid 
		and c.CsId = r.CsId
		and c.termID = 'WN03'
	    );

00104 Lee             Brian
00105 Khan            Amir

10. List all students who took CIS253 in WN03 and in SP03.

I chose to do this as a 5-way join, using c1,r1 to refer to 
the registration for the WN03 section and c2,r2 for SP03.

	select distinct s.studentid, s.last, s.first 
	FROM student s, registration r1, registration r2,
	crssection c1, crssection c2
	WHERE
	s.studentID = r1.studentID and s.studentID = r2.studentID
	AND r1.csID = c1.csID and r2.csID = c2.csID
	AND c1.termID = 'WN03' and c2.termID = 'SP03'
	AND c1.courseID = 'CIS253' and c2.courseID = 'CIS253'
	;

Another way to do this is with two SELECT statements,
one for the students taking the course in WN03 and one for SP03,
and INTERSECTing them:

	(select s.studentid, s.last, s.first
	 From student s, registration r, crssection c
	 where s.studentid = r.studentid and r.csid = c.csid 
	 and c.courseid = 'CIS253' and c.termID='WN03')
	INTERSECT
	(select s.studentid, s.last, s.first
	 From student s, registration r, crssection c
	 where s.studentid = r.studentid and r.csid = c.csid
	 and c.courseid = 'CIS253' and c.termID='SP03')
	
----- --------------- -----------
00102 Patel           Rajesh
00104 Lee             Brian
00105 Khan            Amir