Find the minimum, maximum, and average grade of final exams in sections taught b

Question

Find the minimum, maximum, and average grade of final exams in sections taught by Todd Smythe. I got a very complicated nested subquery thing going that will find his sections but whatever I am doing is not letting me just add the request for the grades. What should be the sql code to solve this?

Tables in the database:

Name           Null     Type        

-------------- -------- ------------Instructor table

INSTRUCTOR_ID NOT NULL NUMBER(8)   

SALUTATION              VARCHAR2(5)

FIRST_NAME              VARCHAR2(25)

LAST_NAME               VARCHAR2(25)

STREET_ADDRESS          VARCHAR2(50)

ZIP                     VARCHAR2(5)

PHONE                   VARCHAR2(15)

CREATED_BY     NOT NULL VARCHAR2(30)

CREATED_DATE   NOT NULL DATE        

MODIFIED_BY    NOT NULL VARCHAR2(30)

MODIFIED_DATE NOT NULL DATE        

Name              Null     Type        

----------------- -------- ------------STUDENT TABLE

STUDENT_ID        NOT NULL NUMBER(8)   

SALUTATION                 VARCHAR2(5)

FIRST_NAME                 VARCHAR2(25)

LAST_NAME         NOT NULL VARCHAR2(25)

STREET_ADDRESS             VARCHAR2(50)

ZIP               NOT NULL VARCHAR2(5)

PHONE                      VARCHAR2(15)

EMPLOYER                   VARCHAR2(50)

REGISTRATION_DATE NOT NULL DATE        

CREATED_BY        NOT NULL VARCHAR2(30)

CREATED_DATE      NOT NULL DATE        

MODIFIED_BY       NOT NULL VARCHAR2(30)

MODIFIED_DATE     NOT NULL DATE        

Name          Null     Type        

------------- -------- ------------ZIPCODE TABLE

ZIP           NOT NULL VARCHAR2(5)

CITY                   VARCHAR2(25)

STATE                  VARCHAR2(2)

CREATED_BY    NOT NULL VARCHAR2(30)

CREATED_DATE NOT NULL DATE        

MODIFIED_BY   NOT NULL VARCHAR2(30)

MODIFIED_DATE NOT NULL DATE        

Name          Null     Type        

------------- -------- ------------ENROLLMENT TABLE

STUDENT_ID    NOT NULL NUMBER(8)   

SECTION_ID    NOT NULL NUMBER(8)   

ENROLL_DATE   NOT NULL DATE        

FINAL_GRADE            NUMBER(3)   

CREATED_BY    NOT NULL VARCHAR2(30)

CREATED_DATE NOT NULL DATE        

MODIFIED_BY   NOT NULL VARCHAR2(30)

MODIFIED_DATE NOT NULL DATE        

Name                  Null     Type          

--------------------- -------- --------------GRADE TABLE

STUDENT_ID            NOT NULL NUMBER(8)     

SECTION_ID            NOT NULL NUMBER(8)     

GRADE_TYPE_CODE       NOT NULL CHAR(2)       

GRADE_CODE_OCCURRENCE NOT NULL NUMBER(38)    

NUMERIC_GRADE         NOT NULL NUMBER(3)     

COMMENTS                       VARCHAR2(2000)

CREATED_BY            NOT NULL VARCHAR2(30)  

CREATED_DATE          NOT NULL DATE          

MODIFIED_BY           NOT NULL VARCHAR2(30)  

MODIFIED_DATE         NOT NULL DATE          

Name          Null     Type        

------------- -------- ------------GRADE_CONVERSION TABLE

LETTER_GRADE NOT NULL VARCHAR2(2)

GRADE_POINT   NOT NULL NUMBER(3,2)

MAX_GRADE     NOT NULL NUMBER(3)   

MIN_GRADE     NOT NULL NUMBER(3)   

CREATED_BY    NOT NULL VARCHAR2(30)

CREATED_DATE NOT NULL DATE        

MODIFIED_BY   NOT NULL VARCHAR2(30)

MODIFIED_DATE NOT NULL DATE        

Name            Null     Type        

--------------- -------- ------------GRADE_TYPE TABLE

GRADE_TYPE_CODE NOT NULL CHAR(2)     

DESCRIPTION     NOT NULL VARCHAR2(50)

CREATED_BY      NOT NULL VARCHAR2(30)

CREATED_DATE    NOT NULL DATE        

MODIFIED_BY     NOT NULL VARCHAR2(30)

MODIFIED_DATE   NOT NULL DATE        

Name                   Null     Type        

---------------------- -------- ------------GRADE_TYPE_WEIGHT

SECTION_ID             NOT NULL NUMBER(8)   

GRADE_TYPE_CODE        NOT NULL CHAR(2)     

NUMBER_PER_SECTION     NOT NULL NUMBER(3)   

PERCENT_OF_FINAL_GRADE NOT NULL NUMBER(3)   

DROP_LOWEST            NOT NULL CHAR(1)     

CREATED_BY             NOT NULL VARCHAR2(30)

CREATED_DATE           NOT NULL DATE        

MODIFIED_BY            NOT NULL VARCHAR2(30)

MODIFIED_DATE          NOT NULL DATE        

Name            Null     Type        

--------------- -------- ------------SECTION TABLE

SECTION_ID      NOT NULL NUMBER(8)   

COURSE_NO       NOT NULL NUMBER(8)   

SECTION_NO      NOT NULL NUMBER(3)   

START_DATE_TIME          DATE        

LOCATION                 VARCHAR2(50)

INSTRUCTOR_ID   NOT NULL NUMBER(8)   

CAPACITY                 NUMBER(3)   

CREATED_BY      NOT NULL VARCHAR2(30)

CREATED_DATE    NOT NULL DATE        

MODIFIED_BY     NOT NULL VARCHAR2(30)

MODIFIED_DATE   NOT NULL DATE        

Name          Null     Type        

------------- -------- ------------COURSE TABLE

COURSE_NO     NOT NULL NUMBER(8)   

DESCRIPTION   NOT NULL VARCHAR2(50)

COST                   NUMBER(9,2)

PREREQUISITE           NUMBER(8)   

CREATED_BY    NOT NULL VARCHAR2(30)

CREATED_DATE NOT NULL DATE        

MODIFIED_BY   NOT NULL VARCHAR2(30)

MODIFIED_DATE NOT NULL DATE        

Explanation / Answer

Herewith I have added 2 queries,

1. below query retrieves the aggregation of the Grade from GRADE table.

Select s.Section_No,Min(g.Numeric_Grate) as Minimum, Max(g.Numeric_Grate) as Maximum,avg(g.Numeric_Grate) as Average

From Section s

Inner Join Instructor i

on i.Instructor_ID=s.Instructor_ID

Inner Join Grade g

on g.Section_ID = s.Section_ID

Where i.First_Name like 'Todd' and Last_Name='Smythe'

Group by s.Section

2. below query retrieves the aggregation of the Grade from ENROLLMENT table using Final_Grade value.

Select s.Section_No,Min(e.Final_Grate) as Minimum, Max(e.Final_Grate) as Maximum,avg(e.Final_Grate) as Average

From Enrollment e

Inner Join Section s

on e.Section_ID=s.Section_ID

Inner Join Instructor i

on i.Instructor_ID=s.Instructor_ID

Where i.First_Name like 'Todd' and Last_Name='Smythe'

Group by e.Final_Grate

Please comments

Related Questions

Navigate

• Browse (All)
• Browse F
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.