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

Due: Nominally Monday, Feb 26
May be turned in for full credit through Friday, Mar 2

See below for additional details!

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

2. List all students who took CIS253 and were NOT Computer Science majors (either AAS or BS)

3. List all students who took a course in WN03

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

5. List all courses offered in WN03

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

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

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

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

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

==============================================================

Here's a minor update regarding the SQL queries above.

For query 1, I ask you to list all classrooms with a capacity
of at least 50. You may interpret this in either of two ways:
(a) all rooms of type 'C' (for classroom) with size >=50
(b) all rooms period with size >=50

(a) is more logical, but since I didn't pick this up in my 
original version, I will accept (b).

============================

For the table Registrations, I have no idea what the Status column
means; in particular, I have no idea what 'X' means. I suggest
ignoring this column entirely.

============================

For query 8, "not necessarily at the same time" is a little 
ambiguous; I should have worded it
	List all students who took a course that their
	advisor has taught; the student need not have
	taken the same section their advisor taught.
That is, the student could have taken the course another semester,
or have taken one of the other sections (the only time this
happens is with CIS253 and CIS265 in SP03).

============================

For query 9, there are NO students who got all A's or B's.
For queries 7 and 8, the answers are the same, because there
are no students who took a different section of a course their 
advisor taught. In order to make testing easier, here are
some entries you can ADD to the database to make the answer for
9 nonempty and the answers for 7 and 8 distinct. (Note that these
additions change some other answers too.)

	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 these entries (if you want to), you can either use ROLLBACK,
or you can use the following:

	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;