The programming test requires you to build your database from the attached zipped file. Build a new schema in a MySQL environment. (Name your schema like finalp2). Download the attached zipped file...

i had upload sql and question please look at it


The programming test requires you to build your database from the attached zipped file. Build a new schema in a MySQL environment. (Name your schema like finalp2). Download the attached zipped file and uncompress. CIS495FinalPart2Build.zip Using MySQLWorkbench, File Run SQL. Stored Procedure Name: createHotel part1. Stored Procedure Name: createHotel PARAMETERS: IN inHotelName varchar(40), IN inHotelAddress varchar(40), IN inHotelCity varchar(40), OUT outHotelNo INT, OUT outCreateHotelResult char(40) Logic: 1. Get the last hotelno and save it. 2. Create a new hotelno that is 1 more than the last hotelno. 3. Insert a new row in hotel using the IN parameters. 4. Insert one hotel name "BMCC Hotel" with address as "199 Chambers Street" and city as "New York". 5. Return the new hotelno to the out parameter. 6. Return "New Hotel Inserted." in the out parameter. 7. Zip your stored procedure and upload part2 Code and test addHotelRoom() stored procedure Specifications: Stored Procedure Name: addHotelHoom PARAMETERS: IN inHotelNo DEC(10,0), IN inRoomNo DEC(10,0), IN inRoomType char(20), IN inRoomPrice DEC(16,2), OUT outAddHotelRoomResult char(40) Variables to declare: DECLARE errorno INT; DECLARE currentHotelNo DEC(10,0); Logic: 1. Check that the inHotelNo is valid using this SQL: SELECT HOTELNO INTO currentHotelNo FROM HOTEL where hotelno = inHotelNo. 2. Code an EXIT HANDLER FOR NOT FOUND, returning "Hotel not found" to outAddHotelRoomResult OUT parameter. 3. Insert a new row in room using the IN parameters. 4. Run the stored procedure multiple times inserting 5 roomnos: 1,2,3,4,5 with roomtype='Single' and roomprice = 200.00. Make sure you pass also the correct hotelno (the new one you created). 5. Check that 5 rooms are inserted in room table. 6. Return "New Hotel room inserted." in the out parameter. 7. Zip your stored procedure and upload