Server Configuration

1. Tomcat clustering load balancer
2. Implementing session affinity in load balancer
3. Implementing session replication

Spring AOP

1. Spring AOP short summary
2. Spring AOP tutorial

Spring remoting interview questions

1. Spring http invoker implementation and constraints 
2. 
   

Sybase interview questions

1. Google Site sybase interview questions
2. What transaction modes are available in Sybase -http://infocenter.sybase.com/help/index.jsp?topic=/com.sybase.infocenter.dc32300.1570/html/sqlug/X82816.htm

Department vise max salary and department specific employee count of people getting max salary in his department

Question - There are two table Employee & Department. Employee table has employeeId, employeeName, salary and department_Id column. Department table has departmentId, departmentName column. DepartmentId column of employee table works as foreign key for departmentId column of Department table.

Considering that multiple employee might be getting the maximum salary in a specific department.write a query, which would provide following result - departmentName, maximum salary of department, count of employee getting max salary in his department.


Sample data setup query - 

create table department(departmentId number(10,0) primary key, departmentName varchar(100));
Create table employee(employeeId number(10,0) primary key, employeeName varchar(100), departmentId number(10,0),Salary number(10,0),
constraint department_fk foreign key  (departmentId) references department(departmentId)
);

insert into department values(1,'Computer Science');
insert into department values(2,'Electrical Engineering');
insert into department values(3,'Electronics Engineering');

insert into employee values (1, 'employee1',1,1000);
insert into employee values (2, 'employee2',1,2000);
insert into employee values (3, 'employee3',1,2000);
insert into employee values (4, 'employee4',2,3000);
insert into employee values (5, 'employee5',2,1000);
insert into employee values (6, 'employee6',2,1000);
insert into employee values (7, 'employee7',3,7000);
insert into employee values (8, 'employee8',3,7000);

commit

Three possible solutions - 

select dept.departmentName,empDetail.salary, empDetail.empCount from 
(select departmentId, salary, count(*) empCount from employee where (salary, departmentId) in (
(select max(salary), departmentId from employee group by departmentId)) group by departmentId, salary) empDetail, Department dept
where dept.departmentId= empDetail.departmentId;


select dept.departmentName,empDetail.salary, empDetail.empCount from 
(select e.departmentId, e.salary, count(*) empCount from employee e where exists (
(select max(e1.salary), e1.departmentId from employee e1 group by e1.departmentId having (e.salary=max(e1.salary) and e1.departmentid=e.departmentid) )) group by departmentId, salary) empDetail, Department dept
where dept.departmentId= empDetail.departmentId;