write a stored function named fHW_10_xxxx () which will return the total payment amount for a given student ID and a year. Please refer to Students, Courses, Students_Courses tables. Assume each credit is $100.

TECH3740/2021 Fall Homework Name:__________________  Section 01 due date: 11:59pm 12/1/2021. Section 04 due date: 11:59 pm 12/6/2021  Total 120 points. Late penalty: 20 points off/week.  No resubmission after receiving the score. Please indicate your complete status on the Blackboard.  Please note that xxxx is your Kean email ID in this homework.  Please create your tables, views and stored routines under TECH3740_2021F database.  Please refer dreamhome database for questions 3 – 10.  Note: The example output is for reference only. The example output might have the different values than real database. DO NOT hard code the values, all the values should be retrieved/calculated from the database. Part I: Please evaluate, analyze and answer the following questions. 1. _ (15 pts) Create one new table Courses_xxxx. Your table names must follow the above format (table name is case sensitive) with following fields in correct data types. This table will be used in your project.  cid: auto_increment integer, primary key (2 pt)  name: varchar(200), unique, NOT NULL (2 pt)  term: varchar(200), NOT NULL (1 pt)  enrollment: int, NOT NULL (1 pt)  Fid: int, NOT NULL, a foreign key to TECH3740.Faculty table. (3 pts)  Rid: int, NOT NULL, a foreign key to TECH3740.Rooms table. (3 pts)  aid: int, NOT NULL, a foreign key to TECH3740.Admin table. (3 pts)

  1. _ (10 pts) insert 5 records into your Courses_xxxx table The fid, rid, aid should not be all the same among the 5 records. The enrollment number should be less than the room size. 3. _ (5 pts) Write a view named vHW_3_XXXX to show the total room number for each hotel that has 3 or more rooms. The output columns should be hotelname, number_of_rooms and it should be sorted based on the total room number from high to low. You should use the Hotel and Room tables in dreamhome database. Here is a sample output. +———–+—————–+ | hotelname | number_of_rooms | +———–+—————–+ | Latham | 3 | | Grosvenor | 3 | +———–+—————–+
  2. _ (5 pts) Write a view named vHW_4_XXXX to list the hotel name, total rooms in the hotel, total revenue if all rooms were booked for the hotel, and the revenue is higher than 85, also the hotels are in London. Your view header names should be: hotelname, count, revenue. You should use Hotel and Room tables in dreamhome database. Below is a sample of the output. +—————+——-+———+ | hotelname | count | revenue | +—————+——-+———+ | Omni Shoreham | 2 | 89.98 | | Grosvenor | 3 | 87.00 | +—————+——-+———+ 5. _ (5 pts) Create a view vHW_5_xxxx to list the project name, staff name and the staff’s branch city for the same staff who working on the same project more than one time. You should use Project, Staff2, Branch, and Working tables. The output column header should be (Project_name, Staff_name, Branch_city). The staff_name should combine fname and lname with a space in between. Below is a sample output: +—————–+————–+————-+ | Project_name | Staff_name | Branch_city | +—————–+————–+————-+ | Kean University | New Engineer | London | | Citi Bank | Susan Brand | Glasgow | | British Museum | Super User | London | +—————–+————–+————-+
    Page 2 of 3 6. (10 pts) Create a view vHW_6_xxxx to show the employee who has the lowest salary for each gender. You must use UNION operation and Staff2 table in the dreamhome database. The output column name should be (gender, name, age, salary). The name should be combined fname and lname, with a space in between. The output should be female first and then male. Below is a sample output: +——+————+——+———+ | sex | name | age | salary | +——+————+——+———+ | F | Mary Howe | 51 | 9000.00 | | F | Julie Lee | 56 | 9000.00 | | M | New Tester | 31 | 8000.00 | +——+————+——+———+
  3. (10 points) Create a view vHW_7_xxxx listing staff’s names, salary, ages, and their supervisors’ names, salary, and ages for all staff whose supervisor’s age is older than the staff’s age and their supervisor’s salary is higher than the staff’s salary. You should use Staff2 table. The output header needs to distinguish between staff and manger: (E_name, E_position, E_salary, E_age, M_name, M_position, M_salary, M_age). Below is a sample output. +———+———-+——-+———+———-+——-+ | E_fName | E_salary | E_age | M_fName | M_salary | M_age | +———+———-+——-+———+———-+——-+ | Susan | 24000.00 | 81 | John | 30000.00 | 75 | +———+———-+——-+———+———-+——-+
    Part II: Write Stored Routines. Grading criteria is based on the test cases for each question. xxxx is your database login ID. The email ID of the testing statements is demo.
  4. (20 points) Please write a stored procedure named pHW_8_xxxx( city ) which will display the student’s transcript by a student’s first_name and last_name. The transcript should have the student’s first_name, last_name, all courses ID, course names and grades at which year/semester. Please refer to Students, Courses, Students_Courses tables.
    8.1 (5 pts) > call pHW_8_demo(‘Austin’, ‘Huang’); The above SQL statement should have output:
    8.2 (5 pts) > call pHW_8_demo(‘Tim’, ‘Smith’); The above SQL statement should have output:
    8.3 (5 pts) > call pHW_8_demo(null,’Smith’); The above statement should have output:
    8.4 (5 pts) > call pHW_8_demo(‘Tim’,”); The above statement should have output:
  5. (20 points) Please write a stored function named fHW_9_xxxx() which will return the course id that the course name contain a given parameter pattern string. You must use LIKE for the pattern match. Please refer to Courses table. 9.1 (5 pts) > select fHW_9_demo(‘Z’) as output; The above SQL statement will return 9.3 (5 pts) > select fHW_9_demo(”) as output; The above SQL statement will return
    Page 3 of 3
    9.2 (5 pts) > select fHW_9_demo(‘M’) as output; The above SQL statement will return.
    9.4 (5 pts) > select fHW_9_demo(null) as output; The above SQL statement will return
  6. (20 points) Please write a stored function named fHW_10_xxxx () which will return the total payment amount for a given student ID and a year. Please refer to Students, Courses, Students_Courses tables. Assume each credit is $100.
    10.1(5 pts) > select fHW_10_demo(2000,1001) as output; The above SQL statement will return
    10.2 (5 pts) > select fHW_10_demo(2016,1001) as output; The above SQL statement will return.
    10.3 (5 pts) > select fHW_10_demo(”, 1002) as output; The above SQL statement will return
    10.4 (5 pts) > select fHW_10_demo(2017, null) as output; The above SQL statement will return
× How can I help you?