Question
STORE (StoreID, StoreAddress, WeekDaysHours, WeekendHours) ACCOUNT (AccountNo., AccountName, Balance) DEPARTMENT (DepartmentID, DepartmentTitle, NoOfEmployees, AccountNo, StoreID, FranchiseTeamID) WEEKLY_SALES_REPORT (ReportID, StartDate, EndDate, SaleAmount, ComRate, ComAmount, BrandID, BName,
STORE (StoreID, StoreAddress, WeekDaysHours, WeekendHours)
ACCOUNT (AccountNo., AccountName, Balance)
DEPARTMENT (DepartmentID, DepartmentTitle, NoOfEmployees, AccountNo, StoreID, FranchiseTeamID)
WEEKLY_SALES_REPORT (ReportID, StartDate, EndDate, SaleAmount, ComRate, ComAmount, BrandID, BName, BSaleAmount, TypeID, TName, TSaleAmount, EmployeeID, Ename, ESaleAmount, EBonus, DepartmentID)
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)
SHAREHOLDER (S.H.ID, Name, Phone, Email, Address, NoOfShares, MemberID)
TEAM (TeamID, TeamName, TeamLeaderID)
MEMBERSHIP (MemberID, TeamID, PercentageOfShare)
Using the tables provided above, provide SQL statements for the following queries.
a. Show the store information along with its departments for all stores that opens the latest during weekend.
b. Show the name and address of all team members for store S001.
c. List the franchise application details for each department of H2H along with the department id and title. Include the departments that have not received any application yet
d. Show the team information and number of members for all successful franchise application.
e. Show the details of the franchise application that took the longest time to reach a decision
f. List the team member details (including shareholder and employee details such as id, name, phone and email) for all the team members who are part of a franchise team of a successful application. For each of the members, also include the date on which their franchise application was granted.
g. List the team member details (shareholder/employee details) who has the maximum credit to his name, based on his percentage of share in the team and the positive account balance of the franchising department. (Hint: account balance can be found in the Account table)
h. Show the top 4 employees (name, salary earned) that have earned the highest salary from January 2017 December 2017. Assume that all casual employees work 12 hours per week and there are 45 working weeks a year.
Step by Step Solution
There are 3 Steps involved in it
Step: 1
Get Instant Access to Expert-Tailored Solutions
See step-by-step solutions with expert insights and AI powered tools for academic success
Step: 2
Step: 3
Ace Your Homework with AI
Get the answers you need in no time with our AI-driven, step-by-step assistance
Get Started