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