Grading schema for homework 2: Total 30 points

Part 1. 10 points 

- creating all the tables, correct as described in page 43: 5 points
- inserting data as directed: 5 points 

Part 2:

2.3 10 points 

Write an SQL statement that
a. Returns the Ids of all seniors in the table Student (3 points)
Solution:
	SELECT S.Id
	FROM Student S
	WHERE S.Status = 'senior'

b. Deletes all seniors from Student (3 points)
Solution:
	DELETE
	FROM Student S
	WHERE S.Status = 'senior'

c. Promotes all juniors in the table Student to seniors (4 points)
Solution:
	UPDATE Student S
	SET S.Status = 'senior'
	WHERE S.Status = 'junior'

NOTE: 

a. One should be consistent in writting the solutions. For example,
if one uses 'Senior' instead of 'senior' then 'Senior' should be used 
throughout.

b. One can drop the aliases (in this case: the prefix 'S.') or use
different aliases (e.g. 'Student')  from/in all of the above solutions. 

2.5 (10 points)
Using the Transcript table, write an SQL statement that 
a. Deregisters the student with Id = 123456789 from the 
course CS305 for the fall of 2001 (3 points)
Solution:
	DELETE
	FROM Transcript
	WHERE StudId = 123456789
	AND CrsCode = 'CS305' AND Semester = 'F2001'

b. Changes to an A the grade assigned to the student with Id = 123456789 
for the course CS305 taken in the fall of 2000 (3 points)
Solution:
	UPDATE Transcript
	SET Grade = 'A'
	WHERE StudId = 123456789
	AND CrsCode = 'CS305' AND Semester ='F2000'

c. Returns the Id of all students who took CS305 in the fall of 2000 (4 points)
Solution:
	SELECT StudId
	FROM Transcript
	WHERE CrsCode = 'CS305' AND Semester = 'F2000'

NOTE: In the above answers, the aliases are omitted.