Question

STORE (StoreID, StoreAddress, WeekDaysHours, WeekendHours)

ACCOUNT (AccountNo., AccountName, Balance)

DEPARTMENT (DepartmentID, DepartmentTitle, NoOfEmployees, AccountNo, 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)

SHAREHOLDER (S.H.ID, Name, Phone, Email, Address, NoOfShares, MemberID)

TEAM (TeamID, TeamName, TeamLeaderID)

MEMBERSHIP (MemberID, TeamID, PercentageOfShare)

Question: (SQL Using ORACLE SQL*Plus)
1. List the team member details (member id and team id) 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.

Answer 1

In SQL we use various commands to extract data from tables in efficient way. To find the maximum value of any particular I'd we use max command.

For example to find the average and salary for particular departments we use

BREAK ON DEPARTMENT_NAME SKIP 1
COMPUTE AVG LABEL 'Dept Average' -
        MAX LABEL 'Dept Maximum' -
        OF SALARY ON DEPARTMENT_NAME
SELECT DEPARTMENT_NAME, LAST_NAME, SALARY
FROM EMP_DETAILS_VIEW
WHERE DEPARTMENT_NAME IN ('Executive', 'Accounting')
ORDER BY DEPARTMENT_NAME;

Thus by using this command we can get the details