Please write a stored procedure which will display the student’s transcript by the input (semester, year). The program needs to meet the following requirements and test cases..If the input year is “*”, the report should have all students for all years. If the input semester is “*”, the report should have all the terms in that year. If the input semester or year is empty or null, a corresponding error message should be printed. 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, and semester from spring to fall. MariaDB [dreamhome]> Select * from Students;| sid| first_name1001 | Austin1002 Mary1003ClaudiaLee1004 | AndrewLin1005 | HelenWu1007 Sarah| Lee6 rows in set (0.010 sec)MariaDB [dreamhome]> Select * from Courses;| cid| name| credits || last_name | birthday | major| HuangSmith1990-01-01 CS1976-02-08 | IT1987-03-21 | Math1947-08-25 | Biology1997-01-25 | CS1995-09-15 | ITJava1Java2| cidCPS1231CPS2231CPS2232 Data StructureCPS3500 Web ProgrammingCPS3740 | Database IntroductioCPS5920 Database Systems| CPS5921 | Data Mining7 rows in set (0.014 sec)MariaDB [dreamhome]> Select * from| sid1001CPS2231 2013 Fall1003 CPS2231 2015 | Spring1004 CPS2231 2014 | Spring1001CPS2232 2016 Fall1004CPS2232 20141005CPS2231 20161007CPS223120151003CPS5920 20161007CPS374020142016 | Spring2017 | Spring1003 CPS59211001 CPS35001003 | CPS2231 2014 Fall1004 CPS2232 2014 Fall| 1004 | CPS2231 | 2013 | Fall14 rows in set (0.001 sec)| year | semester | grade || A-SpringSpringSpringFallFallStudents_Courses;<< < ± <_UARTLOU| AA-| C+B+| B| C| A44433 |33 || C+| F| CC| zipcode|071230834891384071010708304101

Procedures can be defined in such a way that they are subroutines that can contain one or more SQL…

Answered: lease write a stored procedure which…

Database System Concepts

7th Edition

ISBN:9780078022159

Author:Abraham Silberschatz Professor, Henry F. Korth, S. Sudarshan

Publisher:Abraham Silberschatz Professor, Henry F. Korth, S. Sudarshan

Chapter1: Introduction

Section: Chapter Questions

Problem 1PE

See similar textbooks

Related questions

Concept explainers

Question

Please write a stored procedure which will display the student’s transcript by the input (semester, year). The program needs to meet the following requirements and test cases.

.If the input year is “*”, the report should have all students for all years.

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

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

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, and semester from spring to fall.

MariaDB [dreamhome]> Select * from Students;
| sid
| first_name
1001 | Austin
1002 Mary
1003
Claudia
Lee
1004 | Andrew
Lin
1005 | Helen
Wu
1007 Sarah
| Lee
6 rows in set (0.010 sec)
MariaDB [dreamhome]> Select * from Courses;
| cid
| name
| credits |
| last_name | birthday | major
| Huang
Smith
1990-01-01 CS
1976-02-08 | IT
1987-03-21 | Math
1947-08-25 | Biology
1997-01-25 | CS
1995-09-15 | IT
Java1
Java2
| cid
CPS1231
CPS2231
CPS2232 Data Structure
CPS3500 Web Programming
CPS3740 | Database Introductio
CPS5920 Database Systems
| CPS5921 | Data Mining
7 rows in set (0.014 sec)
MariaDB [dreamhome]> Select * from
| sid
1001
CPS2231 2013 Fall
1003 CPS2231 2015 | Spring
1004 CPS2231 2014 | Spring
1001
CPS2232 2016 Fall
1004
CPS2232 2014
1005
CPS2231 2016
1007
CPS2231
2015
1003
CPS5920 2016
1007
CPS3740
2014
2016 | Spring
2017 | Spring
1003 CPS5921
1001 CPS3500
1003 | CPS2231 2014 Fall
1004 CPS2232 2014 Fall
| 1004 | CPS2231 | 2013 | Fall
14 rows in set (0.001 sec)
| year | semester | grade |
| A-
Spring
Spring
Spring
Fall
Fall
Students_Courses;
<< < ± <_UARTLOU
| A
A-
| C+
B+
| B
| C
| A
4
4
4
3
3 |
3
3 |
| C+
| F
| C
C
| zipcode
|
07123
08348
91384
07101
07083
04101

Expert Solution

Step by step

Solved in 3 steps

SEE SOLUTION Check out a sample Q&A here

Knowledge Booster

Learn more about

Need a deep-dive on the concept behind this application? Look no further. Learn more about this topic, computer-science and related others by exploring similar questions and additional content below.

Similar questions