This lecture covers populating the student enrollment system with sample data.
Our Student Enrollment System database is structured and ready. Now, let’s fill it with some realistic sample data. This will allow us to test our queries and see the relationships in action.
Remember the order of insertion: parent tables first, then child tables.
1. Insert Instructors
INSERT INTO Instructors (InstructorName, Email) VALUES ('Dr. Smith', 'smith@university.edu');
INSERT INTO Instructors (InstructorName, Email) VALUES ('Prof. Johnson', 'johnson@university.edu');
INSERT INTO Instructors (InstructorName, Email) VALUES ('Dr. Lee', 'lee@university.edu');2. Insert Students
INSERT INTO Students (StudentName, Major) VALUES ('Alice Brown', 'Computer Science');
INSERT INTO Students (StudentName, Major) VALUES ('Bob White', 'Mathematics');
INSERT INTO Students (StudentName, Major) VALUES ('Charlie Green', 'Physics');
INSERT INTO Students (StudentName, Major) VALUES ('Diana Prince', 'Computer Science');3. Insert Courses
We need to link these courses to our instructors using their `InstructorID`s. Assume Dr. Smith is ID 1, Prof. Johnson is ID 2, and Dr. Lee is ID 3.
INSERT INTO Courses (CourseName, Department, Credits, Instructor_ID)
VALUES ('Introduction to SQL', 'Computer Science', 3, 1);
INSERT INTO Courses (CourseName, Department, Credits, Instructor_ID)
VALUES ('Calculus I', 'Mathematics', 4, 2);
INSERT INTO Courses (CourseName, Department, Credits, Instructor_ID)
VALUES ('Physics I', 'Physics', 4, 3);
INSERT INTO Courses (CourseName, Department, Credits, Instructor_ID)
VALUES ('Advanced SQL', 'Computer Science', 3, 1);4. Insert Enrollments
Now, let’s enroll our students in courses and assign some grades. We’ll need to use the `StudentID` and `CourseID` to link them. You can find these IDs by running `SELECT * FROM Students;` and `SELECT * FROM Courses;`.
Let’s assume:
- Alice Brown: ID 1
- Bob White: ID 2
- Charlie Green: ID 3
- Diana Prince: ID 4
- Intro to SQL: ID 1
- Calculus I: ID 2
- Physics I: ID 3
- Advanced SQL: ID 4
-- Alice enrolls in Intro to SQL (A) and Advanced SQL (B)
INSERT INTO Enrollments (Student_ID, Course_ID, Grade) VALUES (1, 1, 'A');
INSERT INTO Enrollments (Student_ID, Course_ID, Grade) VALUES (1, 4, 'B');
-- Bob enrolls in Calculus I (C)
INSERT INTO Enrollments (Student_ID, Course_ID, Grade) VALUES (2, 2, 'C');
-- Charlie enrolls in Physics I (A)
INSERT INTO Enrollments (Student_ID, Course_ID, Grade) VALUES (3, 3, 'A');
-- Diana enrolls in Intro to SQL (B) and Advanced SQL (A)
INSERT INTO Enrollments (Student_ID, Course_ID, Grade) VALUES (4, 1, 'B');
INSERT INTO Enrollments (Student_ID, Course_ID, Grade) VALUES (4, 4, 'A');
Data Ready for Querying
Our Student Enrollment System is now fully populated with data. We have a rich dataset that will allow us to practice complex queries involving multiple joins and aggregate functions.
In the Next Lecture…
We will begin querying our new database. We’ll start with simple queries and gradually build up to more complex ones, answering questions like “Which students are taking which courses?” and “What is the average grade for each course?”
