All of them Please write stored procedures and stored functions based on the dre

Question

All of them

Please write stored procedures and stored functions based on the dreamhome database. You must create and test your programs on database CPS3740 2017S at server imc.kean.edu. Your program names must EXACTLY follow each question format. The program name is case sensitive. XXXx means your email ID in the test case examples. The test cases in each question provide as the testing SQL queries with the corresponding results. You should test your programs with different input values and make sure the results are correct. The title of your output should also match the test cases. 1. Please write a stored function named fHW2 1XXXX based on the Staff table. It should take one argument branchno and it should meet the following 2 test cases. A. (10 points) If the given branchno is 1B. i0 points) If the given branchno in the Staff table, please return the average salary of is not in the Staff table, please return a NULL value. the staff with the given branchno. mysqbr select fHw2. XXXX(B009) as output; mysql select fHw2 i XXXX(B003') as output; l output I l output I NULL I 18000 I 2. (20 points) Please write a stored procedure named pHw2 2 XXXX based on the otel, Room and Booking tables. Your program should take one argument hotelname and it should meet the following 2 test 0 points) If the given hotel name 2B. 0 points) If the given hotel name is 2A is in the Hotel table, please display the room type, empty or null, please display an error message and the number of room booked for each room "invalid hotel name type for the given hotel name. mysql call pHW2 2 XXXX(NULL) mysql call pHW2 2 XXXX('Grosvenor' mysql call pHw2 2XXXXr); type I count I I message I double 21 l Please input a valid hotel name. I I family I 1 I single I 2 I

Explanation / Answer

Here no Table schema is given for reference . Still giving answer for the first question on assumption:

CREATE PROCEDURE fHW2_1_XXXX(branchno VARCHAR(255)
BEGIN
If EXISTS(Select * from STAFF where BranchNumber = branchno)
Begin
       Select avg(SALARY) as Output
       from STAFF where BranchNumber = branchno
   END
   ELSE
   Select NULL
END

Related Questions

Navigate

• Browse (All)
• Browse A
• Subjects

---