Please write a stored procedure named pHW_6_xxxx( student) which will display the student's transcript by the...

  call pHW_6_demo('Fall', null); The above SQL">  

Transcribed Image Text:

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". If there are students found for the input semester/year, print the report with the format based on the test cases. 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. Please refer to Students, Courses, Students_Courses tables. e. f. g. Data entry validation for year. call pHW_6_demo(", "); > call pHW_6_demo('Fall', null); The above SQL statement should have output: I message | I Please enter a valid year. > call pHW_6_demo(", 2012); call pHW_6_demo(null, 2012); The above SQL statement should have output: +-- I message I +--- I Please enter a valid semester. 1 The message when no students are found. ) Data entry validation for semester. > call pHW_6_demo('*', 2030); > call pHW_6_demo('Spring', 2013); The above SQL statement should have output: I message 1 Year: 2030, semester: * has no students in the system. I I message 1 Year: 2013, semester: Spring has no students in the system. I A report shows students for a specific semesster and year. pHW_6_demo(Fall", 2014); > call The above SQL statement should have output: I last name I first_name I year I semester I Lee I Lee I Claudia I 2014 | Fall I Sarah I 2014 | Fall | 2014 | Fall I Lin I Andrew I Loo I Lee I Lin I Lin I Lin A report shows the students for all semesters in a specific year. > call pHW_6_demo(***, 2014); The above SQL statement should have output: I last name I first name I year 1 semester I Claudia | 2014 | I 2014 I Sarah I Andreu I 2014 | Spring I Andreu I 2014 | Spring I Andreu I 2014 Fall I 1 Mu > call pHW_6_demo("*" "*"); The above SQL statement should have output: Fall Fall I Claudia I Sarah. I Sarah I Andrew I Andreu I Andrew I Andrew I Helen I last name I first_name year I semester I cid I Austin I Huang I Huang I Austin. | 2013 | Fall I 2016 I Fall I 2017 | I Huang I Austin. I Lee I Claudia | 2014 | I Lee Claudia I I Claudia I Lee I Lee I Lee I Lee I Lin I Lin I Lin Lin A report shows all students for all semesters and years. Spring Fall I cid I CPS2231 | Java2 | CPS3748 | Database Introductio I CPS2232 | Data Structure I 2015 | Spring I 2016 | Spring I 2016 I Fall | 2014 | Fall I 2015 | Spring Fall I 2013 | I 2014 | Spring I 2014 | Spring name | 2014 | Fall I 2016 | Spring I cid I CPS2231 | Java2 I CPS3740 | Database Introductio I name I CPS2232 | Data Structure I CPS2231 | Java2 I CPS2232 | Data Structure I name 1 CPS2231 | Java2 I CPS2231 | Java2 I CPS2232 | Data Structure 1 CPS2231 | Java2 I grade I IF | A IC 1 I CPS2232 | Data Structure I CPS2231 | Java2 I grade I IF | A I B+ IA- IC I CPS2231 | Java2 I CPS2232 | Data Structure 1 CPS3500 | Web Programming | CPS2231 | Java2 IF 1 CPS2231 | Java2 IA 1 CPS5921 | Data Mining 18 I CPS5920 | Database Systems IC 1 CPS3740 | Database Introductio | A I grade I I A- I C+ I C+ IB IC I I I B+ I A- IC IA I 1 1 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". If there are students found for the input semester/year, print the report with the format based on the test cases. 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. Please refer to Students, Courses, Students_Courses tables. e. f. g. Data entry validation for year. call pHW_6_demo(", "); > call pHW_6_demo('Fall', null); The above SQL statement should have output: I message | I Please enter a valid year. > call pHW_6_demo(", 2012); call pHW_6_demo(null, 2012); The above SQL statement should have output: +-- I message I +--- I Please enter a valid semester. 1 The message when no students are found. ) Data entry validation for semester. > call pHW_6_demo('*', 2030); > call pHW_6_demo('Spring', 2013); The above SQL statement should have output: I message 1 Year: 2030, semester: * has no students in the system. I I message 1 Year: 2013, semester: Spring has no students in the system. I A report shows students for a specific semesster and year. pHW_6_demo(Fall", 2014); > call The above SQL statement should have output: I last name I first_name I year I semester I Lee I Lee I Claudia I 2014 | Fall I Sarah I 2014 | Fall | 2014 | Fall I Lin I Andrew I Loo I Lee I Lin I Lin I Lin A report shows the students for all semesters in a specific year. > call pHW_6_demo(***, 2014); The above SQL statement should have output: I last name I first name I year 1 semester I Claudia | 2014 | I 2014 I Sarah I Andreu I 2014 | Spring I Andreu I 2014 | Spring I Andreu I 2014 Fall I 1 Mu > call pHW_6_demo("*" "*"); The above SQL statement should have output: Fall Fall I Claudia I Sarah. I Sarah I Andrew I Andreu I Andrew I Andrew I Helen I last name I first_name year I semester I cid I Austin I Huang I Huang I Austin. | 2013 | Fall I 2016 I Fall I 2017 | I Huang I Austin. I Lee I Claudia | 2014 | I Lee Claudia I I Claudia I Lee I Lee I Lee I Lee I Lin I Lin I Lin Lin A report shows all students for all semesters and years. Spring Fall I cid I CPS2231 | Java2 | CPS3748 | Database Introductio I CPS2232 | Data Structure I 2015 | Spring I 2016 | Spring I 2016 I Fall | 2014 | Fall I 2015 | Spring Fall I 2013 | I 2014 | Spring I 2014 | Spring name | 2014 | Fall I 2016 | Spring I cid I CPS2231 | Java2 I CPS3740 | Database Introductio I name I CPS2232 | Data Structure I CPS2231 | Java2 I CPS2232 | Data Structure I name 1 CPS2231 | Java2 I CPS2231 | Java2 I CPS2232 | Data Structure 1 CPS2231 | Java2 I grade I IF | A IC 1 I CPS2232 | Data Structure I CPS2231 | Java2 I grade I IF | A I B+ IA- IC I CPS2231 | Java2 I CPS2232 | Data Structure 1 CPS3500 | Web Programming | CPS2231 | Java2 IF 1 CPS2231 | Java2 IA 1 CPS5921 | Data Mining 18 I CPS5920 | Database Systems IC 1 CPS3740 | Database Introductio | A I grade I I A- I C+ I C+ IB IC I I I B+ I A- IC IA I 1 1