How can I fix my stored procedure to return the average GPA across all sections that a certain professor teaches?

Databases
1

Hello Everyone,

I have been struggling for hours with trying to figure out how I can return the average GPA across all sections that a professor teaches. Here is the code that I have so far.

-- Unit 12 Step 4

-- Create a Stored Procedure named Faculty_GPA to find the AverageGPA of students across all
-- the sections that the professor teaches. 

  USE COLLEGE; 
  
  DROP PROCEDURE IF EXISTS Faculty_GPA ;

DELIMITER $$

CREATE PROCEDURE Faculty_GPA(IN FacultyID INT,  OUT outavgGPA decimal(4, 2))

BEGIN
         DECLARE theGPAInfo DECIMAL(4,2); 
         
		 SET theGPAInfo= 
         (SELECT AVG(registration.Grade)
			FROM 
            Registration
            INNER JOIN 
		     Student ON registration.ID= Student.ID
             INNER JOIN 
             Section ON Student.ID= Section.ID
             WHERE section.ID= 2 AND section.ID= 3);
		
     SET outavgGPA= theGPAInfo; 
END
$$

DELIMITER ;


CALL Faculty_GPA(2, @averageGPA); 


SELECT @averageGPA as GPA; 

--
2

a single value can never be equal to two different values

i think you want OR instead of AND

3

and that’s only going to return rows by coincidence – an incorrect coincidence

4

@r937,
How can I solve this problem?

5

which one, the second one? join on the correct columns

6

@r937,
No, I mind return the average GPA for all the sections a professor( who has a Faculty ID of 2) teaches students in. I know if I run this select statement below it will tell me RegistrationID, StudentID, SectionID, Registration.Grade, but I am just not sure how I can run a query to output the averageGPA of the sections the professor teaches students in order to help solve my bigger problem of working with the procedure I created above. I always try to see what information is stored in each table.

SELECT * FROM registration;
7

okay, let’s start there

which table contains the professor with faculty id of 2?

8

@r937,

The Faculty table contains that.

9

and how is the Faculty table related to your other tables?

10

@r937,

Here is a ERD Model.

1009Ă—616 367 KB

11

making me do all the work, eh? we’ll see about that… :wink:

okay, you won’t need the Faculty table because you already have the faculty id

you will need only the Section and Registration tables

can you try so set up a query which joins those two tables?

12

@r937,

Here is the joining of the two tables named Section and Registration, but I still have trouble in my query with trying to match the sections that the professor taught with what students registered for his sections. How could I do that? The code and screenshot of the result grid of what I have so far. Here you can see that the professor taught sections 2 and 3 because it corresponds to his ID, but what you don’t see is the students’ registered sections corresponding to the sections he taught. I want to know all the students he taught in his sections and what grade they received. Here is code now to achieve the desired output you see. Here is SQL text code below that.

USE COLLEGE; 

SELECT 
Section.ID AS 'Section ID', 
Section.Name AS 'Section Name', 
Section.TaughtByID AS 'Section Taught By',
Registration.StudentID AS 'Student ID', 
Registration.SectionID AS 'Student Registered for this section', 
Registration.Grade AS 'Grade' 
FROM 
Section 
INNER JOIN 
Registration ON Section.ID= Registration.ID
WHERE Section.TaughtByID= 2;
13

think about this join condition, and keep thinking about it until you see why it’s wrong

14

@r937,
Is this wrong because you should never select on ID’s when INNER joining tables?

15

no, it’s wrong because you’re joining on the wrong columns

16

@r937,

How can I join on the right columns? I know I would need to show the section IDs in which he taught students in and all the students that were in those same sections, but I am just not understanding as you probably can tell from the work I displayed. Can you give me a hint as to what specific columns should be in this query?

17

actually, no, you wouldn’t… you would want to show the average across all sections

but forget about what you want to show (the SELECT clause)

always start your thinking with the FROM clause

you need those two tables, and you need to ~match~ the rows based on some value that they have in common

18

@r937,

I figured out why the query was wrong, but I am still not understanding how to return the AVG GPA across all sections. Here is the code I have fixed. I know it’s still not right but I am trying. How can I use the IN parameter of FacultyID inside of my SELECT statement?

-- Create a Stored Procedure named Faculty_GPA to find the AverageGPA of students across all
--  sections that the professor teaches. 

  USE COLLEGE; 
  
  DROP PROCEDURE IF EXISTS Faculty_GPA ;

DELIMITER $$

CREATE PROCEDURE Faculty_GPA(IN FacultyID INT, OUT outavgGPA decimal(4, 2))

BEGIN
         DECLARE theGPAInfo DECIMAL(4,2); 
         
		 SET theGPAInfo= 
         (SELECT AVG(registration.Grade)
			FROM 
            Registration
            INNER JOIN 
            Section ON Registration.SectionID= Section.ID
            WHERE registration.Grade > 0); 
            
		
     SET outavgGPA= theGPAInfo; 
END $$

DELIMITER ;


CALL Faculty_GPA(2, @averageGPA); 



SELECT @averageGPA as GPA;
19

do me a favour, please – get your query working first, ~before~ trying to embed it into a stored proc

20

@r937,
The code that I have does return the AVG gpa but I don’t think it’s the right avg GPA for sections he taught. What I am missing or not doing right? How can I specify in my select statement only the sections he taught students in order to get avg gpa? Basically if I get this to work I can just copy this SQL query into the stored procedure, right? Here is the code:


USE COLLEGE; 

SELECT 
AVG(Registration.Grade) 
FROM 
Registration 
INNER JOIN 
Section ON Registration.SectionID= Section.ID
WHERE Registration.Grade > 0;