Inserting Data into School Database

Now that we’ve learned how to use the ALTER command to modify our database structure, it’s time to fill these tables with some data. In this section, you’ll be performing INSERT operations to populate our altered students, courses, and grades tables with sample data.

We will insert at least 10-15 rows into each table to get a good dataset for further operations.

Inserting Data into the Students Table

Let’s begin with the students table. We’ve altered the table to include columns for student_id, full_name, and email. Now, we will insert sample data into the table.

Here’s the basic INSERT statement:

INSERT INTO students (student_id, full_name, email)

VALUES (1, 'Alice Johnson', 'alice.johnson@email.com'),

(2, 'Bob Smith', 'bob.smith@email.com'),

(3, 'Cathy Brown', 'cathy.brown@email.com'),

(4, 'David Lee', 'david.lee@email.com'),

(5, 'Eva Green', 'eva.green@email.com'),

(6, 'Frank Miller', 'frank.miller@email.com'),

(7, 'Grace Cooper', 'grace.cooper@email.com'),

(8, 'Henry Ford', 'henry.ford@email.com'),

(9, 'Isla Adams', 'isla.adams@email.com'),

(10, 'Jack White', 'jack.white@email.com'),

(11, 'Karen Black', 'karen.black@email.com'),

(12, 'Liam Nelson', 'liam.nelson@email.com'),

(13, 'Mona Lisa', 'mona.lisa@email.com'),

(14, 'Noah James', 'noah.james@email.com'),

(15, 'Olivia Brooks', 'olivia.brooks@email.com');

This statement inserts 15 students into the students table with their respective email addresses. After executing this, you will have a fully populated students table ready for action.

Inserting Data into the Courses Table

Next, let’s move on to the courses table. We added a course_name column to store the names of the courses, and now we will populate the table with data.

Here’s how you can insert courses:

INSERT INTO courses (course_id, course_name)

VALUES (1, 'Mathematics 101'),

(2, 'English Literature 201'),

(3, 'Physics 102'),

(4, 'History 202'),

(5, 'Biology 101'),

(6, 'Chemistry 202'),

(7, 'Computer Science 101'),

(8, 'Sociology 102'),

(9, 'Philosophy 101'),

(10, 'Art History 201'),

(11, 'Political Science 202'),

(12, 'Statistics 301'),

(13, 'Economics 101'),

(14, 'Psychology 202'),

(15, 'Environmental Science 303');

This inserts 15 courses into the courses table. Each course has a unique ID and name. With this data in place, we can now move on to the grades.

Inserting Data into the Grades Table

Finally, let’s fill in the grades table. This table links the students and courses tables, and we will assign each student a grade for different courses. Remember, we altered the grade column to allow decimal values (FLOAT), so we can assign precise grades.

Here’s the INSERT statement for the grades table:

INSERT INTO grades (student_id, course_id, grade)

VALUES (1, 1, 85.5),

(1, 2, 90),

(2, 3, 78),

(3, 1, 88.75),

(4, 4, 92),

(5, 5, 95);

Want to print your doc?
This is not the way.

Try clicking the ⋯ next to your doc name or using a keyboard shortcut (

CtrlP

) instead.