Inserting Data into the Database

The process of inserting data into the database is also RDBMS specific in many cases, and as such, we'll flag vendor differences when they arise. We won't list the full set of INSERT statements in this appendix because they're quite lengthy. For the full set, download the code from the Downloads section of the Apress Web site (http://www.apress.com).

Professor

Insert the following data into the Professor table:

INSERT INTO Professor (ProfessorID,Name)
VALUES (1,'Prof. Dawson');

INSERT INTO Professor (ProfessorID,Name)
VALUES (2,'Prof. Williams');

INSERT INTO Professor (ProfessorID,Name)
VALUES (3,'Prof. Ashby');
...

Course

Insert the following data into the Course table:

INSERT INTO Course (CourseID,Name,Credits)
VALUES (1,'Mediaeval Romanian',5);

INSERT INTO Course (CourseID,Name,Credits)
VALUES (2,'Philosophy',5);

INSERT INTO Course (CourseID,Name,Credits)
VALUES (3,'History of Computing',5);
...

Room

Insert the following data into the Room table:

INSERT INTO Room (RoomID,Comments,Capacity)
VALUES (1,'Main hall',500);

INSERT INTO Room (RoomID,Comments,Capacity)
VALUES (2,'Science Department',200);

INSERT INTO Room (RoomID,Comments,Capacity)
VALUES (3,'Science Room 1',100);
...

Class

Insert the following data into the Class table:

INSERT INTO Class (ClassID,CourseID,ProfessorID,RoomID,Time)
VALUES (1,1,1,6,'Mon 09:00-11:00');

INSERT INTO Class (ClassID,CourseID,ProfessorID,RoomID,Time)
VALUES (2,2,1,5,'Mon 11:00-12:00, Thu 09:00-11:00');

INSERT INTO Class (ClassID,CourseID,ProfessorID,RoomID,Time)
VALUES (3,3,2,3,'Mon 14:00-16:00');
...

Student

Insert the following data into the Student table:

INSERT INTO Student (StudentID,Name)
VALUES (1,'John Jones');

INSERT INTO Student (StudentID,Name)
VALUES (2,'Gary Burton');

INSERT INTO Student (StudentID,Name)
VALUES (3,'Emily Scarlett');
...

Exam

Note that the format you use to enter the date may vary between RDBMS. The following format is one of the most commonly used formats (YYYY-MM-DD). You may find that Oracle, for example, prefers dates in this format to be prefixed by the word DATE, for example, DATE '2003-05-03'. Alternatively, you could provide them in the format 03-May-2003.

Insert the following data into the Exam table:

INSERT INTO Exam
   (ExamID,CourseID,ProfessorID,SustainedOn,Comments)
VALUES (1,1,1,'2003-03-12','A difficult test that should last an hour');
INSERT INTO Exam
   (ExamID,CourseID,ProfessorID,SustainedOn,Comments)
VALUES (2,2,1,'2003-03-13','A simple two hour test');

INSERT INTO Exam
   (ExamID,CourseID,ProfessorID,SustainedOn,Comments)
VALUES (3,3,2,'2003-03-11','1 hour long');

INSERT INTO Exam (ExamID,CourseID,ProfessorID,SustainedOn)
VALUES (4,4,3,'2003-03-18');
...

Enrollment

Again, take care when inserting date information:

INSERT INTO Enrollment
   (EnrollmentID,StudentID,ClassID,EnrolledOn,Grade)
VALUES (1,1,1,'2002-09-23',62);

INSERT INTO Enrollment
   (EnrollmentID,StudentID,ClassID,EnrolledOn,Grade)
VALUES (2,1,2,'2002-09-30',70);

INSERT INTO Enrollment
   (EnrollmentID,StudentID,ClassID,EnrolledOn,Grade)
VALUES (3,2,3,'2003-09-23',51);
...

StudentExam

Insert the following data into the Exam table:

INSERT INTO StudentExam
   (StudentID,ExamID,Mark,IfPassed,Comments)
VALUES (1,1,55,1,'Satisfactory');

INSERT INTO StudentExam
   (StudentID,ExamID,Mark,IfPassed,Comments)
VALUES (1,2,73,1,'Good result');
INSERT INTO StudentExam
   (StudentID,ExamID,Mark,IfPassed,Comments)
VALUES (2,3,44,1,'Scraped through');

INSERT INTO StudentExam
   (StudentID,ExamID,Mark,IfPassed,Comments)
VALUES (2,5,39,0,'Failed, and will need to retake this one later in the year');
...