SQL SERVER Questions

Please use the following database script: https://codeshare.io/GAvx78

Answer the following questions

Create HR database using HRDB script. 1) Using HR database, answer the following four questions: (10 points) A: Write a script that creates a database role named HR_Role in the HR Database. (2 points) B: Assign HR_Role role you created in step 1A: SELECT and UPDATE privileges for the Jobtitle Table and SELECT , UPDATE DELETE and INSERT privileges for EMPLOYEE table and this HR_role should also have right to create session. (4 points) C: Write script that creates two users named john with the password of john and Jennet with the password of Janet and assign them HR_Role.(2 points) D: Change the password of Janet user to jannet1.(2 points) */

/* 2. Using HR database,Create oracle package that contains the following store procedures and user defined functions. (40 pts) (Package Name: EMPLOYEEPACKAGE) (4) */

/* A. Store procedure that adds employee record into employee table (Name store procedure: UDP_EMPLOYEE_HIRE) (9 points) */

/* C. Store procedure that updates employees salary by percentage using Job Title Id and percentage as parameters (Name store procedure: UDP_EMPLOYEE_RAISE_SALARY)(9 points) */ ASSIGN UPDATE PRIVILEGES TO JOHN;

/* D. User defined function that returns the employee years of service using employee Id as parameter of the function. (Name user defined function: UDF_EMPLOYEE_YEARS_OF_SERVICE)(9 points) */

/* E. User defined function that returns retirement date of employee using employee ID as parameter (assume the retirement age is 65 years) (Name user defined function: UDF_EMPLOYEE_RETIREMENT)(9 points) */