Assigment 2 (Due September 8, 11:49 pm)

  1. Practicing Oracle
    bulletcreate the tables Students (with Id, Name, Address, and Status) and Transcripts (with Studid, CrsCode, Semester, and Grade) as described in the textbook  (pg. 43)
    bulletinsert 5 students into the table Students (2 seniors, 1 freshman, 1 junior, 1 sophomore)
    bulletinsert 10 tuples into the table Transcripts,  (2 for each students)

    You should submit the following:
    bulletthe commands that you use to create the tables and insert data into the tables

    Leave the data in your Oracle account so that the TA can check your work.

     

  2. Exercise 2.3 and 2.5 (pg. 25)

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.