Please write a stored procedure named pHW$\_6\_$xxxx$($ student $)$ which will display the student$$s transcript by the input $($semester$,$ year$).$Your program needs to meet the following requirements and test cases.

a$.$ If the input year is $*,$ the report should have all students for all years.

b$.$ If the input semester is $*,$ the report should have all the terms in that year.

c$.$ If the input semester or year is empty or null, a corresponding error message should be printed.

d$.$ If there is no student for the input semester$/$year$,$ the output message should say $$year xxxx$,$ semester zzzz has no students in the system$.$

e$.$ If there are students found for the input semester$/$year$,$ print the report with the format based on the test cases.

f$.$ The report should be sorted by the student$$s last name, first$\_$name from A to Z$,$ year from low to high, semester from spring to fall.

g$.$ Please refer to Students, Courses, Students$\_$Courses tables that is within the CPS$3740\_2024$S database.

i$.$ Please keep the sql simple and no hard coding!

$6\mathrm{.}1$ Data entry validation for year.

$>$ call pHW$\_6\_$demo$(\text{'}\text{'},\text{'}\text{'})$;

$>$ call pHW$\_6\_$demo$(\text{'}$Fall$\text{'},$ null$)$;

$6\mathrm{.}2$ Data entry validation for semester.

$>$ call pHW$\_6\_$demo$(\text{'}\text{'},2012)$;

$>$ call pHW$\_6\_$demo$($null$,2012)$;

$6\mathrm{.}3$ The message when no students are found.

$>$ call pHW$\_6\_$demo$(\text{'}*\text{'},2030)$;

$>$ call pHW$\_6\_$demo$(\text{'}$Spring$\text{'},2013)$;

$6\mathrm{.}4$ A report shows students for a specific semester and year.

$>$ call pHW$\_6\_$demo$(\text{'}$Fall$\text{'},2014)$;

$6\mathrm{.}5$ A report shows the students for all semesters in a specific year.

$>$ call pHW$\_6\_$demo$(\text{'}*\text{'},2014)$;

$6\mathrm{.}6$ A report shows all students for all semesters and years.

$>$ call pHW$\_6\_$demo$(\text{'}*\text{'},\text{'}*\text{'})$;

Here is the information stored in the Students table within the dreamhome database

$\text{'}1007\text{'},$ 'Sarah', 'Lee', $\text{'}1995-09-15\text{'},\text{'}$IT$\text{'},\text{'}04101\text{'}$

$\text{'}1005\text{'},$ 'Helen', $\text{'}$Wu$\text{'},\text{'}1997-01-25\text{'},\text{'}$CS$\text{'},\text{'}07083\text{'}$

$\text{'}1004\text{'},$ 'Andrew', 'Lin', $\text{'}1947-08-25\text{'},$ 'Biology', $\text{'}07101\text{'}$

$\text{'}1003\text{'},$ 'Claudia', 'Lee', $\text{'}1987-03-21\text{'},$ 'Math', $\text{'}91384\text{'}$

$\text{'}1002\text{'},$ 'Mary', 'Smith', $\text{'}1976-02-08\text{'},\text{'}$IT$\text{'},\text{'}08348\text{'}$

$\text{'}1001\text{'},$ 'Austin', 'Huang', $\text{'}1990-01-01\text{'},\text{'}$CS$\text{'},\text{'}07123\text{'}$

This is describing the Courses table;

Field, Type, Null, Key, Default, Extra

'course$\_$name','varchar$(100)\text{'},$'YES',$\text{'}\text{'},$NULL,$\text{'}\text{'}$

'course$\_$id$\text{'},$'int$(11)\text{'},\text{'}$NO$\text{'},$'PRI',NULL,$\text{'}\text{'}$

'course$\_$code','varchar$(20)\text{'},$'YES',$\text{'}\text{'},$NULL,$\text{'}\text{'}$

This is what is stored in the Courses table;

course$\_$id$,$ course$\_$code, course$\_$name

$\text{'}1\text{'},\text{'}$CPS$2231\text{'},$'Java$2\text{'}$

$\text{'}2\text{'},\text{'}$CPS$2232\text{'},$'Data Structure'

$\text{'}3\text{'},\text{'}$CPS$3500\text{'},$'Web Programming'

$\text{'}4\text{'},\text{'}$CPS$3740\text{'},$'Database Introduction'

$\text{'}5\text{'},\text{'}$CPS$5920\text{'},$'Database Systems'

$\text{'}6\text{'},\text{'}$CPS$5921\text{'},$'Data Mining'

This is describing the Students$\_$Courses table;

Field, Type, Null, Key, Default, Extra

'student$\_$id$\text{'},$'int$(11)\text{'},\text{'}$NO$\text{'},$'PRI',$\text{'}0\text{'},\text{'}\text{'}$

'course$\_$id$\text{'},$'int$(11)\text{'},\text{'}$NO$\text{'},$'PRI',$\text{'}0\text{'},\text{'}\text{'}$

'semester','varchar$(20)\text{'},$'YES',$\text{'}\text{'},$NULL,$\text{'}\text{'}$

'year','varchar$(10)\text{'},$'YES',$\text{'}\text{'},$NULL,$\text{'}\text{'}$

'grade','varchar$(5)\text{'},$'YES',$\text{'}\text{'},$NULL,$\text{'}\text{'}$

This is what is stored in the Students$\_$Courses table;

Student$\_$id$,$ course$\_$id$,$ semester, year, grade

null, null, null, null

This is describing the Students table;

Field, Type, Null, Key, Default, Extra

'student$\_$id$\text{'},$'int$(11)\text{'},\text{'}$NO$\text{'},$'PRI',NULL,$\text{'}\text{'}$

'first$\_$name','varchar$(50)\text{'},$'YES',$\text{'}\text{'},$NULL,$\text{'}\text{'}$

'last$\_$name','varchar$(50)\text{'},$'YES',$\text{'}\text{'},$NULL,$\text{'}\text{'}$

This is what is stored in the Students table;

Student$\_$id$,$ first$\_$name, last$\_$name

$\text{'}1\text{'},$'Austin','Huang'

$\text{'}2\text{'},$'Claudia','Lee'

$\text{'}3\text{'},$'Sarah','Lee'

$\text{'}4\text{'},$'Andrew','Lin'

$\text{'}5\text{'},$'Helen',$\text{'}$Wu$\text{'}$

The screenshot consist of the outputs that should be executed.