SQL how to populate Join table

[aidenrelkoff]

I am taking computer science and im working on database design. This is my second join table, but its essentially the same format as the first one.

#second
CREATE TABLE `Enrollment` (
 course_ID SMALLINT NOT NULL,
 student_ID SMALLINT NOT NULL,
 finalGrade SMALLINT NOT NULL,
 letterGrade CHAR(1),
 FOREIGN KEY (`course_ID`) REFERENCES `Course` (`course_ID`) ON DELETE CASCADE,
 FOREIGN KEY (`student_ID`) REFERENCES student (`student_ID`) ON DELETE CASCADE
) ENGINE = InnoDB;

What i need to know is how to get data from three other tables. this is what the tables contain

Course:

course_ID

 

Student:

student_ID

 

Table_1:

finalGrade

letterGrade

 

Student name is contained in both `Student` and `Table_1` so i assume that would be how i would get the correct data. 

Basically i need to make sure that the correct student has the correct course_ID and the right letterGrade and finalGrade.

 

This is what my friend has for his database, its working fine, my database has things in other locations, so it would be different, but here it is anyways:

INSERT INTO enrollment
(studentID, courseID, final_grade_percent, letter_grade)
SELECT (SELECT `studentID` FROM `student` WHERE `student`.`first_name` = `Student Firstname` AND `student`.`last_name` = `Student Lastname`) , 
(SELECT `courseID` FROM `course` WHERE `course`.`Course Data` = `table 6`.`Course Data`), 
`Final Grade %`, `Letter Grade`
FROM  `table 6`;

 

any help at all would be fantastic

[Yamoto42]

You have given conflicting definitions of Table_1.

27 minutes ago, aidenrelkoff said:

Table_1:

finalGrade

letterGrade

 

27 minutes ago, aidenrelkoff said:

Student name is contained in both `Student` and `Table_1`

Is this a database your instructor provided?  If so, please list it completely, including primary keys.  If not, the "course_ID" and "student_ID" should be your keys.  Certain properties are useful as keys, which is why we often create arbitrary values with those properties.


What is the purpose of Table_1?  As it stands, if you are making an "enrollment" table then Table_1 is redundant.  Nothing other than foreign keys should ever be stored in multiple places in a database, but the formal process (normalization) for doing this will be something you learn probably in a few weeks.