Question

Employee_id	First_name	Last_name	Salary	Joining_date	Department
1	John	Abraham	1000000	01-JAN-13 12.00.00 AM	Banking
2	Michael	Clarke	800000	01-JAN-13 12.00.00 AM	Insurance
3	Roy	Thomas	700000	01-FEB-13 12.00.00 AM	Banking
4	Tom	Jose	600000	01-FEB-13 12.00.00 AM	Insurance
5	Jerry	Pinto	650000	01-FEB-13 12.00.00 AM	Insurance
6	Philip	Mathew	750000	01-JAN-13 12.00.00 AM	Services
7	TestName1	123	650000	01-JAN-13 12.00.00 AM	Services
8	TestName2	Lname%	600000	01-FEB-13 12.00.00 AM	Insurance

And Table Name : Incentives

Employee_ref_id	Incentive_date	Incentive_amount
1	01-FEB-13	5000
2	01-FEB-13	3000
3	01-FEB-13	4000
1	01-JAN-13	4500
2	01-JAN-13	3500

MySQL

1) Use ROLLUP to break out employee's totals of salary and grand total of salaries.

2) Create a query to update the employee with employee_id 7 to your name and appropriate values (you can pay yourself anything you like) and employee with employee_id 8 to me ( you can pay me even more).

Answer 1

1. SELECT

Employee_id,

SUM(Salary) as Total Salary,

(Salary + incentive_amount) as grand_total_salary

FROM Employee E

LEFT JOIN Incentives I

ON E.Employee_Id = I.Employee_ref_id

GROUP BY Employee_id, ROLLUP(Salary);

2. UPDATE Employee

SET First_Name = 'Neha',

Last_Name ='Shah',

Salary='70000',

Joining_Date='01-March-13 12.00.00 AM'

Service='Banking'

WHERE

Employee_id =7

UPDATE Employee

SET First_Name = 'ABC',

Last_Name ='DEF',

Salary='60000',

Joining_Date='01-APRIL-13 12.00.00 AM'

Service='Insurance'

WHERE

Employee_id =8