Hi I currently have a database set up with a table like this:
Grades: student name|class name|overall|hw|test|quiz|project|val
student name and
class name are type
varchar2 with the rest being type
number. The categories
quiz are expected to be
val is the actual grade out of 100. So a sample row with a student getting a 95 on her test would look like this:
Grades: Sally | Biology | 0 | 0 | 1 | 0 | 0 | 95
This structure is clearly suboptimal, so I decided to normalize this database with help from this question here to :
Student: id|firstname | lastname (pk = id) Class: id|name|room num (pk = id) Categories: id|name|weight (pk = (id, name, weight)) Grades: student_id (fk)| class_id (fk)| category_id (fk) | name | grade_val ClassEnrollment: student_id (fk)| class_id (fk) | overall_grade (pk = student_id, class_id)
My main question is how to get the data from three tables to fill out the class enrollment table?
I can select all the
overall grade using:
SELECT val FROM grades_old WHERE overall = 1;
And I can select the student id in a number of steps :
INSERT INTO temp(student, class) SELECT student_name, class_name FROM grades_old; SELECT temp.student_name, students.name, students.id INNER JOIN students ON temp.student_name = students.name
However, I'm not sure how to put all this together to get the overall grade, student_id and class_id in one query.
I have manually filled out the Category database since there is only 5 categories in the system right now (overall, hw, test, quiz and project). I have a name column in the student table for now for purposes of matching, I figured I could worry about splitting it later then drop that column once I have split the names.
So I guess my focused question is for now how can I select the overall grade, student_id and class_id from the original grades table to insert it into the class Enrollment table?