how i can do this SQL Query ??

Question

hi

i have

• Employees Table (Eno, FName, Lname, DOB, Tell, Address, Salary, Pno, Bno)

//Clients Table represents all data about a client

// CID is number of client

• Clients Table(CID, FName, LName, Address, Tell, Cbalance,...)

// Movement Table represents all tasks the client can do // MID is number of movement // M_type is type of task

• Movement Table (MID, M_type) // M_type as Depositing , Withdrawing.....

// Working Table is stor all active did from any client

• Working Table (WID, CID, C_value, MID, DOBmovement, Eno)

how i can :

1. Make query to retrieve all data about the employee who had served the maximum number of clients .

2. in the end of each month your balance may be changed by deposit or withdrow processes, therefore you must find the final balance after this processes .

in Sql language .

Answer 1

(1) To figure out how many clients were served by each employee, try thinking about these operators:

• DISTINCT - to get the unique CID/EID pairs in the working table
• GROUP BY and COUNT - to get the number of CID values for each EID
• ORDER BY and FIRST - to get the EID from the group with the largest COUNT(CID)

Once you figure out how to get the correct EID this way, then you can use the code you wrote as a subquery within a simple

SELECT * FROM EMPLOYEES WHERE EID=...

(2) From the information in your question, it's not clear how to find the ending balance because the starting balance is not obviously present. Maybe you are just supposed to computed based on all the deposits and withdrawals in the database, from an assumed original starting balance of zero? If that's the case then try thinking about

• JOIN Working with Movement, so that you can distinguish deposits from withdrawals
• SUM in the combined table to get total deposit and total withdrawal
• if the answer is supposed to be for EVERY customer, then GROUP BY, otherwise just a simple WHERE, to get just the values for the particular customer