Three queries needed with the same result
I'm a student in a couple of different database-related classes. After these classes, I'm 4 away from completing my Master's degree, so I am really wanting to get this done.
I have a lab assignment that reads as follows:
Write a query that performs a join, a subquery, and a correlated subquery using the student, enrollment, grade, and zipcode tables. Execute each query to show that it produces the same results.
Just FYI, the lab environment is running MySQL 5.5.45. If I was able to export the database to my local drive and install it to my instance of 8.0.x, I would. Unfortunately, there isn't a standard command prompt available to run the necessary commands to do it :(
Without displaying the table contents, I can tell you this much:
STUDENT, ENROLLMENT, AND GRADE can all be linked to each other (i.e., PK/FK) via STUDENT_ID; ZIP is the PK for ZIPCODE and only exists in STUDENT
I want to execute a query that pulls the student name (STUDENT.LAST_NAME, STUDENT.FIRST_NAME), their enrollment date for the classes they are enrolled in (ENROLLMENT.ENROLL), and their "Final" grade (GRADE.GRADE_TYPE_CODE = 'FI').
I would like to do this for all students either living in a specific ZIP/range of ZIPs (WHERE STUDENT.ZIP = '100%') or a specific state (WHERE ZIPCODE.STATE = 'GA')
I have been working on the queries for the past week but I keep running into various errors. I even went from attempting the JOIN query to attempting the Subquery option with no luck (I'm not familiar with how to write a Correlated Subquery, so I'm kind of waiting on doing that one until I get the actual Subquery version written out and working).
Below is an example of the JOIN query I started on:
SELECT
STUDENT.STUDENT_ID,
STUDENT.LAST_NAME,
STUDENT.FIRST_NAME,
STUDENT.ZIP,
ZIPCODE.STATE,
ENROLLMENT.ENROLL_DATE,
ENROLLMENT.SECTION_ID,
GRADE.NUMERIC_GRADE
FROM STUDENT AS S1, STUDENT AS S2, ENROLLMENT, GRADE, ZIPCODE
WHERE STUDENT.ZIP = ZIPCODE.ZIP
INNER JOIN S1
WHERE S1.STUDENT_ID = ENROLLMENT.STUDENT_ID
INNER JOIN S2
WHERE S2.STUDENT_ID = GRADE.STUDENT_ID
WHERE GRADE.GRADE_CODE_TYPE > 'FI'
ORDER BY
STUDENT.STUDENT_ID, ENROLLMENT.SECTION_ID, GRADE.NUMERIC_GRADE ASC;
When I run this query, I get the following error:
Failed to execute SQL:
SQL SELECT STUDENT.STUDENT_ID, STUDENT.LAST_NAME, STUDENT.FIRST_NAME, STUDENT.ZIP, ZIPCODE.STATE, ENROLLMENT.ENROLL_DATE, ENROLLMENT.SECTION_ID, GRADE.NUMERIC_GRADE FROM STUDENT, ENROLLMENT, GRADE, ZIPCODE WHERE STUDENT.ZIP = ZIPCODE.ZIP INNER JOIN STUDENT AS S1 WHERE S1.STUDENT_ID = ENROLLMENT.STUDENT_ID INNER JOIN STUDENT AS S2 WHERE S2.STUDENT_ID = GRADE.STUDENT_ID WHERE GRADE.GRADE_CODE_TYPE > 'FI' ORDER BY STUDENT.STUDENT_ID, ENROLLMENT.SECTION_ID, GRADE.NUMERIC_GRADE ASC; failed:
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'INNER JOIN STUDENT AS S1 WHERE S1.STUDENT_ID = ENROLLMENT.STUDENT_ID INNER JOI' at line 1
Mind you, this is after much wailing and gnashing of teeth for the past several days. The subquery version is more convoluted IMO, so I'm just trying to focus on one of the queries then attempt to build out the other queries from there. I reached out to a tutor online and we made a little progress but we were still hitting roadblocks. So, I now reach out to you all for help.
Thanks in advance!