SQL Madness

UPDATE GRADE INNER JOIN MATH119_ENROLLED ON GRADE.shrtckg_grde_code_final = MATH119_ENROLLED.shrtckg_grde_code_final
SET MATH119_ENROLLED.grade_40_scale = [grade].[FROM20082009]
WHERE (((MATH119_ENROLLED.shrtckn_term_code)>=”200840″));

This is this SQL statement Dr. Wang wrote on my data analytics data preparation assignment. It converted letter grades to the equivalent 4.0 scale grade based on term. This is pretty complicated, and I’m not exactly sure how it all works. This is my attempt to work through and understand it.

First of all, I notice this SQL statement does not contain a SELECT command. It has UPDATE in its place. This may be because we’re not retrieving information, just updating a table. In this case, the UPDATE command is right before GRADE, but the field to be updated is actually in the other table, MATH119_ENROLLED.

This statement joins tables GRADE and MATH119_ENROLLED using the PK/FK shrtckg_grde_code_final. The SET command seems to be the function that actually changes the values in the field. This command is setting the values of MATH119_ENROLLED.grade_40_scale to the values of GRADE.FROM20082009 only on the values where MATH119_ENROLLED.skrtckg_term_code is greater than or equal to 200840, which is Fall semester, 2008. This would have to be run again with a couple of revisions to change the values of the grades on earlier semesters.