SQL & PL/SQL

Design
Table Name Field Name Data Type Allow Null Reference

Employee
ID Int No
Name Varchar(20) No
ID_Department Int No Department.ID
ID_Manager Int Yes Employee.ID
Salary Int No
Bonus Numeric(6,2) Yes

Department
ID Int No
Name Varchar(20) No

Awards
ID_Employee Int No Employee.ID
ID_Manager Int No Employee.ID
NoOfAwards TinyInt No

Data

Employee
ID Name ID_Department ID_Manager Salary Bonus
1 Mohan 3 NULL 25000 0
2 Chintan 3 1 15000 0
3 Ajay 2 2 10000 0
4 Vipul 2 3 7500 0
5 Rohini 1 2 5000 0

Department
ID Name
1 Computer
2 Account
3 Sales
4 HR



Awards
ID_Employee ID_Manager NoOfAwards
2 1 4
3 2 5
5 3 2
4 3 2
3 1 3

Note:
1 Manager of any employee is also an employee of a company.
2 Employee with ID_Manager = NULL is a BOSS/OWNER. (e.g. Mohan)
3 "[Awards] table represents No.of Awards won by an employee with combination of other employee as a manager which could be other than his actual manager also.
e.g. Rohini(as an employee) has won 2 awards while working with Ajay(as a Manager)
e.g. Mohan as an employee can not win award with other employee as a Manager but he can win award as a Manager with other employee"



1 "Update the bonus of all employees => (total salary of all employees of that employee's department) x (Number of employees under that employee)%
Note: Employee who is not a Manager should get 0.5% of total salary of all employees of his department"
"e.g. Chintan should get (total of his Department)*(Chintan is manager of 2 employees)% => 40000*2% = 800.
e.g. Rohini is not a manager of any employee so he should get (total of his Department)*0.5% => 5000 * 0.5% = 25"

2 List out all employee names and Total Number of Awards won as an Employee and Total Number of Awards won as a Manager.