Question: Write a stored function named fHW _ 9 _ xxxx ( student _ id , year ) that calculates and returns the total tuition fees

Write a stored function named fHW_9_xxxx(student_id, year) that calculates and returns the total tuition fees a student has incurred for a specific year based on their enrolled courses. The function should meet the following requirements
Case 1
If the student_id is negative or NULL, return an error message Please enter a valid student_id.> select fHW_9_xxxx(null,2020) as output;> select fHW_9_xxxx(-1,2020) as output;
case2
If the year is negative or NULL, return an error message Please enter a valid year.> select fHW_9_xxxx(1007, null) as output;> select fHW_9_xxxx(1007,-1) as output;
Case3
If both the student_id and year are 0, return the message with the total payment for all students across all years and semesters.> select fHW_9_xxxx(0,0) as output;
Case4
If the student_id is not in the system, regardless of whether the year is 0 or a specific year, return the message: 'Student_id zzzz is not in the system.'> select fHW_9_xxxx(2010,2017) as output;
case5
If student_id is in the system and the year is 0, return the message with total payment for the given student_id across all years.> select fHW_9_xxxx(1007,0) as output;
Case6
If student_id is in the system and the year is 1, return the message with the payment for the given student_id for each year.> select fHW_9_xxxx(1007,1) as output;
Case7
If the student_id is 0 or in the system and year is NOT in the system, return the message: 'No student founds for year yyyy is in the system'.> select fHW_9_xxxx(0,2200) as output;
Case8
If the student_id is 0 and year is the system, return the total payment for the given year across all students.> select fHW_9_xxxx(0,2017) as output;
Case9
If the student_id is 1 and year is is in the system, return the message with the total payment for the given year for each student.> select fHW_9_xxxx(1,2017) as output;
Case10
If the student_id and year are is in the system, but student_id not enrolled in the give year, return the message Student ID: zzzz not enrolled in year yyyy.> select fHW_9_xxxx(1007,2017) as output;
Case11
If the student_id and year are is in the system and student_id enrolled in the give year, return the message with the total payment for the given student_id and year.> select fHW_9_xxxx(1004,2014) as output;

Step by Step Solution

There are 3 Steps involved in it

1 Expert Approved Answer
Step: 1 Unlock blur-text-image
Question Has Been Solved by an Expert!

Get step-by-step solutions from verified subject matter experts

Step: 2 Unlock
Step: 3 Unlock

Students Have Also Explored These Related Programming Questions!