Question

Please write a stored function named fHW_8_xxxx () which takes two inputs (year, sid) If the inputs are valid,

a. If the year is empty or null, return an error message "Please input a valid year."
b. If the sid is empyt or null, return an error message "Please input a valid student id."
c. If both inputs are valid, return the total payment amount for a given student ID and a year.
d. If input sid is "*", return the total payment amount for all students for the input year.
C. If input year is "*", return the total payment amount for the given student ID for all years.
f. If no record found for the given year and sid, return a message "No record found for student id: zzzz at year: yyyy." where zzz is the input sid and yyyy is the input year.
g. Please refer to Students, Courses, Students Courses tables. Assume each credit is $100.
i) Input validation for year.
>select fHW 8 demo(", 1002) as output;
>select fHW 8 demo(null, 1002) as output;
The above SQL statement will retum
output
Please input a valid year.

Answer 1

To solve this problem, you can create a stored function in a database that takes two inputs: year and sid. The function should have several conditions to handle different scenarios.

Step-by-step explanation:

To solve this problem, you can create a stored function in a database that takes two inputs: year and sid. The function should have several conditions to handle different scenarios:

1. If the year is empty or null, return the error message 'Please input a valid year.'
2. If the sid is empty or null, return the error message 'Please input a valid student id.'
3. If both inputs are valid, retrieve the total payment amount for the given student ID and year from the database.
4. If the input sid is '*', retrieve the total payment amount for all students for the input year.
5. If the input year is '*', retrieve the total payment amount for the given student ID for all years.
6. If no record is found for the given year and sid, return the message 'No record found for student id: zzzz at year: yyyy.'

Make sure to refer to the Students, Courses, and Students Courses tables for the required data. Assume each credit is $100.