1. Registered User
Join Date
Feb 2013
Posts
3

below are 2 tables-> shail_customer and shail_employee

SQL> desc shail_customer;
Name Null? Type
----------------------- -------- -------------
CID NUMBER(5)
CNAME VARCHAR2(20)
CAMOUNT NUMBER(20)

select * from shail_customer;

CID CNAME CAMOUNT
------ -------------------- ----------
1 shailesh 30000
2 amit 400000
3 utkarsh 35000
4 satyajit 45000
5 amol 44000

SQL> desc shail_employee;
Name Null? Type
----------------------- -------- ----------------
EID NUMBER(10)
ENAME VARCHAR2(20)
BASIC_SALARY NUMBER(20)
COMMISION NUMBER(20)
INSENTIVES NUMBER(20)
TOTAL_SAL NUMBER(20)

primary key eid& cid
*Commision is calculated as camount*.30
*Total_sal is calculated as basic_salary+commision+insentives

plz suggest a procedure to calculate commision and total salary

2. Registered User
Join Date
Aug 2003
Location
Where the Surf Meets the Turf @Del Mar, CA
Posts
7,776
how do determine which employee sold to which customer?
can 1 customer buy from multiple employees?

3. Registered User
Join Date
Feb 2013
Posts
3
hey anacedent,

for time being you can take one customer at a time..

means i making a java prgm to calculate employee salary .
Employee salary is based on his fixsalary+commision+insentives
commision is calculated based on customers bill amount
if customer bill amount is 30,000 on that amount 30% will be employee's referal commision

4. Registered User
Join Date
Aug 2003
Location
Where the Surf Meets the Turf @Del Mar, CA
Posts
7,776
which row in customer table is associated with which row in employee table?
Last edited by anacedent; 02-02-13 at 10:20.

5. Registered User
Join Date
Feb 2013
Posts
3
you can take 1st row of customer able and link with 1st row of employee...
here you can put some value in employee table that will be done from front end...

6. Registered User
Join Date
Aug 2003
Location
Where the Surf Meets the Turf @Del Mar, CA
Posts
7,776
rows in a table are like balls in a basket.

which ball in a basket is the "first" ball?

rows in a table have NO inherent order.

>you can take 1st row of customer able and link with 1st row of employee...

what is result when customer table has more rows than employee table?

Realize & understand that this "design" is fatally flawed!

7. Registered User
Join Date
Jun 2004
Location
Liverpool, NY USA
Posts
2,520
You need a third table that will tie the employee to the customer a shail_sales table. It will have at a minimum CID, EID

Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•