Question

Can someone please fix my SQL SELECT code? I keep getting column ambiguously defined error.

/*8. List the students who have received any numeric grade score of at least 95 in an Advanced Java Programming course. Show student name, the grade type code, and the numeric grade.*/

SELECT STUDENT_ID, FIRST_NAME, LAST_NAME, GRADE_TYPE_CODE, NUMERIC_GRADE

FROM STUDENT JOIN ENROLLMENT ON STUDENT.STUDENT_ID = ENROLLMENT.STUDENT_ID

JOIN SECTION ON ENROLLMENT.SECTION_ID = SECTION.SECTION_ID

JOIN COURSE ON SECTION.COURSE_NO = COURSE.COURSE_NO

JOIN GRADE ON STUDENT.STUDENT_ID = GRADE.STUDENT_ID

WHERE GRADE.NUMERIC_GRADE >95 AND COURSE.DESCRIPTION = 'Advanced Java Programming'

GROUP BY CONCAT(FIRST_NAME, LAST_NAME), GRADE_TYPE_CODE, NUMERIC_GRADE;

Tables for the project:

CREATE TABLE INSTRUCTOR

(INSTRUCTOR_ID NUMBER(8,0)

,SALUTATION VARCHAR2(5)

,FIRST_NAME VARCHAR2(25)

,LAST_NAME VARCHAR2(25)

,STREET_ADDRESS VARCHAR2(50)

,ZIP VARCHAR2(5)

,PHONE VARCHAR2(15)

,CREATED_BY VARCHAR2(30)

,CREATED_DATE DATE

,MODIFIED_BY VARCHAR2(30)

,MODIFIED_DATE DATE

)

/

CREATE TABLE GRADE

(STUDENT_ID NUMBER(8,0)

,SECTION_ID NUMBER(8,0)

,GRADE_TYPE_CODE CHAR(2)

,GRADE_CODE_OCCURRENCE NUMBER(38,0)

,NUMERIC_GRADE NUMBER(3,0) DEFAULT 0

,COMMENTS VARCHAR2(2000)

,CREATED_BY VARCHAR2(30)

,CREATED_DATE DATE

,MODIFIED_BY VARCHAR2(30)

,MODIFIED_DATE DATE

)

/

CREATE TABLE GRADE_TYPE

(GRADE_TYPE_CODE CHAR(2)

,DESCRIPTION VARCHAR2(50)

,CREATED_BY VARCHAR2(30)

,CREATED_DATE DATE

,MODIFIED_BY VARCHAR2(30)

,MODIFIED_DATE DATE

)

/

CREATE TABLE GRADE_CONVERSION

(LETTER_GRADE VARCHAR2(2)

,GRADE_POINT NUMBER(3,2) DEFAULT 0

,MAX_GRADE NUMBER(3,0)

,MIN_GRADE NUMBER(3,0)

,CREATED_BY VARCHAR2(30)

,CREATED_DATE DATE

,MODIFIED_BY VARCHAR2(30)

,MODIFIED_DATE DATE  

)

/

CREATE TABLE GRADE_TYPE_WEIGHT

(SECTION_ID NUMBER(8,0)

,GRADE_TYPE_CODE CHAR(2)

,NUMBER_PER_SECTION NUMBER(3,0)

,PERCENT_OF_FINAL_GRADE NUMBER(3,0)

,DROP_LOWEST CHAR(1)

,CREATED_BY VARCHAR2(30)

,CREATED_DATE DATE

,MODIFIED_BY VARCHAR2(30)

,MODIFIED_DATE DATE

)

/

CREATE TABLE SECTION

(SECTION_ID NUMBER(8,0)

,COURSE_NO NUMBER(8,0)

,SECTION_NO NUMBER(3,0)

,START_DATE_TIME DATE

,LOCATION VARCHAR2(50)

,INSTRUCTOR_ID NUMBER(8,0)

,CAPACITY NUMBER(3,0)

,CREATED_BY VARCHAR2(30)

,CREATED_DATE DATE

,MODIFIED_BY VARCHAR2(30)

,MODIFIED_DATE DATE

)

/

CREATE TABLE COURSE

(COURSE_NO NUMBER(8,0)

,DESCRIPTION VARCHAR2(50)

,COST NUMBER(9,2)

,PREREQUISITE NUMBER(8,0)

,CREATED_BY VARCHAR2(30)

,CREATED_DATE DATE

,MODIFIED_BY VARCHAR2(30)

,MODIFIED_DATE DATE

)

/

CREATE TABLE ENROLLMENT

(STUDENT_ID NUMBER(8,0)

,SECTION_ID NUMBER(8,0)

,ENROLL_DATE DATE

,FINAL_GRADE NUMBER(3,0)

,CREATED_BY VARCHAR2(30)

,CREATED_DATE DATE

,MODIFIED_BY VARCHAR2(30)

,MODIFIED_DATE DATE

)

/

CREATE TABLE STUDENT

(STUDENT_ID NUMBER(8,0)

,SALUTATION VARCHAR2(5)

,FIRST_NAME VARCHAR2(25)

,LAST_NAME VARCHAR2(25)

,STREET_ADDRESS VARCHAR2(50)

,ZIP VARCHAR2(5)

,PHONE VARCHAR2(15)

,EMPLOYER VARCHAR2(50)

,REGISTRATION_DATE DATE

,CREATED_BY VARCHAR2(30)

,CREATED_DATE DATE

,MODIFIED_BY VARCHAR2(30)

,MODIFIED_DATE DATE

)

CREATE TABLE ZIPCODE

(ZIP VARCHAR2(5)

,CITY VARCHAR2(25)

,STATE VARCHAR2(2)

,CREATED_BY VARCHAR2(30)

,CREATED_DATE DATE

,MODIFIED_BY VARCHAR2(30)

,MODIFIED_DATE DATE

)

/

Answer 1

SELECT distinct STUDENT.STUDENT_ID, FIRST_NAME, LAST_NAME, GRADE_TYPE_CODE, NUMERIC_GRADE

FROM STUDENT JOIN ENROLLMENT ON STUDENT.STUDENT_ID = ENROLLMENT.STUDENT_ID

JOIN SECTION ON ENROLLMENT.SECTION_ID = SECTION.SECTION_ID

JOIN COURSE ON SECTION.COURSE_NO = COURSE.COURSE_NO

JOIN GRADE ON STUDENT.STUDENT_ID = GRADE.STUDENT_ID

WHERE GRADE.NUMERIC_GRADE >95 AND COURSE.DESCRIPTION = 'Advanced Java Programming';

STUDENT_ID exists in Enrollment and Student table so prefix for Table name is required to write in query so avoid ambiguity.

Group by is not required, use distinct .

Do ask if any doubt. Please upvote.