Please do the question correctly, do not copy from the other answers. Correct answer will be highly appreciated. Do not use JOIN or ON in the query.

Thank you.

The list of tables available for this assignment is the following: STORE (StoreID, StoreAddress, WeekDayshours, WeekendHours) ACCOUNT (AccountNo., AccountName, Balance) DEPARTMENT (DepartmentID, DepartmentTitle, NoofEmployees, AccountN StoreID, FranchiseTeamID) FRANCHISE_APP (AppNumber, AppDate, DecisionDate, Status, DepartmentID, TeamID) EMPLOYEE (EmployeeID, Name, Phone, Email, Address, Gender, DOB, JoiningDate, CentralorLocalEmployment, EFTPOSID, CANumber, DepartmentID) CASUAL EMPLOYEE (EmployeeID, HourlyRate) PART_TIME_EMPLOYEE (EmployeeID, WeeklyHours, Salary) FULL_TIME_EMPLOYEE (EmployeeID, LeaveDays, Salary, MemberID) TEAM MEMBER (MemberID) SHARHOLDER (S.H.ID, Name, Phone, Email, Address, NoOfShares, MemberID) TEAM (TeamID, TeamName, TeamLeaderID) MEMBERSHIP (MemberID, TeamID, Percentageofshare) NOTE: PK is printed underlined and FK is printed italic in italics. The tasks are listed in the next page. Task 2 [35 marks] Provide the implementation of the following stored procedures and function. For submission, please include both the PL/SQL code and an execute procedure/SQL statement to demonstrate the functionality. a. Write a procedure that takes an employee id as argument and lists the details of an employee's department and reporting boss. Note that an employee's reporting boss is the person who has the highest percentage of share in the franchise team. The reporting boss can be either a full-time employee or a shareholder. If the reporting boss is a shareholder, display number of shares along with contact details and if the boss is an employee, mention DOB, gender and joining date along with the contact information. Hint: First, try to identify the member id of the boss. Then, using that member id, check whether the member is a shareholder or full-time employee. [20 marks] b. Write a stored function that takes an employee id as input and returns how long (in years) an employee has been a part of the company. For convenience, we have provided an attribute in the employee table called 'joiningDate'. Use the function to write an sql query that lists down all the employees and their associated duration of employment. [15 marks]